Mysql Group_concat Usage Precautions

The Group_concat function returns a string result that is composed of concatenated values in the grouping, often used with group by.

If you need a custom delimiter, you can use SEPARATOR.

Example:

SELECT group_concat (ID) IDs from sys_oem_resources WHERE The PID is not a NULL GROUP by PID;

SELECT group_concat (id SEPARATOR ' * ') IDs from Sys_oem_resources WHERE pid are not NULL GROUP by PID;

Note the example:

Do not use Group_concat, perhaps you now use the Group_concat get the result is hidden bug.

The results of the Group_concat are limited by the Group_concat_max_len variable.

The default Group_concat_max_len = 1024, which means that the length of the string exceeds 1024 bytes is truncated.

See the default length of Group_concat by command "show variables like ' Group_concat_max_len '":

Mysql> Show variables like ' Group_concat_max_len ';
+----------------------+-------+
| variable_name | Value |
+----------------------+-------+
| Group_concat_max_len | 1024 |
+----------------------+-------+
1 row in Set

Add configuration to MySQL config file: group_concat_max_len = 1 (-1 for maximum or set length according to actual requirement), after configuration, restart MySQL service, see below:

Mysql> Show variables like ' Group_concat_max_len ';
+----------------------+------------+
| variable_name | Value |
+----------------------+------------+
| Group_concat_max_len | 4294967295 |
+----------------------+------------+
1 row in Set

Mysql Group_concat Usage Precautions