【MySQL】拆分逗号分隔字符串并分组生成序号
1：创建测试数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> create table t(id int,name varchar(20)); Query OK, 0 rows affected mysql> insert into t values(1,'a1,a2,a3'),(2,'b1,b2,b3,b4'),(3,'c1,c2'),(4,'d1'); Query OK, 4 rows affected Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t; +----+-------------+ | id | name | +----+-------------+ | 1 | a1,a2,a3 | | 2 | b1,b2,b3,b4 | | 3 | c1,c2 | | 4 | d1 | +----+-------------+ 4 rows in set mysql> |
2：通过SQL实现如题的要求
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> SELECT t.id, t.NAME, SUBSTRING_INDEX(SUBSTRING_INDEX(t. NAME, ',', b.seq), ',' ,- 1) AS name2, b.seq FROM t, (SELECT @seq := @seq + 1 AS seq FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3) r1, (SELECT 1 UNION SELECT 2 UNION SELECT 3) r2, (SELECT @seq := 0) r0 ORDER BY seq) b WHERE b.seq <= LENGTH(t. NAME) - LENGTH(REPLACE(t. NAME, ',', '')) + 1 ORDER BY id, seq; +----+-------------+-------+-----+ | id | NAME | name2 | seq | +----+-------------+-------+-----+ | 1 | a1,a2,a3 | a1 | 1 | | 1 | a1,a2,a3 | a2 | 2 | | 1 | a1,a2,a3 | a3 | 3 | | 2 | b1,b2,b3,b4 | b1 | 1 | | 2 | b1,b2,b3,b4 | b2 | 2 | | 2 | b1,b2,b3,b4 | b3 | 3 | | 2 | b1,b2,b3,b4 | b4 | 4 | | 3 | c1,c2 | c1 | 1 | | 3 | c1,c2 | c2 | 2 | | 4 | d1 | d1 | 1 | +----+-------------+-------+-----+ 10 rows in set mysql> |