The group_concat () function data used in MySQL is intercepted (with the default length limit). Exercise caution !, Mysqlgroup_concat

The group_concat () function data used in MySQL is intercepted (with the default length limit). Exercise caution !, Mysqlgroup_concat

Recently encountered a problem at work:

Some of our system's logic processing is implemented by the storage process. However, one day our Customer Service reported that the order was not placed, and we checked the basic product resources involved in the order.

There are two codes in the stored procedure of 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 the stored procedure is executed, Variable _ err will return 1. You can only debug the stored procedure to find the problem. When debugging the following section, it is found that after the SQL statement is executed, _ err is changed to 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 the temporary table tmp_order_detail contains a large amount of data, I guess it is a problem with group_concat. So I reduced the data in the temporary table by half and found that it was successful. So I guess group_concat has a length limit, google, sure enough.

For the group_concat function:

Group_concat is okay when the data volume is small, but there is a problem when the data volume hits

Group_concat: the default connectable length is 1024. If the maximum length has been set, it will be truncated to this length;

When group_concat is used in a select statement, the limit statement becomes invalid;

Solution:

1,Modify the MySQL configuration file:

# Length 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 in the stored procedure to execute the stored procedure. OK, success!

 

Off duty (today is Sunday )!!!