Perfect solution to the problem of data being truncated when using the group concat function in Mysql5.7

The day before yesterday, I encountered a problem in the production environment: the data selected using the GROUP_CONCAT function was truncated, and the maximum length did not exceed 1024 bytes. At first, I thought it was a problem with the navicat client itself limiting the field length. Later, I deliberately re-INSERTed a field with a length exceeding 1024 bytes, but Navicat was able to display it completely, so the problem with Navicat was ruled out.

Then I thought of the familiar number 1024. Could it be that the C++ framework was processed when receiving the data transmitted by MySQL through the socket? So I manually printed this field in the log and found that even if the data length exceeds 1024 bytes, it can still be displayed completely.

1. Find the cause

At this point, we can only start from SQL statements. I searched online for questions about GROUP_CONCAT data truncation, and the answers all pointed to the group_concat_max_len parameter, whose default value is exactly 1024. You can view this default value directly in the database using the following command:

mysql> show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| group_concat_max_len | 1024 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql>

The MySQL official manual defines it as The maximum permitted result length in bytes for the GROUP_CONCAT The maximum permitted result length in bytes for the GROUP_CONCAT() function .

2. Problem Solving

Just adjust group_concat_max_len to the maximum value. The official MySQL 5.7 manual gives the following definition:

Since BZ's test virtual machine MySQL5.7.19 is 64-bit, group_concat_max_len can be configured to the maximum value by the following two methods:

#### Method 1: Modify the MySQL configuration file my.cnf and add group_concat_max_len = 18446744073709551615 in the [mysqld] node
#### Method 2: Directly set the console to take effect immediately -- [Required operation] Change the global configuration----
SET GLOBAL group_concat_max_len=18446744073709551615;
-- [Optional operation] Make the configuration take effect immediately in the current session. Other logged-in session terminals need to be restarted for the configuration to take effect.
SET SESSION group_concat_max_len=18446744073709551615;

3 Test results

The second method is used here. By executing SELECT LENGTH(GROUP_CONCAT(Fremark)) FROM account; and comparing the results, we can find that the problem of data truncation when using GROUP_CONCAT in MySQL 5.7 has been successfully solved.

Summarize

The above is the perfect solution to the problem of data truncation when using the group concat function in Mysql5.7. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!