mysql开发难点学习 最后更新时间：2023年10月07日 #### 使用过程控制实现从子菜单数据查找到主菜单数据 ##### 问题描述 在开发过程中遇到了一个数据情况，需要通过多级菜单中的子菜单来得到子菜单及其往上的所有父菜单。数据字段为:`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结束循环，返回菜单名字列表。 ##### 示例 ```sql 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 -- 调用存储过程 ``` 调用 ```sql call allChildOrganization（"4"） ``` 返回 ```sql 第三级菜单1:第二级菜单1:第一级菜单 ``` ##### 注意 注意在开发存储过程时变量名不可以与select中的字段名相同。否则会导致返回不出数据。 #### 自定义mysql函数，函数形式参数设置默认值 ##### 问题描述 在开发过程中经常需要对数据进行脱敏。基于字符截取和拼接的写法，虽然可以很好的完成脱敏工作。但是较长的语句和较多的参数，既不方便开发，也不方便管理和阅读。于是就想到可以通过UDP的方式来开发一个新的函数解决问题。 但是在实际开发过程中，会发现无论怎么设置形式参数的默认值，都会语法错误。 ##### 解决思路 这个问题是由于mysql版本导致的。函数参数设置默认值在mysql8.0过后在支持的。之前的版本都不支持。 所以只能通过ifnull的方式简单解决。 示例： ```sql 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`动态创建再加上字符串拼接的方法动态创建数据表 示例： ```sql 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 ```
Comments | NOTHING