求MySQL版本代码：将两组逗号分隔字符串对应存入临时表

需求说明 #

给定两组逗号分隔字符串：

string 1: 'A,B,C,D,E'
string 2: 'X091,X089,X051,X043,X023'

需要将其拆分后按顺序对应关联，得到如下结果：

String1     String2
---------------------
A           X091
B           X089
C           X051
D           X043
E           X023

现有SQL Server实现代码 #

DECLARE @str1 VARCHAR(1000)= 'A,B,C,D,E';
DECLARE @str2 VARCHAR(1000)= 'X091,X089,X051,X043,X023';

;WITH
 --split the first string
 a1 AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @str1, j+1) FROM a1 WHERE j > i)
,b1 AS (SELECT n, SUBSTRING(@str1, i+1, IIF(j>0, j, LEN(@str1)+1)-i-1) s FROM a1 WHERE i >= 0)
 --split the second string
,a2 AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @str2, j+1) FROM a2 WHERE j > i)
,b2 AS (SELECT n, SUBSTRING(@str2, i+1, IIF(j>0, j, LEN(@str2)+1)-i-1) s FROM a2 WHERE i >= 0)
--join them by the index
SELECT b1.n
      ,b1.s AS s1
      ,b2.s AS s2
FROM b1
INNER JOIN b2 ON b1.n=b2.n
ORDER BY b1.n;

MySQL版本实现代码 #

MySQL 8.0及以上版本支持递归CTE，以下是适配后的代码：

SET @str1 = 'A,B,C,D,E';
SET @str2 = 'X091,X089,X051,X043,X023';

WITH RECURSIVE
-- 拆分第一个字符串
a1 AS (
    SELECT 0 AS n, -1 AS i, 0 AS j
    UNION ALL
    SELECT n+1, j, LOCATE(',', @str1, j+1) FROM a1 WHERE j > i
),
b1 AS (
    SELECT n, SUBSTRING(@str1, i+1, IF(j>0, j, LENGTH(@str1)+1)-i-1) AS String1 
    FROM a1 WHERE i >= 0
),
-- 拆分第二个字符串
a2 AS (
    SELECT 0 AS n, -1 AS i, 0 AS j
    UNION ALL
    SELECT n+1, j, LOCATE(',', @str2, j+1) FROM a2 WHERE j > i
),
b2 AS (
    SELECT n, SUBSTRING(@str2, i+1, IF(j>0, j, LENGTH(@str2)+1)-i-1) AS String2 
    FROM a2 WHERE i >= 0
)
-- 按索引关联
SELECT b1.String1, b2.String2
FROM b1
INNER JOIN b2 ON b1.n = b2.n
ORDER BY b1.n;

关键差异说明 #

1. 变量声明：MySQL使用SET替代SQL Server的DECLARE声明变量
2. 递归CTE：MySQL需要显式指定RECURSIVE关键字
3. 字符串查找函数：用LOCATE替代CHARINDEX
4. 字符串长度函数：用LENGTH替代LEN
5. 条件判断函数：用IF替代IIF

内容的提问来源于stack exchange，提问作者Jason Joel Pinto