mysql group_concat数据过长截断

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values

group_concat将非NULL的值拼接返回字符串,当没有非NULL的值时返回NULL.
有长度限制,遇到过一个由于长度限制数据截断导致的bug,sql如下

-- 这里id,price都是int,4字节
SELECT id,SUBSTRING_INDEX(GROUP_CONCAT(price order by id), ',', -1) AS price
FROM xxx
where xxxx=xxx
GROUP BY id;

这里将price拼接,再通过SUBSTRING_INDEX取最后一个price,由于数据截断,GROUP_CONCAT返回的字符串最后一个是”,”而不是price,导致SUBSTRING_INDEX取到的是””(字符串),在业务逻辑里直接转为int报错了,由此产生bug.
且只有当数据量足够多时才会触发(超过171行时,1024=1714+1702,引擎为InnnoDB,MySQL8.0,字符集为utf8mb4_0900_ai_ci)

-- 有兴趣的可以复现下,查一个int类型的数据就行
SELECT GROUP_CONCAT(price) FROM (
SELECT `price` 
FROM xxx
where `xxx` = "xxx" limit 171 ) as t;

语法

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

SELECT student_name,
        GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;

SELECT student_name,
        GROUP_CONCAT(DISTINCT test_score
                      ORDER BY test_score DESC SEPARATOR ' ')
       FROM student
       GROUP BY student_name;

限制

1. 数据长度,group_concat_max_len

   -- 查询长度显示,默认值为1024
   SELECT  @@group_concat_max_len;
   
   -- 设置
   SET [GLOBAL | SESSION] group_concat_max_len = val;

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

参考

• mysql文档 https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
• csdn http://t.csdn.cn/G8xdm