MySQL逗号分隔的值分成多行 [英] MySQL comma separated values into multiple rows

问题描述

我试图拆分字符串值，它作为参数传递给我的SQL函数到多行。我尝试了很多样品，但我不能找到正确的输出。

I'm trying to split string values, which is passed as the parameter to my SQL function into multiple rows. I tried many samples out there but I'm not able to find the correct output.

这是我的代码这里：

DROP function if exists F_Split;
DROP TABLE if exists temp1;

CREATE TEMPORARY TABLE temp1(val CHAR(255));
DELIMITER $$
CREATE FUNCTION F_Split (x1 char(50)) 
RETURNS char(100)
DETERMINISTIC
BEGIN
DECLARE colval varchar(100);    
SET @sql = CONCAT("insert into temp1 (val) values ('", REPLACE(x1, ",", "'),('"),"');");
select val from temp1 into colval;
RETURN colval;
END$$
DELIMITER ;

select F_Split('6,4');

我用一个参数调用F_Split（'6,4'将值存储到临时表中，并且函数需要基于逗号分隔返回2行。

I'm calling F_Split('6,4') with a single parameter, I'm trying to store the values into a temp table and function needs to return 2 rows based on the comma separation. i.e

异常输出：

Row1:6
Row2:4

实际输出：
Null。

Actual Output: Null.

我知道insert语句不能正常工作，但我不知道如何解决这个问题。
我想在用户定义函数中实现。
需要帮助！感谢

I know the insert statement is not working properly but I'm not sure how to fix this. I want to Implement in the User Defined Function. Help needed! Thanks

推荐答案

您也可以使用这样的查询。列ID仅用于测试。如果你想要分割更多的值超过4，你必须修改UNION和CONCAT字符串',,,,'

you can also use a query like this. The column id is only for testing. If you want split more values than 4 you must modify the UNION and also the CONCAT string ',,,,'

SELECT
    id
    , SUBSTRING_INDEX( SUBSTRING_INDEX(val, ',', id), ',', -1) AS myvalue
FROM (
    SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
) cnt
CROSS JOIN (SELECT CONCAT('123,456,6678',',,,,') AS val) AS c
HAVING myvalue <> '';

示例

mysql> SELECT
    ->     id
    ->     , SUBSTRING_INDEX( SUBSTRING_INDEX(val, ',', id), ',', -1) AS myvalue
    -> FROM (
    ->     SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> ) cnt
    -> CROSS JOIN (SELECT CONCAT('123,456,6678',',,,,') AS val) AS c
    -> HAVING myvalue <> '';
+----+---------+
| id | myvalue |
+----+---------+
|  1 | 123     |
|  2 | 456     |
|  3 | 6678    |
+----+---------+
3 rows in set (0,00 sec)

mysql>

这篇关于MySQL逗号分隔的值分成多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！