建表语句:
CREATE TABLE `testmysql_hangzhuanlie` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(50) DEFAULT NULL,
`tags` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=650 DEFAULT CHARSET=utf8mb4 COMMENT='测试表'
表中示例数据:
行转列:
SELECT
a.id,
a.username,
SUBSTRING_INDEX( SUBSTRING_INDEX( a.tags, ',', b.help_topic_id + 1 ), ',',- 1 ) AS tag
FROM
`testmysql_hangzhuanlie` AS a
JOIN mysql.help_topic AS b
ON b.help_topic_id < ( length( a.tags ) - length( REPLACE ( a.tags, ',', '' ) ) + 1 );
执行效果(以上sql是按逗号分割):