项目场景:
相关背景:
使用SQL函数出现:Row 13809 was cut by GROUP_CONCAT()的情况,
问题描述
问题:
例如:使用group_concat函数拼接字段时,发生截断的情况,导致导入数据出现问题:
SELECT
ID_CRD_NO,
GROUP_CONCAT(DISTINCT DIAG_CD) AS DIAG_CODE,
GROUP_CONCAT(DISTINCT DIAG_DSCPT) AS DIAG_NAME
FROM dwd.MIX_DIAG
WHERE SUBSTRING(DIAG_CD,1,3) IN ('E10','E11','E12','E13','E14')
AND LENGTH(TRIM(id_crd_no)) = 18
GROUP BY ID_CRD_NO
. Cause: java.sql.SQLException: Row 13805 was cut by GROUP_CONCAT()
2023-02-28 09:51:45 [com.groupds.cdr.xwave.core.thread.JobThread#run]-[201]-[Thread-45596]
原因分析:
分析:通过报错日志可以看到是使用GROUP_CONCAT函数报错,查找原因发现是拼接的字符串过长导致无法返回结果。通过查找资料是由于group_concat_max_len参数设置导致的。
因为默认GROUP_CONCAT函数返回的结果大小被MySQL默认限制为1024(字节)的长度。
解决方案:
解决方案一:执行sql语句,查看函数存放最大长度:
show variables like “group_concat_max_len”;
设置函数存放最大长度:
SET GLOBAL group_concat_max_len=2048;
SET SESSION group_concat_max_len=2048;
解决办法2:
查询的时候将group_concat字段长的人员信息剔除,再union all 上。
SELECT
ID_CRD_NO,
CASE WHEN INSTR(DIAG_NAME,'xxx') > 0 THEN 1
ELSE 0
END AS DIABETES_COMPLICATION,
CASE WHEN INSTR(DIAG_NAME,'xxx') > 0 THEN 0
ELSE 1
END AS DIABETES_NOT_COMPLICATION
FROM
(
SELECT
ID_CRD_NO,
GROUP_CONCAT(DISTINCT DIAG_CD) AS DIAG_CODE,
GROUP_CONCAT(DISTINCT DIAG_DSCPT) AS DIAG_NAME
FROM dwd.MIX_DIAG
WHERE SUBSTRING(DIAG_CD,1,3) IN ('xxxx')
AND LENGTH(TRIM(id_crd_no)) = 18
-- 字段过长数据,手动更新
AND ID_CRD_NO not in ('xxxxx')
GROUP BY ID_CRD_NO
) t
union all
SELECT
distinct ID_CRD_NO,
1 AS DIABETES_COMPLICATION,
0 AS DIABETES_NOT_COMPLICATION
FROM dwd.MIX_DIAG
WHERE LENGTH(TRIM(id_crd_no)) = 18
-- 字段过长数据,手动更新
AND ID_CRD_NO in ('xxxxxxx')