Detailed explanation of examples of using group_concat() function in MySQL

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!