提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

mysql线上排错 group_concat_max_len group_concat函数 引起的查询数据丢失 实践笔记

mysql
2023-04-18 12:55:40 时间

mysql线上排错 group_concat_max_len group_concat函数 引起的查询数据丢失 实践笔记

0.问题场景

因为默认GROUP_CONCAT函数返回的结果大小被MySQL默认限制为1024（字节）的长度。

写这个是因为遇到接口返回数据跟数据库实际数据不符，相差较多，排查完发现的问题，这边记录下解决方案。

MySQL提供的group_concat函数可以拼接某个字段值成字符串，如 select group_concat(user_name) from sys_user,默认的分隔符是 逗号。

如：select group_concat(user_name SEPARATOR ‘_’) from sys_user; 但是如果 user_name 拼接的字符串的长度字节超过1024 则会被截断。

通过命令 “show variables like ‘group_concat_max_len’” 来查看group_concat 默认的长度：

show variables like 'group_concat_max_len';

1.写几个sql来验证。

我们可以先查出我们数据的最大长度，在用GROUP_CONCAT函数查询，对比数据长度差异，以及验证GROUP_CONCAT查出来的长度是不是1024

select user_name from sys_user;#查看user_name字段有多多少位，查看到的是6位，假设都是6位
select COUNT(user_name)*6 '个数*位数' from sys_user;#查看user_name字段有多少个乘以6位 会大于1024
select group_concat(user_name SEPARATOR '') from sys_user; #user_name字段拼接起来
select LENGTH(a.aa) as '字段拼接长度' from(select group_concat(user_name SEPARATOR '') as aa from sys_user ) a; #查看user_name字段拼接起来有总共有多长

可以看到只能查出1024位 远短于6972

2.这时就需要修改 group_concat_max_len 参数到需要的大小，比如102400，扩大一百倍。使得我们使用GROUP_CONCAT函数查询的时候可以正常返回。修改的方式有两种：

2.1方法一：(永久生效需要重启)在MySQL的配置文件中加入如下配置:

#先查询group_concat_max_len的长度
show variables like "group_concat_max_len";
# 在mysqld下加入
group_concat_max_len = 102400

重启生效

#再次查询group_concat_max_len的长度
show variables like "group_concat_max_len";

2.2.方法二：(临时使用，重启失效)更简单的操作方法，执行SQL语句：

#先查询group_concat_max_len的长度
show variables like "group_concat_max_len";
# 设置长度
SET GLOBAL group_concat_max_len = 102400;

SET SESSION group_concat_max_len = 102400;

长度更改为102400

3.我们再次用第1步的sql来验证

select LENGTH(a.aa) as '字段拼接长度' from(select group_concat(user_name SEPARATOR '') as aa from sys_user ) a; #查看user_name字段拼接起来有总共有多长

相关文章