Search code examples
mysqlsqlselectconcatenationgroup-concat

How to use mysql group_concat(concat()) without concat cuts

I'm curious why mySQL with group_concat(concat()) doesn't show the full length of result as it should. For the fewer set of result it is okey - the result perfectly shown. But when another set let's say when the result is more than 12.

MySQL code:

SELECT province_id AS prov_id, province_name AS prov_name, count( tent_info.tid ) AS ntent, 
GROUP_CONCAT( concat(tent_info.tuser, '-', tent_info.tname )
ORDER BY tent_info.tname ASC
SEPARATOR ',' ) AS tlist
FROM tbl_province
INNER JOIN tent_info ON tbl_province.province_id = tent_info.prov
WHERE tbl_province.geo_id = '6'
GROUP BY province_id
ORDER BY province_name ASC

MySQL result:

enter image description here Now the problem is what I said. The result doesn't completely loaded up. I assumed that when the result is more than 12. As the picture shown below.

enter image description here

Well I think it might because of :

  1. maximum length allow by group_concat or
  2. misuse of concat() or
  3. some error in the tlist occurred such as empty text or invalid characters

Please suggest.

Solution

  • Try to update this variable length to more until you not found full result. You can set variable globally & session wise.

    SET SESSION group_concat_max_len = '20000';