最近工作中遇到一个业务,需要按表中的数据分类统计数量,一番研究后发现使用存储过程,动态行转列最为方便。于是便进行了一下常规操作:
这里直接列出我当前业务下的存储过程数据,写法都大同小异,虽然业务逻辑是动态行转列,但动态sql基本都这么写,文章着重记录此次异常的发现与原因,便不细说sql语句的具体写法:
SET @sql = NULL;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT( "sum( case BKJB when '", BKJB ,"' then 1 else 0 end) '", ss,"'" )
) INTO @sql FROM `XXXX_table`;
SET @sql = CONCAT('SELECT BKJB,BKZY,', @sql,
'FROM `XXXX_table` GROUP BY BKJB,BKZY');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
其中最为重要的一步就是使用GROUP_CONCAT函数配合distinct与contact来营造我们需要查询与转换的行sql:
GROUP_CONCAT(
DISTINCT CONCAT( "sum( case BKJB when '", BKJB ,"' then 1 else 0 end) '", ss,"'" )
)
这个sql真是在这个执行过程中至关重要。
下面我贴一下我的这个执行结果的截图:
执行完毕后我对照第一组结果看了下,没问题,一切都这么完美,但是当我带入到整体的存储过程中去执行时,诡异的一幕发生了:
报错:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
看到这个人都麻了,这种报错就是纯纯的格式不正确,出现在不是很复杂的sql中最折磨人了。于是我将整个sql从前到后的每个标点每个空格都研究过了,这个恐怖的问题依然存在。所以,我便将问题可能点集中到了@sql这里面,毕竟这里面是跑的执行过的结果,不能一眼就看到。将上面运行的 GROUP_CONCAT结果,打开来一看,倒吸一口凉气,我贴出来全部的在下面:
"sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '111',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '112',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '113',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '114',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '115',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '121',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '122',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '123',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '131',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '132',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '133',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '134',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '135',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '136',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '137',sum( case BKJB,BKZY when '100','101' then 1 else 0 end) '141',sum( case BKJB,BKZY when '100','"
原来根本结果就不对,没有显示全!
这时经过检查才知道,group_concat()函数聚合后的值有长度限制,默认为1024!而我这表中有几百万的数据,查出来远远大于这个长度,这样烂尾的结果带入sql自然引起报错。
查看当前库默认group_concat结果长度:
show variables like 'group_concat_max_len'
临时设置该字段长度,重启库后失效:
SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400;
果然是恐怖的报错,原因竟是在于字段的默认值,花了半天时间得以解决的问题,随手记录下,有需要的朋友可以参考下。