MySQL经常会用到查询树结构数据，这里专门收集整了一篇。
- 构建函数
构建树查询函数：查询父级节点函数
-- 在mysql中完成节点下的所有节点或节点上的所有父节点的查询 -- 根据传入id查询所有父节点的id DROP FUNCTION IF EXISTS `getParList`; delimiter // CREATE FUNCTION `getParList`(rootId INT) RETURNS varchar(255) BEGIN DECLARE sTemp VARCHAR(255); -- 设置变量 DECLARE sTempPar VARCHAR(255); SET sTemp = ‘‘; SET sTempPar =rootId; #循环递归 while sTempPar is not null DO #判断是否是第一个，不加的话第一个会为空 IF sTemp != ‘‘ THEN SET sTemp = concat(sTemp,‘,‘,sTempPar); ELSE SET sTemp = sTempPar; END IF; SET sTemp = concat(sTemp,‘,‘,sTempPar); SELECT group_concat(pid) INTO sTempPar FROM china_region where pid<>id and FIND_IN_SET(id,sTempPar)>0; END WHILE; RETURN sTemp; END //
-- id是前面表中的，110105为子级id
select * from china_region where FIND_IN_SET(id,getParList(110105));
构建树查询函数：查询子级节点函数
-- 根据传入id查询所有子节点的id DROP FUNCTION IF EXISTS `getChildList`; delimiter // CREATE FUNCTION `getChildList`(rootId INT) RETURNS varchar(255) BEGIN DECLARE sTemp VARCHAR(255); DECLARE sTempChd VARCHAR(255); SET sTemp = ‘‘; SET sTempChd =rootId; WHILE sTempChd is not null DO SET sTemp = concat(sTemp,‘,‘,sTempChd); SELECT group_concat(id) INTO sTempChd FROM china_region where FIND_IN_SET(pid,sTempChd)>0; END WHILE; RETURN sTemp; END //
-- id是前面表中的，110000为父级id
select * from china_region where FIND_IN_SET(id,getChildList(110000));
-- 批量更新
update china_region set sort_number = CASE `level` when 0 then 0 when 1 then 100 when 2 then 200 when 3 then 300 end, label = case `level` when 1 then ‘value1‘ when 2 then ‘value2‘ when 3 then ‘value3‘ ELSE ‘others‘ end where id >= 100000 and id <120000 ;
-- 清空 sort_number 和 label 列
UPDATE china_region set sort_number=NULL, label=NULL;
构建循环查询函数：循环查询函数
-- 循环查询 delimiter $$ drop function if exists loops; create function loops(num int) returns varchar(255) begin declare i int default 110101; declare result varchar(255) default ‘‘; lpl:loop -- lp1 为循环体名称 LOOP 为关键字 if result != ‘‘ then set result=concat(result,‘,‘,i); else set result=i; end if; update china_region set sort_number=i where id=i; set i=i + 1; if i>num then leave lpl; -- 离开循环体 end if; end loop lpl; -- 结束循环 return result; end $$ delimiter;
-- 循环查询序号+1
select loops(110117);
构建拼接函数：循环拼接函数
-- 循环拼接组合 delimiter $$ drop function if exists fun_addStr; create function fun_addStr(str1 varchar(32),str2 varchar(32),num int) returns varchar(200) begin declare i int default 1; declare result varchar(200) default ‘‘; set result=str1; myloop:loop set i=i+1; set result=concat(result,str2); if i>num then leave myloop; end if; end loop myloop; return result; end $$ delimiter;
-- 循环拼接组合
select fun_addStr(‘字符串一‘,‘字符串二‘,3);
- 存储过程
-- 查询数据库中的存储过程和函数
select `name` from mysql.proc where db = ‘testdb‘ and `type` = ‘PROCEDURE‘; -- 存储过程 select * from mysql.proc where db = ‘testdb‘ and `type` = ‘PROCEDURE‘ and name=‘xx‘; select `name` from mysql.proc where db = ‘testdb‘ and `type` = ‘FUNCTION‘; -- 函数
show procedure status; -- 存储过程 show function status; -- 函数
-- 查看存储过程或函数的创建代码
show create procedure proc_name; show create function func_name;
-- 查看视图
SELECT * from information_schema.VIEWS; -- 视图 SELECT * from information_schema.TABLES; -- 表
-- 查看触发器
SHOW TRIGGERS [FROM db_name] [LIKE expr];
SELECT * FROM information_schema.`TRIGGERS` T WHERE trigger_name=‘mytrigger‘;
作者：Jason Zeng 于 2020-06-14
博客：http://www.cnblogs.com/zengming/
GItHub：https://github.com/lovelifeming
严正声明：
1.由于本博客部分资源来自互联网，版权均归原作者所有。转载的目的是用于学术交流与讨论学习，将不对任何资源负法律责任。
2.若无意中侵犯到您的版权利益，请来信联系我，我会在收到信息后会尽快给予处理！
3.所有资源内容仅供学习交流之用，请勿用作商业用途，谢谢。
4.如有转发请注明出处，来源于http://www.cnblogs.com/zengming/ ，谢谢合作。