觉得你还是应该用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<i do
-> 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)
追问谢谢!php不是很熟,请问能不能帮忙写下代码呢,其中id字段是主键,序号不重复,谢谢了