MySql group_concat踩坑
摘要
group_concat有长度限制,导致结果被截取,部分结果丢失
问题描述
需求:当学生入学的时候,如果auto_invoice_customer中不存在这个学生,则将学生信息插入auto_invoice_customer表
表:auto_invoice
字段名 | 类型 | 备注 |
---|---|---|
ID | int(11) | 主键 |
... |
表: auto_invoice_customer
字段名 | 类型 | 备注 |
---|---|---|
ID | int(11) | 主键 |
auto_invoice_id | int(11) | 表auto_invoice的ID |
student_id | int(11) | 学生ID |
... |
有问题的实现:
SELECT
ai.ID,
GROUP_CONCAT( aic.STUDENT_ID ) studentIds
FROM
auto_invoice ai
INNER JOIN auto_invoice_customer aic ON aic.AUTO_INVOICE_ID = ai.ID
WHERE
1 = 1
GROUP BY
ai.ID;
通过上面的SQL查询出所有已存在的学生列表,代码中判断:如果学生不存在,则插入到auto_invoice_customer表
问题: GROUP_CONCAT有长度限制,默认为1024个字符。当已经存在的学生太多时,studentIds长度超过1024时,会有一部分学生被截掉,或者最后一个学生截掉了一部分。 导致有部分学生没查到,认为不在auto_invoice_customer中,导致重复插入。
长度限制可以通过下面的SQL查询:
SHOW VARIABLES LIKE '%group_concat%';
改进
SELECT
ai.ID,
studentId
FROM
auto_invoice ai
INNER JOIN auto_invoice_customer aic ON aic.AUTO_INVOICE_ID = ai.ID
WHERE
1 = 1
通过上面的SQL查询出所有已存在的学生,代码中判断:如果学生不存在,则插入到auto_invoice_customer表