MySQL cleverly uses temporary tables to convert comma-separated strings into rows

• 
  

• Separation effect

• Separate the command line

• Command line explanation

• Involved function

• 
  

• substring_index(str, delim, count)

• replace( str, from_str, to_str)

• length(str) Get the length of the string

• 
  

Separation effect

-- 分隔前1,2,3,4-- 分隔后12341234567

Separate the command line

SELECT 
	substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) resultFROM 
	mysql.help_topic bwhere 
	b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1);123456

Command line explanation

help_topic itself is a Mysql help to explain the comment table, used to explain the various proper nouns of Mysql, because the data ID of this table is increased from 0, which is convenient for us to count, but the 8.0.17 version only has 686 data. Beyond this number, we need to customize a table

Can be used as a temporary table for counting, the query statement will only use help_topic to count, the excess part is actually dirty data

b.help_topic_id <Get the total number of separated rows

b.help_topic_id <  (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1)1

This statement is actually a traversal, help_topic_id starts from 0 and increases to

Intercept in order, first intercept all characters before the nth separator, and then intercept the last digit of the string

1  		—— 11,2		—— 21,2,3	—— 31,2,3,4	—— 41234

Involved function

substring_index(str, delim, count)

replace( str, from_str, to_str)

length(str) Get the length of the string