mysql FIND_IN_SET 尝试性优化
2023-09-11 14:18:11 时间
原函数
CREATE DEFINER=`root`@`localhost` FUNCTION `getDepartList`(departid VARCHAR(2000)) RETURNS varchar(1000) CHARSET utf8mb4 BEGIN DECLARE pTemp VARCHAR(1000); DECLARE cTemp VARCHAR(1000); SET pTemp = '$'; SET cTemp =CAST(departId AS CHAR); WHILE cTemp is not null DO SET pTemp = concat(pTemp,',',cTemp); SELECT group_concat(ID) INTO cTemp FROM t_s_depart WHERE FIND_IN_SET(parentdepartid,cTemp)>0; END WHILE; RETURN pTemp; END
1.尝试使用索引【使用Explain 目测有点效果】
2.优化函数
1.使用=条件替代【有些sb,命中索引,但查询次数过多,效率并没有多大提升】
CREATE DEFINER = 'root'@'%' FUNCTION gxgx.getDepartList(departid VARCHAR(100)) RETURNS VARCHAR(5000) CHARSET utf8mb4 BEGIN DECLARE tempParentDepartid varchar(32); DECLARE resultTemp VARCHAR(5000); DECLARE cTemp VARCHAR(1000); DECLARE countNum int(9); DECLARE iteratorNum int(9); SET tempParentDepartid = CAST(departId AS char); SET resultTemp = CONCAT('$',',',tempParentDepartid); SET countNum = 2; SET iteratorNum = 2; WHILE iteratorNum <= countNum DO SET tempParentDepartid = SUBSTRING_INDEX(SUBSTRING_INDEX(resultTemp,',',iteratorNum),',',-1); SELECT GROUP_CONCAT(ID) INTO cTemp FROM t_s_depart WHERE parentdepartid = tempParentDepartid; IF cTemp IS NOT NULL THEN SET resultTemp = CONCAT(resultTemp,',',cTemp); SET countNum = LENGTH(resultTemp) - LENGTH(REPLACE(resultTemp,',','')) + 1; END IF; SET iteratorNum = iteratorNum + 1; -- set cTemp = null; -- SELECT ID INTO cTemp FROM TMP LIMIT 1; END WHILE; -- drop temporary table if exists tmp; RETURN resultTemp; END
2.使用临时表【一旦函数出错,可能临时表没有删除导致函数无法正确执行,该函数在开发过程中废弃,因此不保证正确】
CREATE DEFINER = 'root'@'%' FUNCTION gxgx.getDepartList(departid VARCHAR(100)) RETURNS VARCHAR(5000) CHARSET utf8mb4 BEGIN DECLARE pTemp VARCHAR(5000); DECLARE cTemp VARCHAR(100); CREATE TEMPORARY TABLE tmp ( id varchar(100) primary key ); SET pTemp = '$'; SET cTemp = CAST(departId AS char); INSERT INTO tmp(id) VALUES(cTemp); WHILE cTemp is not NULL DO DELETE FROM tmp WHERE ID = cTemp; SET pTemp = CONCAT(pTemp,',',cTemp); INSERT INTO tmp(id) SELECT ID FROM t_s_depart WHERE parentdepartid = cTemp; -- set cTemp = null; SELECT ID INTO cTemp FROM TMP LIMIT 1; END WHILE; drop temporary table if exists tmp; RETURN pTemp; END
3.使用IN条件
CREATE DEFINER = 'root'@'localhost' FUNCTION gxgx.getDepartList(departid varchar(100)) RETURNS VARCHAR(6000) CHARSET utf8mb4 BEGIN DECLARE pTemp varchar(6000); DECLARE cTemp varchar(6000); SET pTemp = '$'; SET cTemp = CAST(departid AS char); WHILE cTemp IS NOT NULL DO SET pTemp = CONCAT(pTemp, ',', cTemp); SELECT GROUP_CONCAT(ID) INTO cTemp FROM t_s_depart WHERE parentdepartid IN (SELECT DISTINCT (SUBSTRING_INDEX (SUBSTRING_INDEX (cTemp, ',', b.help_topic_id + 1), ',', -1)) AS parentdepartid FROM mysql.help_topic AS b WHERE b.help_topic_id < (CHAR_LENGTH(cTemp) - CHAR_LENGTH(REPLACE(cTemp, ',', '')) + 1)); END WHILE; RETURN pTemp; END
相关文章
- MySQL update A set num=num+ ? where id=?是否存在并发的问题
- MySQL存储过程详解 mysql 存储过程
- mysql binary like_MYSQL的binary解决mysql数据大小写敏感问题的方法
- 【MySQL高级】Mysql复制及Mysql权限管理
- Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘ (2)
- 【Java】通过“配置文件”建立MySQL数据库连接,读取数据库(有图,有示例)
- Mysql 环境配置查询
- MySQL 监控
- 【MySQL进阶-03】深入理解mysql的索引分类,覆盖索引,覆盖索引失效,回表,MRR
- 《MySQL排错指南》——1.3 当错误可能由之前的更新引起时
- 基于PHP+MySQL实现在线考试系统【100010717】
- 基于Java(SSM)+Mysql实现(WinForm)企业进销存管理系统【100010076】
- JDBC 连接 MySQL 异常:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
- 深入MySQL复制(三):半同步复制
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- python操作mysql数据库系列-操作MySql数据库(五)
- 编译安装php时提示Cannot find MySQL header files的解决方法
- powerdesigner添加mysql的字符集ENGINE和DEFAULT CHARACTER SET
- Mysql_mysql force Index 强制索引
- mysql数据库迁移到kingbase数据库上(其他数据库与其类似)
- MySQl表的增删查改(聚合查询+联合查询)
- PostgreSQL通过mysql_fdw访问MySQL数据库
- MySQL经常使用命令--create命令使用
- 高速创建和mysql表相应的java domain实体类
- MySQL性能优化的21个最佳实践 和 mysql使用索引
- nodejs中mysql用法
- python封装一个工具类 ,对MySQL数据库增删改查,可多字段动态插入mysql数据库中