mysql 多个值用 分隔_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)