1、借助一张id连续的mysql表,并使得存储的id值尽可能大于字段里存的值个数
如果使用表自增id,则id默认从1开始:
2、拼写sql
t_auto_id 表中 id最小值为1:
SELECT
a.id,
a.name,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.address, ',', b.id ), ',', - 1 ) AS address
FROM
`t_user` AS a
JOIN t_auto_id AS b ON b.id <= ( length( a.address ) - length( REPLACE ( a.address, ',', '' ) ) + 1 )
原数据:
列转行之后效果:
注:如果t_auto_id 表中 id最小值为0,则需要调整sql为:
SELECT
a.id,
a.name,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.address, ',', b.id + 1 ), ',', -1 ) AS address
FROM
`t_user` AS a
JOIN t_auto_id AS b ON b.id < ( length( a.address ) - length( REPLACE ( a.address, ',', '' ) ) + 1 )