MySQL table data row and column conversion method

During the development process, for historical reasons or performance reasons, you need to convert the table column data to row data, or row data to column data use, this article will introduce the MySQL table data column conversion method, provide a complete demonstration examples and SQL skills.

1. Row to Column

Create test data tables and data

CREATE TABLE ' option ' (' category_id ' int (ten) unsigned NOT NULL COMMENT ' class ID ', ' name ' varchar () NOT null COMMENT ' name ', KEY ' category_id ' (' category_id ')) Engine=innodb DEFAULT charset=utf8;insert into ' option ' (' category_id ', ' name ') VALUES (1, ' big '), (1, ' in '), (1, ' small '), (2, ' Mercedes '), (2, ' BMW '), (3, ') ', (3, ') ', (3, ' 2018 '), (3, ' 1m '), (4, ' 2m '); Mysql> select * from ' option '; +-------------+--------+| category_id | Name   |+-------------+--------+|           1 | Big     | |           1 | In     | |           1 | Small     | |           2 | Mercedes-Benz   | |           2 | BMW   | |           3 | |   |           3 | |   |           3 |   | |           3 | 2018   | |           4 | 1m     | |           4 | 2m     |+-------------+--------+

After row to column, expect to get the following result

+-------------+---------------------+| category_id | Name                |+-------------+---------------------+|           1 | Big, Medium, small            | |           2 | Mercedes-Benz, BMW           | |           3 | 2015,2016,2017,2018 | |           4 | 1m,2m               |+-------------+---------------------+

Row to column, you can use the group_concat () function in conjunction with the group by implementation.

The Group_concat () function can get the link value of the expression binding, the default delimiter is a comma, and can be set to another delimiter by separator.

Note: the Group_concat () function has a length limit on the returned result, which defaults to 1024 bytes, but is sufficient for normal conditions.

The use of the Group_concat () function can be referred to my previous article: "MySQL function concat and group_concat usage instructions"

Execution Result:

Mysql> Select Category_id,group_concat (name) as name from the ' option ' Group by category_id ORDER by category_id;+--------- ----+---------------------+| category_id | Name                |+-------------+---------------------+|           1 | Big, Medium, small            | |           2 | Mercedes-Benz, BMW           | |           3 | 2015,2016,2017,2018 | |           4 | 1m,2m               |+-------------+---------------------+

2. Career change

Create test data tables and data

CREATE TABLE ' option2 ' (' category_id ' int (ten) unsigned NOT NULL COMMENT ' class ID ', ' name ' varchar (+) ' NOT null COMMENT ' name set  Engine=innodb DEFAULT charset=utf8;insert into ' option2 ' (' category_id ', ' name ') VALUES (1, ' big, Medium, Small '), (2, ' Mercedes Benz, BMW '), (3, ' 2015,2016,2017,2018 '), (4, ' 1m,2m ');mysql> select * from ' option2 '; +-------------+---------------------+| category_id | Name                |+-------------+---------------------+|           1 | Big, Medium, small            | |           2 | Mercedes-Benz, BMW           | |           3 | 2015,2016,2017,2018 | |           4 | 1m,2m               |+-------------+---------------------+

After a career change, expect to get the following results

+-------------+--------+| category_id | Name   |+-------------+--------+|           1 | Big     | |           1 | In     | |           1 | Small     | |           2 | Mercedes-Benz   | |           2 | BMW   | |           3 | |   |           3 | |   |           3 |   | |           3 | 2018   | |           4 | 1m     | |           4 | 2m     |+-------------+--------+

Column change is more complex than row-to-column, and for column content is delimited data, we can use the substring_index () function to split the output, and combine the Cartesian product to achieve the loop.

Select A.category_id,substring_index (Substring_index (A.name, ', ', b.category_id), ', ', -1) as name from ' Option2 ' as Ajoin ' Option2 ' as B on b.category_id<= (Length (a.name)-Length (replace (A.name, ', ', ")) +1) Order by a.category_id, b.category_id;

Execution Result:

Mysql> Select A.category_id,substring_index (Substring_index (A.name, ', ', b.category_id), ', ', -1) as name from ' Option2 ' as a,    join ' option2 ' as B on b.category_id<= (Length (a.name)-Length (replace (A.name, ', ', ')) +1)    ORDER BY a.category_id,b.category_id;+-------------+--------+| category_id | name   |+-------------+--- -----+|           1 | Big     | |           1 | In     | |           1 | Small     | |           2 | Mercedes-Benz   | |           2 | BMW   | |           3 | |   |           3 | |   |           3 |   | |           3 | 2018   | |           4 | 1m     | |           4 | 2m     |+-------------+--------+

This article explains the MySQL table data row and column conversion method, more relevant content please pay attention to the PHP Chinese network.

Related recommendations:

How to quickly view configuration files with Nginx

A method of combining multiple one-dimensional combinations of PHP into two-dimensional arrays

PHP Returns a method that specifies multiple columns in an array