MySQL中的表值函数
在SQL server中,可以自定义表值函数,然后通过select * from 表值函数 where .... group by ....,可以像操作一张表一样去查询这个函数,但是在MySQL中,并没有表值函数这样的概念,因此我们可以使用MySQL提供的json_table函数来模拟出表值函数的效果,具体思路就是将表的数据转换为json,然后通过json_table来解析这个json完成需求,具体例子如下
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delimiter //
drop FUNCTION if exists fnReportSplitTable;
CREATE FUNCTION fnReportSplitTable(v_splitStr VARCHAR(50), v_temp VARCHAR(2000))
RETURNS VARCHAR(4000) READS SQL DATA
BEGIN
DECLARE v_rrr VARCHAR(4000);
-- 截取字符串的长度
DECLARE v_splitStrLen INT;
DECLARE v_rowInx INT;
DECLARE v_indexIndex INT;
DECLARE v_strStr NVARCHAR(100);
-- 创建临时表接受结果数据
drop TEMPORARY table if EXISTS v_resultTable;
CREATE TEMPORARY TABLE v_resultTable (rowInx INT, strStr VARCHAR(100));
SET v_splitStrLen = length(v_splitStr);
SET v_rowInx = 1;
WHILE length(v_temp) > 0 DO
SET v_indexIndex = locate(v_splitStr, v_temp);
IF v_indexIndex = 0 THEN
INSERT INTO v_resultTable VALUES (v_rowInx, v_temp);
SET v_temp = '';
ELSE
SET v_strStr = substring(v_temp, 1, v_indexIndex - 1);
INSERT INTO v_resultTable VALUES (v_rowInx, v_strStr);
SET v_temp = substring(v_temp, v_indexIndex + v_splitStrLen, length(v_temp) - (v_indexIndex - 1) - v_splitStrLen);
SET v_rowInx = v_rowInx + 1;
END IF;
END WHILE;
SELECT concat('[', GROUP_CONCAT(json_object('rowInx', rowInx, 'strStr', strStr)), ']') INTO v_rrr FROM v_resultTable;
drop TEMPORARY table if EXISTS v_resultTable;
RETURN ifnull(v_rrr, '');
END;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
上面定义的是一个MySQL的函数,完成的需求为,通过分隔符拆分字符串,并返回json数据,然后我们如下调用该函数
SELECT * FROM json_table(fnReportSplitTable(',', 'wangyu,jintaiyan,linyuner'),'$[*]' columns (rowInx varchar(50) path '$.rowInx', strStr varchar(100) path '$.strStr')) t;
返回的结果为:
rowInx strStr
1 wangyu
2 jintaiyan
3 linyuner