Shulou(Shulou.com)06/01 Report--

The day before yesterday, I encountered a problem in the production environment: the data selected by using the GROUP_CONCAT function was truncated, and the maximum length did not exceed 1024 bytes. At first, I thought it was the navicat client itself that limited the length of the field. Later deliberately re-INSERT a field length of more than 1024 bytes of data, but navicat can be fully displayed, so the navicat problem is ruled out.

Then think of the familiar number 1024. Could it be that the C++ framework was processed when receiving data from MySQL via socket? So manually print this field in the log and find that even if the data length exceeds 1024 bytes, it can still be displayed completely.

1 Find out why

At this point, you can only start from SQL statements. Search the GROUP_CONCAT data truncation question online, the answer points to the group_concat_max_len parameter, its default value is exactly 1024. This default value can be viewed directly in the database with 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 maximum permitted result length in bytes for the GROUP_CONCAT() function. This limits the length of GROUP_CONCAT data.

2 Solving problems

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

Because BZ's test virtual machine MySQL 5.7.19 is 64-bit, you can configure group_concat_max_len to the maximum value by the following two methods:

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

3 Test results

The second method used here, by performing SELECT LENGTH(GROUP_CONCAT(Fremark)) FROM account; results comparison, you can find that MySQL 5.7 has successfully solved the use of GROUP_CONCAT data truncation problem.

summary

The above is the perfect solution to the problem of using group concat function data truncation in Mysql 5.7 introduced by Xiaobian. I hope it will help you. If you have any questions, please leave a message to me. Xiaobian will reply to you in time. Thank you very much for your support!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.