mysql单列数据指定符号分割列(单行转单列)

mysql数据

查询结果

查询sql

SELECT
	SUBSTRING_INDEX( SUBSTRING_INDEX( ( SELECT group_concat( NAME SEPARATOR ';' ) FROM a ), ';', help_topic_id + 1 ), ';',- 1 ) AS num 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < LENGTH( ( SELECT group_concat( NAME SEPARATOR ';' ) FROM a ) )- LENGTH(
REPLACE ( ( SELECT group_concat( NAME SEPARATOR ';' ) FROM a ), ';', '' ))+ 1

不依赖mysql库的写法

set @i = 0;
SELECT
	@i,
	SUBSTRING_INDEX((
		SELECT
			SUBSTRING_INDEX( ( SELECT group_concat( NAME SEPARATOR ';' ) FROM a WHERE id = 1 ), ';', @i := @i + 1 )),
	';',
	- 1 
) 
FROM
	(
	SELECT
		1 UNION ALL
	SELECT
		1 UNION ALL
	SELECT
		1 UNION ALL
	SELECT
		1 UNION ALL
	SELECT
		1 UNION ALL
	SELECT
		1 UNION ALL
	SELECT
		1 UNION ALL
	SELECT
		1 
	) AS tmp

配合java使用

set @i = 0;
SELECT
	@i,
	SUBSTRING_INDEX((
		SELECT
			SUBSTRING_INDEX( ( SELECT group_concat( NAME SEPARATOR ';' ) FROM a WHERE id = 1 ), ';', @i := @i + 1 )),
	';',
	- 1 
) 
FROM
	(
	 SELECT 1
		<foreach item = "index" collection = "countList">
			UNION ALL SELECT 1
		</foreach >
	) AS tmp

int[] countList = {1, 2, 3, 4, 5, 6, 7, 8, 9};

暂时未找到能直接遍历出指定个数次的sql办法,所以只能在sql中强行拼接出指定个数或者用mybatis生成,等想到再更新