MySQL 将分隔数据转换为多值IN列表

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

已经有了分隔数据，想要将其转换为where子句IN列表中的项目
例如:
7654,7689,7782,7788
要将该字符串用在WHERE子句中，但是下面的SQL语句是错误的，因为EMPNO是一个数值列:
select ename,sal,deptno
from emp
where empno in (‘7654,7689,7782,7788’)

因为EMPNO是一个数值列，而此IN列表是一个字符串值，所以此SQL语句会时报。
现将此字符串转换为用逗号分解的数值列表。

二.解决方案

表面上看SQL应该将分隔字符串作为一个分隔值列表对待，但是实际情况不是这样的。
当SQL遇到括在引号中的逗号时，并不知道吃符号表示多值列表，SQL必须将括在引号中的内容当成一个整体对待，也就是一个字符串值。
因此必须将字符串分解为各个单独的EMPNO。
这种解决方案的关键就是需要遍历字符串，但并不时一个字符串一个字符串的遍历，而是要将这个字符串转化为有效的EMPNO值。

代码:

select empno,ename,sal,deptno
 from emp
where empno in
      (
select substring_index(
       substring_index(list.vals,',',iter.pos),',',-1) empno
  from (select id pos from t10) as iter,
       (select '7654,7689,7782,7788' as vals) list
where iter.pos <=
      (length(list.vals) - length(replace(list.vals,',',''))) + 1
      ) 

测试记录:

mysql> select * from t10;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql>
mysql> select empno,ename,sal,deptno
    ->  from emp
    -> where empno in
    ->       (
    -> select substring_index(
    ->        substring_index(list.vals,',',iter.pos),',',-1) empno
    ->   from (select id pos from t10) as iter,
    ->        (select '7654,7689,7782,7788' as vals) list
    -> where iter.pos <=
    ->       (length(list.vals) - length(replace(list.vals,',',''))) + 1
    ->       ) ;
+-------+--------+---------+--------+
| empno | ename  | sal     | deptno |
+-------+--------+---------+--------+
|  7654 | MARTIN | 1250.00 |     30 |
|  7782 | CLARK  | 4000.00 |     10 |
|  7788 | SCOTT  | 3000.00 |     20 |
+-------+--------+---------+--------+
3 rows in set (0.00 sec)

上面看得不直关，拆分为下面的，看得更直观了

mysql> SELECT iter.pos,
    ->        list.vals,
    ->        -- 提取第iter。pos个','的位置
    ->           substring_index(vals,',',iter.pos) as str1,
    ->           -- 在上一个的基础上 提取最后一次出现','的右边的数据
    ->           substring_index(substring_index(vals,',',iter.pos),',',-1) as str2
    -> from
    -> (select id pos from t10) as iter,
    -> (select '7654,7689,7782,7788' as vals) list
    -> where iter.pos <= length(list.vals) - length(replace(list.vals,',','')) + 1
    -> ;
+------+---------------------+---------------------+------+
| pos  | vals                | str1                | str2 |
+------+---------------------+---------------------+------+
|    1 | 7654,7689,7782,7788 | 7654                | 7654 |
|    2 | 7654,7689,7782,7788 | 7654,7689           | 7689 |
|    3 | 7654,7689,7782,7788 | 7654,7689,7782      | 7782 |
|    4 | 7654,7689,7782,7788 | 7654,7689,7782,7788 | 7788 |
+------+---------------------+---------------------+------+
4 rows in set (0.00 sec)

其实如果empno中数据分布是OK的情况下，可以使用instr函数来解决
代码:

select empno,ename,sal,deptno
from emp
where instr('7654,7689,7782,7788',empno) > 0;

测试记录

mysql> select empno,ename,sal,deptno
    -> from emp
    -> where instr('7654,7689,7782,7788',empno) > 0;
+-------+--------+---------+--------+
| empno | ename  | sal     | deptno |
+-------+--------+---------+--------+
|  7654 | MARTIN | 1250.00 |     30 |
|  7782 | CLARK  | 4000.00 |     10 |
|  7788 | SCOTT  | 3000.00 |     20 |
+-------+--------+---------+--------+
3 rows in set (0.00 sec)