How to convert a column of comma-separated values ​​into columns in MySQL

Preface

Sometimes you come across business tables that do not follow the first normal form design pattern. That is, multiple attribute values ​​are stored in one column. As shown in the following table

There are generally two common requirements (see the end of the article for test data)

1. Get all non-repeating values, such as

The SQL is as follows:

select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
from
 (select group_concat(distinct `value`) as col from `row_to_col`) as a
join
 mysql.help_topic as b
on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2. Display each value and its corresponding primary key, such as

The SQL is as follows:

select a.pk,substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)
from
 (select `value` as col,pk from `row_to_col`) as a
join
 mysql.help_topic as b
on b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

Implementation ideas:

Requirement 1:

1. Use the group_concat function to concatenate the values ​​of the value column into a comma-separated string, and then use the substring_index function to intercept the string
2. Through the substring_index function feature, we need to know how many commas there are in the string and the position of each comma
3. Number of commas = char_length(string)-char_length(replace(string,',',''))
4. Comma position = mysql.help_topic.id < number of commas [+1]
5. Finally, use the distinct function to remove duplicates from the intercepted single value

Notice:
1. The auto-increment ID of the mysql.help_topic table starts from 0, so the ID needs to be increased by 1 when intercepting. See: substring_index(a.col,',',b.help_topic_id+1)
2. When the last character of the value column is not a comma: the number of commas + 1 is to ensure that the value after the last comma is not missed during truncation, that is: char_length(a.col) - char_length(replace(a.col,',',''))+1;
When the last character of the value column is a comma: the number of commas does not need to be +1, just: char_length(a.col) - char_length(replace(a.col,',',''))
3. Because the id needs to be +1 when intercepting, the connection is < instead of <=. See: b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))[+1])
4. The auto-increment id of the mysql.help_topic (mysql version: 5.7.21-1) table, the maximum value is 636. If the number of commas in the string after group_concat is greater than this value, you need to process the value of the auto-increment id separately.

Requirement 2: The idea is basically the same as Requirement 1, except that the final query is different

Functions involved:

length: Returns the number of bytes occupied by the string, which is the length of the calculated field. A Chinese character or Chinese symbol is counted as three characters, and a number, letter or English symbol is counted as one character.
char_length: Returns the number of characters in a string. Regardless of whether it is a Chinese character, a number, a letter, or a symbol (regardless of Chinese or English), it is considered a character.
replace(str,old_string,new_string): Replace all occurrences of old_string in string str with new_string.
substring_index(truncated field, keyword, number of times the keyword appears): truncate the string. If the number of times the keyword appears is a negative number, it will be counted backwards to the end of the string.
group_concat([DISTINCT] fields to be connected (can be multiple, separated by commas) [Order BY sorting field ASC/DESC] [Separator 'separator']):
Concatenates the values ​​in the same group generated by group by and returns a string result. The default separator is comma.

Test data:

DROP TABLE IF EXISTS `row_to_col`;
CREATE TABLE `row_to_col` (
 `pk` int(11) NOT NULL AUTO_INCREMENT,
 `value` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');

refer to:
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

This is the end of this article about how to convert a column of comma-separated values ​​into rows and columns in MySQL. For more information about MYSQL comma-separated values ​​into rows and columns, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!