开发过程中,因为历史原因或性能原因,需要对表的列数据转为行数据,或行数据转换为列数据使用,本文将介绍mysql表数据行列转换的方法,提供完整演示例子及sql技巧。

1.行转列

 
创建测试数据表及数据

CREATE TABLE `option` (
 `category_id` int(10) unsigned NOT NULL COMMENT '分类id',
 `name` varchar(20) NOT NULL COMMENT '名称',
 KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `option` (`category_id`, `name`) VALUES
(1, '大'),
(1, '中'),
(1, '小'),
(2, '奔驰'),
(2, '宝马'),
(3, '2015'),
(3, '2016'),
(3, '2017'),
(3, '2018'),
(4, '1m'),
(4, '2m');

mysql> select * from `option`;
+-------------+--------+
| category_id | name   |
+-------------+--------+
|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     |
|           4 | 2m     |
+-------------+--------+

行转列后,期望得到以下结果

+-------------+---------------------+
| category_id | name                |
+-------------+---------------------+
|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 |
|           4 | 1m,2m               |
+-------------+---------------------+

 
行转列,可以使用group_concat()函数结合group by实现。

group_concat()函数可以得到表达式结合体的连结值,默认分隔符为逗号,可以通过separator设置为其他分隔符。

注意:group_concat()函数对返回的结果有长度限制,默认为1024字节,不过对于正常的情况已经足够。

关于group_concat()函数的使用可以参考我之前的文章:《mysql函数concat与group_concat使用说明》
 
执行结果:

mysql> select category_id,group_concat(name) as name from `option` group by category_id order by category_id;
+-------------+---------------------+
| category_id | name                |
+-------------+---------------------+
|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 |
|           4 | 1m,2m               |
+-------------+---------------------+


2.列转行

 
创建测试数据表及数据

CREATE TABLE `option2` (
 `category_id` int(10) unsigned NOT NULL COMMENT '分类id',
 `name` varchar(100) NOT NULL COMMENT '名称集合'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `option2` (`category_id`, `name`) VALUES
(1, '大,中,小 '),
(2, '奔驰,宝马'),
(3, '2015,2016,2017,2018'),
(4, '1m,2m');

mysql> select * from `option2`;
+-------------+---------------------+
| category_id | name                |
+-------------+---------------------+
|           1 | 大,中,小            |
|           2 | 奔驰,宝马           |
|           3 | 2015,2016,2017,2018 |
|           4 | 1m,2m               |
+-------------+---------------------+

列转行后,期望得到以下结果

+-------------+--------+
| category_id | name   |
+-------------+--------+
|           1 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     |
|           4 | 2m     |
+-------------+--------+

 
列转行比行转列复杂,对于列内容是用分隔符分隔的数据,我们可以使用substring_index()函数进行分割输出,并结合笛卡尔积来实现循环。

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;

 
执行结果:

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 | 大     |
|           1 | 中     |
|           1 | 小     |
|           2 | 奔驰   |
|           2 | 宝马   |
|           3 | 2015   |
|           3 | 2016   |
|           3 | 2017   |
|           3 | 2018   |
|           4 | 1m     |
|           4 | 2m     |
+-------------+--------+