MYSQL命令怎么实现将表中某个字段中多个记录拆分，急求答案！

答案:2 mip版

解决时间 2021-02-25 21:04

提问者网友：夏日╮悲歌
2021-02-25 09:42

比如原数据如图，我想将第二个字段中有逗号分隔的多个值分别拆成一行

拆开后成这样的记录

哪位大侠帮帮忙，不胜感激……

最佳答案

二级知识专家网友：抚弦思华年
2021-02-25 10:17

觉得你还是应该用php去写。非要用mysql本身的话给个参考：

mysql> select * from a;
+------+--------+------+----+
| id   | name   | num1 | bb |
+------+--------+------+----+
|    1 | a,     |    2 |  3 |
|    2 | b,c,   |    2 |  4 |
|    3 | d,e,f, |    2 |  5 |
+------+--------+------+----+
3 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure `strsplit1`(in str3 varchar(2))
    -> begin
    ->  declare i int(10);
    ->  declare j int(10);
    ->  declare k int(10);
    ->  declare str1 varchar(100);
    ->         declare str4 varchar(1000);
    ->  declare str5 varchar(100);
    ->
    ->  select count(*) into i from a;
    ->  if i!=0 then
    ->          drop table if exists tmp_table1;
    ->          CREATE TEMPORARY TABLE tmp_table1 like a;
    ->  end if;
    ->
    ->  set j=0;
    ->  while j     ->          select id,name into str1,str4 from a limit j,1;
    ->          select instr(str4,str3) into k from dual;
    ->          if k=0 then
    ->                  insert into tmp_table1(id,name) values(str1,str4);
    ->          end if;
    ->          while k!=0 do
    ->                  select substring_index(str4,str3,1) into str5 from dual;
    ->                  insert into tmp_table1(id,name) values(str1,str5);
    ->                  select mid(str4,k+1) into str4 from dual;
    ->                  select instr(str4,str3) into k from dual;
    ->          end while;
    ->  set j=j+1;
    ->  end while;
    ->
    -> select * from tmp_table1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>  delimiter ;

最后结果：
mysql> call strsplit1(',');
+------+------+------+----+
| id   | name | num1 | bb |
+------+------+------+----+
|    1 | a    | NULL |  1 |
|    2 | b    | NULL |  2 |
|    2 | c    | NULL |  3 |
|    3 | d    | NULL |  4 |
|    3 | e    | NULL |  5 |
|    3 | f    | NULL |  6 |
+------+------+------+----+
6 rows in set (0.25 sec)

Query OK, 0 rows affected (0.30 sec)

全部回答