Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how mysql splits a line of data into multiple rows of data according to a comma". In daily operation, I believe many people have doubts about how mysql splits one line of data into multiple rows of data according to commas. The editor consulted all kinds of materials and sorted out simple and useful methods of operation. I hope it will be helpful for you to answer "how mysql splits one line of data into multiple rows of data according to commas". Next, please follow the editor to study!

Separation effect

-- before separation

1,2,3,4

-- after separation

one

two

three

four

Separate command Lin

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);命令行解释 help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表 可以用做计数的临时表，查询的语句只会用help_topic计数，超出的部分其实都是脏数据

B.help_topic_id < get the total number of rows separated

B.help_topic_id < (LENGTH)-LENGTH (REPLACE) + 1)

This statement is actually a traversal, with help_topic_id incremented from 0 to

Intercept sequentially, first intercepting all characters before the nth delimiter, and then intercepting the last bit of the string

1-1

1, 2-2.

1, 2, 2, 3-- 3

1, 2, 3, 4-4.

The treatment method of mysql.help_topic without limitation

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

If mysql.help_topic does not have permission, you can create your own temporary table to join the query with the table you want to query.

Getting this field can be split into up to several strings:

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

Create a temporary table and add data to it:

Note:

The temporary table must have a column of self-increasing data starting at 0 or 1.

Temporary table name is arbitrary, there can be only one field

The amount of data temporarily represented must be larger than the value of MAX (LENGTH (a.name)-LENGTH (REPLACE (a.name,',')) + 1)

Involved function

Substring_index (str,delim,count)

The parameter name explains the string delim delimiter that str needs to split. Split count by a character if count is positive, take all characters before the nth delimiter; when count is negative, take all characters after the nth delimiter from the last.

Replace (str, from_str, to_str)

Parameter name explains the string that str needs to replace from_str needs to be replaced to_str needs to replace string

Length (str) gets the string length

At this point, the study on "how mysql splits a row of data into multiple rows of data according to commas" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.