MySql 一行变多行(根据特定符号分割)

最近有一个需求是这样的。
查询预估分数在某个范围内，就比如某些人给一个人打分分别为7分，1分，4分。然后查询分数范围在5分到10分的，所打分数其中有一个在范围内就算该条数据有效。数据表存储的数据是下面这样的。

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pre_score` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `pre_score`) VALUES (1, '5.5');
INSERT INTO `test` (`id`, `pre_score`) VALUES (2, '7,2');
INSERT INTO `test` (`id`, `pre_score`) VALUES (3, '1');
INSERT INTO `test` (`id`, `pre_score`) VALUES (4, '5.5');
INSERT INTO `test` (`id`, `pre_score`) VALUES (5, '2');
INSERT INTO `test` (`id`, `pre_score`) VALUES (6, '3');
INSERT INTO `test` (`id`, `pre_score`) VALUES (7, '4,8');
INSERT INTO `test` (`id`, `pre_score`) VALUES (8, '2,5');
INSERT INTO `test` (`id`, `pre_score`) VALUES (9, '5.5');
INSERT INTO `test` (`id`, `pre_score`) VALUES (10, '6');

因为打分还有可能存在小数，所以最开始考虑用查询范围为 5 - 10分，循环写出5,6，7,8，9,10。然后使用find_in_set是不行的。所以只能考虑使用between。但是数据表存储的是逗号分割的字符串，所以就像如何才能把一条数据拆成多条，然后使用between查询。

使用select * from test

那么，把数据转换成为下面这样，需要怎么样实现呢:

普通 sql 实现(需要依赖 mysql.help_topic 表)

SELECT
	`id`,
	substring_index( substring_index( t.pre_score, ',', b.help_topic_id + 1 ), ',',- 1 ) AS pre_score 
FROM
	test t
	INNER JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( t.pre_score ) - LENGTH( REPLACE ( t.pre_score, ',', '' )) + 1 ) 
WHERE
	`pre_score` <> '';	

mysql.help_topic 无权限处理办法

mysql.help_topic 的作用是对 SUBSTRING_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。

如果 mysql.help_topic 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。

获取该字段最多可以分割成为几个字符串:

SELECT MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1) FROM `test` a;

创建临时表，并给临时表添加数据:

注意:

1.临时表必须有一列从 0 或者 1 开始的自增数据
2.临时表表名随意，字段可以只有一个
3.临时表示的数据量必须比 MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1) 的值大

DROP TABLE IF EXISTS `tmp_help_topic`;
CREATE TABLE IF NOT EXISTS `tmp_help_topic` (
  `help_topic_id` bigint(20) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`help_topic_id`)
);
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();
INSERT INTO `tmp_help_topic`() VALUES ();

查询：

SELECT
	a.id,
	a.num,
	SUBSTRING_INDEX( SUBSTRING_INDEX( a.`name`, ',', b.help_topic_id ), ',',- 1 ) NAME 
FROM
	test a
	JOIN tmp_help_topic b ON b.help_topic_id <= ( LENGTH( a.`name` ) - LENGTH( REPLACE ( a.`name`, ',', '' )) + 1 );

函数的意思

1.REPLACE 函数:

把 字符串   a,b,c,d   里面的逗号替换成空字符串
SELECT REPLACE('a,b,c,d', ',', '');
-- 输出: abcd

那么：

# 获取逗号的个数
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));

# 按照逗号分割后会有几个元素，这里分割后就是 a b c d，就是 4 个元素 
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1);

2.SUBSTRING_INDEX 函数:

SUBSTRING_INDEX 是字符串截取函数

SUBSTRING_INDEX(str, delim, count)

1. str : 表示需要拆分的字符串
2. delim : 表示分隔符，通过某字符进行拆分
3. count : 当 count 为正数，取第 n 个分隔符之前的所有字符；当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

例如:

SELECT SUBSTRING_INDEX('a*b*c*d', '*', 1); -- 返回: a
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 2); -- 返回: a*b 
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 3); -- 返回: a*b*c
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 4); -- 返回: a*b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -1); -- 返回: d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -2); -- 返回: c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -3); -- 返回: b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -4); -- 返回: a*b*c*d

那么：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 1), '*', -1); -- 返回: a
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 2), '*', -1); -- 返回: b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 3), '*', -1); -- 返回: c
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 4), '*', -1); -- 返回: d

一行变多行原理

回到SQL

SELECT
	`id`,
	substring_index( substring_index( t.pre_score, ',', b.help_topic_id + 1 ), ',',- 1 ) AS pre_score 
FROM
	test t
	INNER JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( t.pre_score ) - LENGTH( REPLACE ( t.pre_score, ',', '' )) + 1 ) 
WHERE
	`pre_score` <> '';	

1. SUBSTRING_INDEX(SUBSTRING_INDEX(a.name, ',', b.help_topic_id), ',',-1 ) 就是获取 tmp_help_topic 表的 help_topic_id 字段的值作为 pre_score 字段的第几个子串
2. 使用了 join 就会把字段 pre_score分为( LENGTH( t.pre_score ) - LENGTH( REPLACE ( t.pre_score, ',', '' )) + 1 ) 行，并且每行的字段刚好是 pre_score 字段的第 help_topic_id 个子串

最终实现需求

SELECT
	* 
FROM
	(
	SELECT
		`id`,
		substring_index( substring_index( t.pre_score, ',', b.help_topic_id + 1 ), ',',- 1 ) AS pre_score 
	FROM
		test t
		INNER JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( t.pre_score ) - LENGTH( REPLACE ( t.pre_score, ',', '' )) + 1 ) 
	WHERE
		`pre_score` <> '' 
	) a 
WHERE
	pre_score BETWEEN 5 
	AND 10

参考：MySql 一行变多行(根据特定符号分割)