mysql：split的存储过程，返回表

ryoqjall 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(392)

我发现了很多mysql的split过程返回字符串的第x部分的例子。但是我需要一个split过程来返回被拆分字符串的所有部分，比如：

SELECT split(",", "1,2,3,4,5,6");

应该返回；

+-------+
| split |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
+-------+

我试过：

DELIMITER $$

CREATE PROCEDURE `split`(delimeter VARCHAR(1),txt VARCHAR(65535))
RETURNS split TABLE (
  part varchar(1024) NOT NULL)
DETERMINISTIC
BEGIN

DECLARE pos, posOld;

set pos := locate(delimeter,txt);
set posOld = 1;

WHILE pos > 0 DO
 set part := subst(text, posOld, pos-1);
 set posOld := pos+1;
 insert into split (`part`) values (part);

 set pos := locate(delimeter, txt, posOld);
END WHILE;

END$$

但是得到错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'split TABLE (part varchar(1024) NOT NULL)

正确的方法是什么？

mysql

来源：https://stackoverflow.com/questions/49755261/mysql-stored-procedure-for-split-return-table

1条答案

按热度按时间

bis0qfac1#

您只能使用纯sql来实现这一点。
这个技巧是通过结合使用mysql数字生成器和嵌套的子字符串索引函数来实现的。
查询
此查询将生成从1到100的数字。
因此，最终的查询最多可以支持100个分隔的值。

SELECT 
 (@number  := @number + 1) AS number
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS record_1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS record_2
CROSS JOIN ( SELECT @number := 0 ) AS init_user_param

请参见演示http://sqlfiddle.com/#!9/c314ca/5型
现在我们将从逗号分隔的字符串中提取值
查询
将[position]替换为一个0到5之间的数字，您要从逗号分隔的字符串中提取该值。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5', ',', [position]), ',', -1) AS split;

请参见演示http://sqlfiddle.com/#!9/c314ca/16型
现在我们知道了将这两个查询组合到一个工作解决方案的基础知识。
查询

SELECT
  DISTINCT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(@CSV, ',', generator.number), ',', -1) AS split
FROM (

  SELECT 
   (@number  := @number + 1) AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_2
  CROSS JOIN ( SELECT @number := 0 ) AS init_user_param
) 
 AS generator
CROSS JOIN (
  SELECT @CSV := '1,2,3,4,5'
) AS init_user_param

请参见演示http://sqlfiddle.com/#!9/c314ca/20型

赞(0）回复(0）举报 2021-06-24

我来回答

mysql：split的存储过程，返回表

1条答案

相关问题

热门标签

最新问答