Solution to the problem of using Group_concat () function data in MySQL for long error

Recently in the Office software project, the Development Authority indicator encountered a problem:
Some of our system's logical processing is implemented with stored procedures, but one day the customer feedback said the authority index allocation error, check the allocation of permissions data involved in the data rights base resources, no problem.
There are two lines of code in the stored procedure for the allocation of permission metrics:

SET @tests = ';
SELECT
Group_concat (DISTINCT REPLACE (CONCAT ("", (Indicator_content), ""), ', ', ', ')
As Allnumber into @tests

After executing the stored procedure, the variable @tests returns a piece of data, only debugging the stored procedure for a problem. Debugging to the following paragraph, when found to execute the SQL, @tests becomes an incomplete statement
1 Select Group_concat (DISTINCT REPLACE (CONCAT ("", (Indicator_content), ""), ', ', ' ",") into @tests from US Erofindicator;
Because the Userofindicator table stores the permission metrics are stored by the metric number of data is more, so guess is not group_concat problem, so I put the Userofindicator table in half of the data, that is, according to a user query, Found success, so guess Group_concat is not a length limit, Google a bit, sure enough is the length of this function is not caused.
About the Group_concat function:
Group_concat when the amount of data is small, but there is a problem when the amount of data is large;
Group_concat: The default length of the connection is 1024, if the maximum length has been set, the length will be truncated to this length;
In a query (SELECT) statement, when Group_concat is used, the limit is invalidated;
Workaround:
1. Modify the MySQL configuration file:
#需要设置的长度
Group_concat_max_len = 5120

2. You can also use SQL statement settings:
SET GLOBAL group_concat_max_len=5120;
SET SESSION group_concat_max_len=5120;
But the above two methods seem I have a problem, the second method to take effect, the length of the check is not a problem, but the execution of the statement has a problem;

Finally I use the third method as follows:

3, modify the MySQL configuration file method, after modifying the MySQL service restart, you can set the scope of the GROUP_CONCAT through the statement, such as:

SET GLOBAL Group_concat_max_len=-1;

SET SESSION Group_concat_max_len=-1;

Set MySQL maximum length setting above,

Mysql> Show variables like "%concat%"; Command checked, the amount of data is very long.

Note that the Linux settings need to be in/etc/my.cnf and need to be set up in order to operate global with Administrator privileges;

I used the above third method in the stored procedure, executes the stored procedure, OK, the perfect fix!

But sometimes we'd better use the previous two in the production environment, try to avoid the online restart of the database service solution.

Solution to the problem of using Group_concat () function data in MySQL for long error