MySQL GROUP_CONCAT limitation solution

effect:

The GROUP_CONCAT function can concatenate a field value into a string. The default separator is a comma, i.e., ",

If you need a custom separator, you can use SEPARATOR

like:

SELECT GROUP_CONCAT(name SEPARATOR '_') FROM user

limit:

The default length of the string concatenated by GROUP_CONCAT is 1024 bytes. If it exceeds this length, it will be truncated.

Query the default length:

Order

SHOW VARIABLES LIKE 'group_concat_max_len'

Operation method: (as shown in the picture)

Adjust the length setting method:

(1) Add the following to the MySQL configuration file:

File: my.ini

Additional settings:

group_concat_max_len = 102400

(2) SQL statement:

SET GLOBAL group_concat_max_len = 102400;SET SESSION group_concat_max_len = 102400;

Note: The disadvantage of this method is that the settings will become invalid after restarting the service

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.