mysql开发难点学习

使用过程控制实现从子菜单数据查找到主菜单数据

问题描述

在开发过程中遇到了一个数据情况，需要通过多级菜单中的子菜单来得到子菜单及其往上的所有父菜单。数据字段为:id、name、parent_id
设表格的名字为j_theme_directory
数据内容大致为：

id	name	parent_id
1	第一级菜单
2	第二级菜单1	1
3	第二级菜单2	1
4	第三级菜单1	2

如表j_theme_directory所示，假设已知第三级菜单1的id，需要取得他的父菜单的信息。

解决思路

常规开发中，前后端中的循环语句或者递归写法，可以有效的进行循环查询数据来获取到需求的数据。这里不能使用前后端来获取。在mysql中虽然不如前后端方便，但是同样可以考虑封装一个程序过程来达成需求的数据。
封装思路大致为：定义存储id，name和parent_id的变量。定义一个判断是否结束循环的标识符变量。定义一个存储树的字符串。
通过select来查询传入的id所对应的数据，并将数据赋值给存储id、name和parent_id的值。
进入循环，判断是否存在父级id，如果存在则组合菜单名并继续通过select向下查找。如果不存在，则使标识符为false结束循环，返回菜单名字列表。

示例

DROP PROCEDURE IF EXISTS allChildOrganization;
CREATE PROCEDURE allChildOrganization ( IN theme_dir_id VARCHAR ( 65 ) -- 输入参数为 theme_dir_id  类型为：varchar(65)
    ) BEGIN
    DECLARE
        sw int(2) DEFAULT(1); -- 下文中while循环结束的标志
    DECLARE
        list VARCHAR(255) DEFAULT ( "" );
    DECLARE
        p_depth SMALLINT UNSIGNED DEFAULT ( 0 );-- 记录查询的深度（循环的次数）\
    DECLARE 
        c_id VARCHAR(255);
    DECLARE 
        c_parent_id VARCHAR(255);
    DECLARE 
        c_name VARCHAR(255);

    SELECT id,parent_id, `name` into c_id,c_parent_id,c_name from j_theme_directory where id = theme_dir_id;
    set list = c_name;
    while sw = 1 do
        if (c_parent_id is not null) then
            SELECT id,parent_id, name into c_id,c_parent_id,c_name from j_theme_directory where id = c_parent_id;
            set list = concat(list,":",c_name);
        else
            set sw = 0;
        end if;
    end while;
    
    select list;
end -- 调用存储过程

调用

call allChildOrganization（"4"）

第三级菜单1:第二级菜单1:第一级菜单

注意

注意在开发存储过程时变量名不可以与select中的字段名相同。否则会导致返回不出数据。

自定义mysql函数，函数形式参数设置默认值

问题描述

在开发过程中经常需要对数据进行脱敏。基于字符截取和拼接的写法，虽然可以很好的完成脱敏工作。但是较长的语句和较多的参数，既不方便开发，也不方便管理和阅读。于是就想到可以通过UDP的方式来开发一个新的函数解决问题。
但是在实际开发过程中，会发现无论怎么设置形式参数的默认值，都会语法错误。

解决思路

这个问题是由于mysql版本导致的。函数参数设置默认值在mysql8.0过后在支持的。之前的版本都不支持。
所以只能通过ifnull的方式简单解决。
示例：

CREATE FUNCTION tuoming (col_name VARCHAR(255),start_s INT(12),start_e INT(12),end_e INT(12),xnum INT(12))
RETURNS VARCHAR(255)
BEGIN
    DECLARE val VARCHAR(255);
    set start_s = IFNULL(start_s,1);
    set start_e = IFNULL(start_e,3);
    set end_e = IFNULL(end_e,9);
    set xnum = IFNULL(xnum,5);

    SET val = concat(
        substr(col_name, start_s, start_e),

    REPEAT
        ("*", xnum),
        substr(col_name, end_e)
    );
    RETURN val; #函数体中肯定有 RETURN 语句
END

mysql动态建表

问题描述

在开发过程中遇到了一些情况，需要通过mysql来根据表名动态创建数据表。

解决思路

可以通过mysql中的PREPARE和EXECUTE动态创建再加上字符串拼接的方法动态创建数据表
示例：

drop PROCEDURE if EXISTS createOpen;
create PROCEDURE createOpen(in table_name VARCHAR(255))
BEGIN
    set @dropDatasql = concat("DROP table IF EXISTS open_",table_name,";");
    set @createDatabasesql = concat("create table open_",table_name," as select * from ",table_name,";");
    set @selectDatasql = concat("select * from ",table_name,";");
    PREPARE temp from @dropDatasql;
    EXECUTE temp;
    PREPARE temp FROM @createDatabasesql;
    EXECUTE temp;
    PREPARE temp FROM @selectDatasql;
    EXECUTE temp;
END