RDS MySQL函数group_concat相关问题的处理

  • A+
所属分类:MySQL
高性能企业级服务器首台5折

1. Group_concat 返回结果的长度

函数 group_concat 返回结果的长度受参数 group_concat_max_len 控制,默认值是 1024,即默认返回1024字节长度结果。

# 参数名称 默认值 最小值 最大值 作用
1 group_concat_max_len 1024 4 1844674407370954752 group_concat 函数返回结果的最大长度,单位字节

注:该参数可以在控制台  参数设置中配置(全局生效),也可以在会话级别设置(当前会话生效)。


1
2
3
4
5
6
7
8
9
10
<span class="hljs-keyword">set</span> group_concat_max_len=<span class="hljs-number">90</span>; <span class="hljs-comment">-- 设置当前会话 group_concat_max_len 为 90 字节</span>

<span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'group_concat_max_len'</span>; <span class="hljs-comment">-- 查看当前会话的 group_concat_max_len 值</span>


<span class="hljs-keyword">select</span> <span class="hljs-keyword">group_concat</span>(<span class="hljs-keyword">distinct</span> <span class="hljs-keyword">concat_ws</span>(<span class="hljs-string">' '</span>, t1.col0, t1.col2, t1.col3, t1.col4) separator <span class="hljs-string">"---"</span>)
<span class="hljs-keyword">from</span> grp_con_test t1, grp_con_test t2 \G  <span class="hljs-comment">-- 查询结果</span>

<span class="hljs-keyword">select</span> <span class="hljs-keyword">length</span>(<span class="hljs-keyword">group_concat</span>(<span class="hljs-keyword">distinct</span> <span class="hljs-keyword">concat_ws</span>(<span class="hljs-string">' '</span>, t1.col0, t1.col2, t1.col3, t1.col4) separator <span class="hljs-string">"---"</span>))
<span class="hljs-keyword">from</span> grp_con_test t1, grp_con_test t2 \G  <span class="hljs-comment">-- 查询结果的长度</span>

group_concat_06.png

2. Group_concat(distinct) 去除重复数据失效的处理

2.1 失效原因

当设置 group_concat_max_len 为较大值时,会出现使用 group_concat 和 distinct 来去除结果中重复数据失效的情况,比如:


1
2
<span class="hljs-keyword">select</span> <span class="hljs-keyword">group_concat</span>(<span class="hljs-keyword">distinct</span> <span class="hljs-keyword">concat_ws</span>(<span class="hljs-string">' '</span>, t1.col0, t1.col2, t1.col3, t1.col4) separator <span class="hljs-string">"---"</span>)
<span class="hljs-keyword">from</span> grp_con_test t1, grp_con_test t2 \G <span class="hljs-comment">-- 查询结果</span>

group_concat_03.png

可以看到,结果中出现多个重复值

出现这个问题的原因是,当 group_concat 返回结果集比较大,则会出现内存临时表无法承载全部结果集数据,进而会使用磁盘临时表;而 group_concat 在使用磁盘临时表时会触发 bug 导致无法去除重复数据。


1
<span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'tmp_table_size'</span>; <span class="hljs-comment">-- 显示当前会话 tmp_table_size 参数设置</span>

group_concat_05.png

2.2 解决方法

调整 tmp_table_size 参数设置,增大内存临时表的最大尺寸。


1
2
3
4
5
6
<span class="hljs-keyword">set</span> tmp_table_size=<span class="hljs-number">1</span>*<span class="hljs-number">1024</span>*<span class="hljs-number">1024</span> <span class="hljs-comment">-- 设置当前会话 tmp_table_size 为 1 MB</span>

<span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'tmp_table_size'</span> <span class="hljs-comment">-- 查看当前会话 tmp_table_size 的设置</span>

<span class="hljs-keyword">select</span> <span class="hljs-keyword">group_concat</span>(<span class="hljs-keyword">distinct</span> <span class="hljs-keyword">concat_ws</span>(<span class="hljs-string">' '</span>, t1.col0, t1.col2, t1.col3, t1.col4) separator <span class="hljs-string">"---"</span>)
<span class="hljs-keyword">from</span> grp_con_test t1, grp_con_test t2 \G

group_concat_04.png

注:

  • 1. Bug 信息请参考:Bug 68145
  • 2. 参数 tmp_table_size 可以在控制台  参数设置中设置(全局生效,但对设置前已经连接的会话不生效),也可以在会话级别设置(当前会话生效)。

 

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: