Today, we want to migrate and convert a batch of data. We checked that the group_concat function is used to easily implement column-to-row settings for fields. The procedure is as follows. test data preparation mysqlusetest; Databasechangedmysqlselect * fromt_kenyon; + ------ + | id | + ------ + | 1 | 123 | 789 | 345 | 78 | + ---
Today, we want to migrate and convert a batch of data. We checked that the group_concat function is used to easily implement column-to-row settings for fields. The procedure is as follows. test data preparation mysql use test; Database changed mysql select * from t_kenyon; + ------ + | id | + ------ + | 1 | 123 | 789 | 345 | 78 | + ---
Today, we want to migrate and convert a batch of data. Check that the group_concat function is used to easily implement column-to-row setting of fields. The procedure record is as follows:
I. Test Data Preparation
Mysql> use test;
Database changed
Mysql> select * from t_kenyon;
+ ------ +
| Id |
+ ------ +
| 1 |
| 1, 123 |
| 1, 789 |
| 1, 345 |
| 78 |
+ ------ +
5 rows in set (0.00 sec)
Ii. Usage
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 ID values and convert them into columns.
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. 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)
4. Combine separators and sorting
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. group the same value
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 |
| 1, 123 |
| 1, 345 |
| 1, 789 |
+ ------------------ +
5 rows in set (0.00 sec)
Iii. parameter settings and restrictions
1. View settings on the server
Mysql> show variables like '% group_concat % ';
+ ---------------------- + ------- +
| Variable_name | Value |
+ ---------------------- + ------- +
| Group_concat_max_len| 1024 |
+ ---------------------- + ------- +
1 row in set (0.00 sec)
The value of the preceding settings indicates that the current default length is 1 kb.
2. Change the parameter value
Method 1: Modify the parameters in the configuration file and add group_concat_max_len = 10240.
Method 2: Implement in session, global or current session
Set global group_concat_max_len = 10240;
Set session group_concat_max_len = 10240;
