mysql 参数 分隔符_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)

二.使用经过

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)

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.查看服务器中设置

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;