mysql splits a row of data into multiple rows based on commas

Separation effect

-- Before separation
1,2,3,4
-- After separation
1
2
3
4

Separate command lines

SELECT 
 substring_index(substring_index('1,2,3,4',',', b.help_topic_id + 1), ',', -1) result
FROM 
 mysql.help_topic b
where 
 b.help_topic_id < (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 1);

Command line explanation

help_topic itself is a Mysql help explanation comment table, which is used to explain various Mysql terms. Since the data ID of this table increases from 0, it is convenient for us to count, but the 8.0.17 version has only 686 data. If it exceeds this number, we need to customize a table

It can be used as a temporary table for counting. The query statement will only use help_topic for counting. The excess part is actually dirty data.

b.help_topic_id < Get the total number of rows after separation

b.help_topic_id < (LENGTH('1,2,3,4') - LENGTH(REPLACE('1,2,3,4', ',', '')) + 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, then intercept the last character of the string

1 —— 1
1,2 —— 2
1,2,3 —— 3
1,2,3,4 —— 4

mysql.help_topic no permission solution

The function of mysql.help_topic is to connect the data from the SUBSTRING_INDEX function (that is, the data separated by the delimiter) to make a Cartesian product.

If you do not have permission to use mysql.help_topic, you can create a temporary table to connect to the table you want to query.

Get the maximum number of strings that the field can be split into:

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

Create a temporary table and add data to it:

Notice:

• The temporary table must have a column of auto-increment data starting from 0 or 1
• The temporary table name is arbitrary, and there can be only one field
• The amount of data to be temporarily represented must be greater than MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1)

Involved functions

substring_index(str, delim, count)

replace(str, from_str, to_str)

length(str) Get the length of a string

This is the end of this article about how to split a row of data into multiple rows of data in MySQL based on commas. For more information about how to split a row of data into multiple rows in MySQL based on commas, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!