mysql一个字段拆分多个字段mysql如何将表中的一个字段拆分成多个字段中间用','隔开如表idnamename1name2...1张三,李四2,王二,李王,小明拆分成idnamename

Question

mysql 一个字段拆分多个字段
mysql如何将表中的一个字段拆分成多个字段中间用','隔开如
表
id name 　　name1 name2 ...
1 张三,李四
2, 王二,李王,小明
拆分成
id name 　　name1 name2　name3 ...
1 张三 　,　李四
2, 王二,李王,小明　　王二　　李王 小明 ...
求哪位大神
错了
拆分成
id name 　　name1 name2　name3 ...
1 张三,李四 　　张三 李四
2, 王二,李王,小明　　王二　　李王 小明 ...

Answer 1

觉得你还是应该用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)
希望能解决您的问题。