需求:
由目標(biāo)表testtt
得到分隔后的數(shù)據(jù)
步驟一:
創(chuàng)建中間表test
CREATE?TABLE?`test`?(
??`id`?int(11)?NOT?NULL,
??`name`?varchar(255)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?ROW_FORMAT=DYNAMIC
添加數(shù)據(jù)
insert into `test` (`id`, `name`) values('0',NULL);
insert into `test` (`id`, `name`) values('1',NULL);
insert into `test` (`id`, `name`) values('2',NULL);
insert into `test` (`id`, `name`) values('3',NULL);
insert into `test` (`id`, `name`) values('4',NULL);
insert into `test` (`id`, `name`) values('5',NULL);
insert into `test` (`id`, `name`) values('6',NULL);
insert into `test` (`id`, `name`) values('7',NULL);
insert into `test` (`id`, `name`) values('8',NULL);
insert into `test` (`id`, `name`) values('9',NULL);
insert into `test` (`id`, `name`) values('10',NULL);
insert into `test` (`id`, `name`) values('11',NULL);
insert into `test` (`id`, `name`) values('12',NULL);
?步驟二:
創(chuàng)建目標(biāo)表testtt
CREATE?TABLE?`testtt`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`value`?varchar(255)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=5?DEFAULT?CHARSET=utf8
添加數(shù)據(jù)
insert into `testtt` (`id`, `value`) values('1','1,2,3');
insert into `testtt` (`id`, `value`) values('2','4,5');
insert into `testtt` (`id`, `value`) values('3','6');
insert into `testtt` (`id`, `value`) values('4','7,8');
步驟三:
編寫sql
SELECT
a.id,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.value, ',', b.id + 1 ), ',', -1 ) AS VALUE
FROM testtt a
JOIN test b ON b.id < ( LENGTH( a.value ) - LENGTH( REPLACE ( a.value, ',', '' ))+ 1 )sql注意點(diǎn):
1,test表的行數(shù)要大于需要拆分字段拆分后的數(shù)量(這里是單條數(shù)據(jù)拆分后的數(shù)量),否者會(huì)少數(shù)據(jù)文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-817290.html
2,test表id需要從0連續(xù)的,否則也會(huì)少數(shù)據(jù)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-817290.html
到了這里，關(guān)于mysql把以逗號(hào)連接的字段拆分成列表的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
