MySQL/MariaDB中如何正确比较长字符串以匹配geometry字段重复值

MariaDB对TEXT、BLOB类型字段执行GROUP BY、ORDER BY操作时，默认仅比对字段的前max_sort_length字节内容，该参数默认值为1024字节。你使用的geometry字段最长可达20000字符，远超过默认阈值，因此仅比对了前缀内容，导致分组匹配结果错误。

方案1：调整会话级比对长度参数 #

无需重启数据库，仅在当前会话生效，执行以下语句后再运行原查询即可：

-- 调整长字符串比对阈值为32KB，覆盖你字段的最大长度20000字符
SET SESSION max_sort_length = 32768;
-- 按需调整group_concat最大长度，避免VNS拼接结果被截断
SET SESSION group_concat_max_len = 102400;

-- 原查询语句
SELECT group_concat(VNS),
       `geometry`,
       count(*) AS count 
FROM geo_table 
GROUP BY geometry 
HAVING count > 1 
ORDER BY count DESC;

方案2：基于哈希值分组（效率更高） #

对长字符串计算哈希值后按哈希分组，既可以避免比对长度限制，查询效率也远高于直接比对长字符串，万级数据量下无需担心哈希碰撞问题：

SELECT GROUP_CONCAT(VNS),
       ANY_VALUE(`geometry`) AS geometry,
       COUNT(*) AS count 
FROM geo_table 
GROUP BY MD5(`geometry`)
HAVING count > 1 
ORDER BY count DESC;

注：如果数据库开启了ONLY_FULL_GROUP_BY模式，使用ANY_VALUE可以避免非分组字段的查询报错，同一哈希分组下的geometry值完全一致，取任意值即可。

内容的提问来源于stack exchange，提问作者Pavlo N