MYSQL函數group_concat的使用
今天對一批數據要遷移轉換，查了下，有group_concat這個函數簡單實現字段的列轉行設置，過程記錄如下
一.測試數據准備
mysql> use test;
Database changed
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 123 |
| 789 |
| 345 |
| 78 |
+------+
5 rows in set (0.00 sec)
二.使用經過 www.2cto.com
1.以默認的逗號作為分隔符
mysql> select group_concat(id) from t_kenyon;
+------------------+
| group_concat(id) |
+------------------+
| 1,123,789,345,78 |
+------------------+
1 row in set (0.00 sec)
2.對ID值進行排序後行轉列
mysql> select group_concat(id order by id) from t_kenyon;
+------------------------------+
| group_concat(id order by id) |
+------------------------------+
| 1,78,123,345,789 |
+------------------------------+
1 row in set (0.00 sec)
3.使用其他分割符，如*和;等
mysql> select group_concat(id separator '*') from t_kenyon;
+--------------------------------+
| group_concat(id separator '*') |
+--------------------------------+
| 1*123*789*345*78 |
+--------------------------------+
1 row in set (0.00 sec) www.2cto.com
4.分隔符與排序結合起來用
mysql> select group_concat(id order by id separator '_') from t_kenyon;
+--------------------------------------------+
| group_concat(id order by id separator '_') |
+--------------------------------------------+
| 1_78_123_345_789 |
+--------------------------------------------+
1 row in set (0.00 sec)
5.對相同的值分組
mysql> insert into t_kenyon values (78);
Query OK, 1 row affected (0.00 sec)
mysql> select group_concat(id) from t_kenyon group by id;
+------------------+
| group_concat(id) |
+------------------+
| 1 |
| 78,78 |
| 123 |
| 345 |
| 789 |
+------------------+
5 rows in set (0.00 sec)
三.參數設置與限制說明
1.查看服務器中設置 www.2cto.com
mysql> show variables like '%group_concat%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| group_concat_max_len | 1024 |
+----------------------+-------+
1 row in set (0.00 sec)
以上設置的值說明當前是默認長度1KB
2.改變參數值
方法一：修改配置文件中參數，新增 group_concat_max_len = 10240
方法二：在會話中實現，全局或當前session中
SET GLOBAL group_concat_max_len=10240;
SET SESSION group_concat_max_len=10240;
作者 kenyon
