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

梦里梦外; 2022-12-13 14:19 134阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb