MYSQL函数编写一

最新推荐文章于 2023-01-16 14:11:19 发布

weixin_33950035

最新推荐文章于 2023-01-16 14:11:19 发布

阅读量144

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/2381372/blog/663779

版权

2019独角兽企业重金招聘Python工程师标准>>>

需求表如下：

单纯的用mysql函数来表达上述关系：

DELIMITER $$
USE `euht`$$
DROP FUNCTION IF EXISTS `queryCurrentAlarmChildren`$$
CREATE DEFINER=`root`@`%` FUNCTION `queryCurrentAlarmChildren`(nodeId CHAR(255),treeLevel CHAR(255)) RETURNS VARCHAR(4000) CHARSET utf8
BEGIN
DECLARE sNodeId VARCHAR(255);
DECLARE sCurrentAlarmId VARCHAR(8000);
DECLARE sTreeLevel VARCHAR(255);
DECLARE sTempLine VARCHAR(4000);
DECLARE sTempEdu VARCHAR(4000);
DECLARE sTempEbu VARCHAR(4000);
DECLARE sWhileInt INT;
DECLARE sWhileTotal INT;
DECLARE sWhileGroupConcat VARCHAR(1024);
DECLARE sWhileConcat VARCHAR(4000);
DECLARE sWhileTempValue INT;
SET sNodeId = CAST(nodeId AS CHAR);
SET sTreeLevel = CAST(treeLevel AS CHAR);
SET sCurrentAlarmId = '$';
SET sTempLine = '';
SET sTempEdu = '';
SET sTempEbu = '';
SET sWhileInt = 0;
SET sWhileTotal = 0;
SET sWhileConcat = '';
IF sTreeLevel='3' THEN 
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE device_id = sNodeId;
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE device_id = sNodeId ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
ELSEIF sTreeLevel='2' THEN
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE device_id = sNodeId;
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE device_id = sNodeId ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
	SELECT COUNT(id) INTO sWhileTotal FROM cap_ebu WHERE edu_id = sNodeId;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_ebu WHERE edu_id = sNodeId ORDER BY add_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sTempEbu = CONCAT(sTempEbu,',',IFNULL(sWhileConcat,''));
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
ELSEIF	sTreeLevel='1' THEN
	SELECT COUNT(*) INTO sWhileTotal FROM cap_edu WHERE line_id = sNodeId;
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_edu WHERE line_id = sNodeId LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sTempEdu = CONCAT(sTempEdu,',',IFNULL(sWhileConcat,''));	
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
	SELECT COUNT(id) INTO sWhileTotal FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0 ORDER BY add_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sTempEbu = CONCAT(sTempEbu,',',IFNULL(sWhileConcat,''));
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
ELSE
	SELECT GROUP_CONCAT(id) INTO sTempLine FROM train_line WHERE parent_id = sNodeId;
	SELECT COUNT(*) INTO sWhileTotal FROM cap_edu WHERE FIND_IN_SET (line_id,IFNULL(sTempLine,''))>0;
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_edu WHERE FIND_IN_SET(line_id,sTempLine)>0 LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sTempEdu = CONCAT(sTempEdu,',',IFNULL(sWhileConcat,''));	
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
	SELECT COUNT(id) INTO sWhileTotal FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0 ORDER BY add_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sTempEbu = CONCAT(sTempEbu,',',IFNULL(sWhileConcat,''));
	SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0;
	SET sWhileInt = 0;
	SET sWhileConcat = '';
	WHILE sWhileTotal >= (sWhileInt*20) DO
		SET sWhileTempValue = sWhileInt*20;
		SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20;
		SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,''));
		SET sWhileInt = sWhileInt + 1;
	END WHILE;
	SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));
END IF;
RETURN sCurrentAlarmId;
END$$
DELIMITER ;

SELECT * FROM current_alarm WHERE FIND_IN_SET(id,queryCurrentAlarmChildren('22fd52a51efb4401b091f944e2051a89','0'))>0;

注意：

编写函数的时候，需要注意几个地方：

使用“GROUP_CONCAT”函数时，能够拼接的最大长度是1kb(1024字节)。否则，超过部分将会忽略。
考虑到这个原因，写函数时，通过分页的思路来进行分段拼接。
使用“GROUP_CONCAT”函数时，如果拼接的来源为零，则拼接的结果为null；如
SELECT GROUP_CONCAT(id) FROM aform WHERE id = '11' ;（不存在id为11的行），执行的结果为null。
使用“CONCAT”函数时，如果存在为null值，则拼接结果为null。如：
SELECT CONCAT('1',',',NULL)；执行结果为null
SELECT CONCAT(NULL,',','2')；执行结果为null
当“DECLARE ”申明了一个变量，而没给该变量初始化时，其默认为null，这里只验证了VARCHAR类型。

后记：上面这种方法能解决需求中问题，可是查询效率极慢，无法忍受。下面这种方法效率得到极大提高。

因为页面会提供两个参数给后台：一个参数是nodeId(当前点击的id)，另一个参数是level（树等级）。

根据level等级来判断执行那个查询语句。

例如：level为0或-1（代表全网），则执行以下查询语句：

SELECT * FROM
    (SELECT * FROM euht.HISTORY_ALARM ha WHERE ha.DEVICE_ID = 
    (SELECT ceb.ID FROM euht.CAP_EBU ceb WHERE ha.DEVICE_ID = ceb.ID AND ceb.EDU_ID = 
        (SELECT ced.id FROM euht.CAP_EDU ced WHERE ceb.EDU_ID = ced.ID AND ced.LINE_ID = 
                (SELECT tl.ID FROM euht.TRAIN_LINE tl WHERE tl.PARENT_ID = ？ AND tl.ID = ced.LINE_ID)))
    UNION ALL
    (SELECT * FROM euht.HISTORY_ALARM ha WHERE ha.DEVICE_ID = 
        (SELECT ced.id FROM euht.CAP_EDU ced WHERE ha.DEVICE_ID = ced.ID AND ced.LINE_ID = 
                (SELECT tl.ID FROM euht.TRAIN_LINE tl WHERE tl.PARENT_ID = ？ AND tl.ID = ced.LINE_ID)))) t LIMIT 0,15

如果level为1（代表京津高铁这一等级），则执行以下查询语句：

                SELECT
			*
			FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = 
	    	(SELECT ceb.ID FROM CAP_EBU ceb WHERE ha.DEVICE_ID = ceb.ID AND ceb.EDU_ID = 
	        (SELECT ced.id FROM CAP_EDU ced WHERE ceb.EDU_ID = ced.ID AND ced.LINE_ID = #{nodeId,jdbcType=VARCHAR}))
	        UNION ALL
	        SELECT
			*
	        FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = 
	        (SELECT ced.id FROM CAP_EDU ced WHERE ha.DEVICE_ID = ced.ID AND ced.LINE_ID = #{nodeId,jdbcType=VARCHAR})

如果level为2（代表EDU这一等级），则执行以下查询语句：

SELECT
			*
			FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = 
	    	(SELECT ceb.ID FROM CAP_EBU ceb WHERE ha.DEVICE_ID = ceb.ID AND ceb.EDU_ID = #{nodeId,jdbcType=VARCHAR})
	        UNION ALL
	        SELECT
			*
	        FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = #{nodeId,jdbcType=VARCHAR}

如果level为3（代表EBU这一等级），则执行以下查询语句：

SELECT
			*
	        FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = #{nodeId,jdbcType=VARCHAR}

转载于:https://my.oschina.net/u/2381372/blog/663779

weixin_33950035

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫