Use of the MYSQL function group_concat

The MYSQL function group_concat is used to migrate and convert a batch of data today. After checking the function, group_concat is used to easily implement column-to-row setting of fields. The procedure is recorded as follows. test data preparation mysql> use test; Database changedmysql> select * from t_kenyon; + ------ + | id | + ------ + | 1 | 123 | 789 | 345 | 78 | + ------ + 5 rows in set (0.00 sec) II. use through www.2cto.com 1. use the default comma as the separator mysql> select group_concat (id) from t_kenyon; + ---------------- + | group_concat (id) | + ------------------ + | 1,123,789,345, 78 | + ------------------ + 1 row in set (0.00 sec) 2. sort the ID values and convert them to mysql> select group_concat (id order by id) from t_kenyon; + consumer + | group_concat (id order by id) | + ---------------------------- + |, 123,345,789 | + ------------------------------ + 1 row in set (0.00 sec) 3. use other delimiters, such as * And;. 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. use mysql> select group_concat (id order by id separator '_') from t_kenyon; + ------------------------------------------------ + | group_concat (id order by id separator '_') | + ---------------------------------------- + | 201778_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)
III. parameter settings and restrictions 1. check that www.2cto.com mysql> show variables like '% group_concat %' is set on the server '; + ---------------------- + ------- + | Variable_name | Value | + ---------------------- + ------- + | group_concat_max_len | 1024 | + ---------------------- + ------- + 1 row in set (0.00 sec) the above value indicates that the current default length is 1 KB 2. method 1: Modify parameters in the configuration file and add group_concat_max_len = 10240. Method 2: set global group_concat_max_len = 10240 globally or in the current session; set session group_concat_max_len = 10240; prepared by kenyon