CODEWITHSUNDEEP
mysqlsqlconcatenationgroup-concat
user3408779
user3408779

Reputation: 997

Is there any maximum limit with mysql Group concat?

Below is my query to get the records from table with group concat. 

SELECT GROUP_CONCAT(CONCAT('''', order_number, '''' )) as order_number FROM edi_order_response where order_status = 'DELIVERED_INTIME' AND  DATE(`arul_timestamp`) BETWEEN '2018-08-21' AND '2018-08-22'

From the above query I am getting 93 records and one with unterminated record with single quote like below 

'78E4PLMF','7TQ223UD','8FKC5B2F',......,'89KB6MRR','

But when I see in related table with the above date range there are nearly 330 records. So is there any way to get all the records based on the given date range? Any help would be greatly appreciated.

Upvotes: 0

Views: 891

Answers (2)

amku91
amku91

Reputation: 1038

By Default it controlled by group_contact_max_len and having max length of 1024 characters

Well you can change this to get more data like:

SET @@group_concat_max_len = value_numeric;

value_numeric must be > 1024; by default the group_concat_max_len value is 1024.

Change in your mysql .ini file and restart your server.

If you don't have acess to server then you can achieve this by setting temporary session scope like

SET SESSION group_concat_max_len = 1000000;
select group_concat(column) from table group by column

But it's gonna only work in this session only. For different session you have to set it again.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Yes. It is controlled by the setting group_concat_max_len and the default is 1024.

You can read about it in the documentation.

You can also set the value to a higher value:

SET [GLOBAL | SESSION] group_concat_max_len = <some larger value>

Upvotes: 1

Related Questions