I recently encountered a problem at work:
Some logical processing of our system is implemented using stored procedures, but one day the customer service reported that the order failed to be placed, and I checked the products involved in the order. Basic resources, no problem.
There are two lines of code in the stored procedure for placing an order:
1 declare _err int default 0;2 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set _err=1;3 DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set _err=2;
After executing the stored procedure, the variable _err will return 1. You can only debug the stored procedure to find the problem. . When debugging the following paragraph, I found that after executing this sql, _err became 1
1 select group_concat(concat(@room_name,',',run_date,',',total_count) separator '|') into @order_desc from tmp_order_detail order by run_date;
Because there is more data in the temporary tabletmp_order_detail , so I guessed it was a problem with group_concat, so I reduced the data in the temporary table by half, and found that it was successful, so I guessed whether group_concat has a length limit, and I googled it, and sure enough.
About the group_concat function:
group_concat: The default connectable length is 1024; if the maximum length has been set, exceeding this length will Truncated to this length;
In the query (select) statement, after using group_concat, the limit will become invalid;
Solution:
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;
I used the second method above in the stored procedure to execute the stored procedure, OK, success!
Get off work (today is Sunday)! ! !
The above is the detailed content of Detailed explanation of examples of using group_concat() function in MySQL. For more information, please follow other related articles on the PHP Chinese website!