MySQL one line to many lines (split according to specific symbols)

Recently, there is a demand like this .
The query estimated score is within a certain range , For example, some people rate a person as 7 branch ,1 branch ,4 branch . Then query the score range in 5 be assigned to 10 Points of , If one of the scores is within the range, the data is valid . The data stored in the data table is as follows .

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');

Because there may be decimals in scoring , So at first, the query range is 5 - 10 branch , Cycle writing 5,6,7,8,9,10. And then use find_in_set You can't . So we can only consider using between. But the data table stores comma separated strings , So it's like how to split one piece of data into multiple pieces , And then use between Inquire about .

Use select * from test

that , Convert the data into the following , How to realize it :

Ordinary sql Realization ( Need to rely on mysql.help_topic surface )

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 Unauthorized handling method

mysql.help_topic The function of is to SUBSTRING_INDEX Data from function ( That is, it is divided according to the delimiter ) Connect the data to make Cartesian product .

If mysql.help_topic No authority , You can create a temporary table by yourself , Used to join the query with the table to be queried .

The field can be divided into several strings at most :

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

Create a temporary table , And add data to the temporary table :

Be careful :

1. The temporary table must have a column from 0 perhaps 1 Self increasing data at the beginning
2. Temporary table name is optional , There can be only one... Field
3. The amount of data temporarily represented must be greater than MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1) It's worth a lot

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 ();

Inquire about ：

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 );

Function means

1.REPLACE function :

 hold   character string    a,b,c,d    Replace the comma inside with an empty string 
SELECT REPLACE('a,b,c,d', ',', '');
--  Output : abcd

that ：

#  Get the number of commas 
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));

#  There are several elements after being separated by commas , After the partition here is  a b c d, Namely  4  Elements  
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1);

2.SUBSTRING_INDEX function :

SUBSTRING_INDEX Is a string interceptor

SUBSTRING_INDEX(str, delim, count)

1. str : Represents the string that needs to be split
2. delim : Represents a separator , Split by a character
3. count : When count Is a positive number , Take the first place n All characters before a separator ; When count It's a negative number , Take the penultimate n All characters after a delimiter .

for example :

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

that ：

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

One line to many lines principle

go back to 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 ) Is to get tmp_help_topic Tabular help_topic_id The value of the field is used as pre_score The number of substrings of the field
2. Used join Will put the field pre_score It is divided into ( LENGTH( t.pre_score ) - LENGTH( REPLACE ( t.pre_score, ',', '' )) + 1 ) That's ok , And the fields in each row are just pre_score Field help_topic_id Substring

Final implementation requirements

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

Reference resources ：MySql One line becomes many lines ( Divide according to specific symbols )