MariaDB 5.5执行COUNT/GROUP BY查询报1034重复键错误如何解决

根因说明 #

该问题是MariaDB 5.5.x版本的已知遗留bug：InnoDB引擎执行带GROUP BY的查询时，默认会创建内部临时索引用于分组排序，当待分组的字符串列存在不可见控制字符、或触发utf8mb4_unicode_ci校对规则的边界比较异常时，内部索引构建过程会错误抛出1034类的索引冲突报错，而非正常执行分组逻辑。该问题在MariaDB 10.0及以上版本已修复，无法升级的情况下可通过以下规避方案解决：

可行解决方案 #

方案1：禁用GROUP BY默认排序（优先尝试，改造成本最低） #

MariaDB 5.5中GROUP BY默认会附带排序逻辑，添加ORDER BY NULL可跳过排序，改用哈希分组逻辑，不会触发内部临时索引创建，绝大多数情况可直接解决问题。
修改后SQL示例：

SELECT COUNT(*) as numb, `AddressLine1` FROM <table> GROUP BY `AddressLine1` ORDER BY NULL;

方案2：强制按字节比较分组 #

若方案1无效，可对分组列添加BINARY修饰，强制按字节维度比较值，规避校对规则的比较异常，同时也会跳过有问题的内部索引逻辑。
修改后SQL示例：

SELECT COUNT(*) as numb, `AddressLine1` FROM <table> GROUP BY BINARY `AddressLine1`;

方案3：手动使用MyISAM临时表中转 #

若上述SQL改法都不满足业务需求，可通过临时表中转统计，MyISAM引擎的临时表不会触发该InnoDB专属bug：

-- 创建临时表存储待分组列
CREATE TEMPORARY TABLE tmp_addr (addr VARCHAR(180)) ENGINE=MyISAM;
-- 导入全量地址数据
INSERT INTO tmp_addr SELECT `AddressLine1` FROM <table>;
-- 执行分组统计
SELECT COUNT(*) as numb, addr FROM tmp_addr GROUP BY addr;
-- 用完可主动删除临时表，会话结束也会自动清理
DROP TEMPORARY TABLE IF EXISTS tmp_addr;

方案4：清理异常值 #

如果问题固定出现在id为15485、16044的两行，可先查询两行值的十六进制确认是否存在不可见字符：

SELECT id, HEX(`AddressLine1`) FROM <table> WHERE id IN (15485,16044);

若返回的十六进制值中包含00、200B（0宽空格）、0A（换行）等肉眼不可见的控制字符，直接将这两行的AddressLine1值删除后手动重新输入即可。

内容的提问来源于stack exchange，提问作者Mm-Art-In