MySQL按单词长度排序后字母序异常问题求助

问题背景 #

• 维护的站点功能：根据输入字母展示所有匹配单词，数据库存储约17万个单词，最长15字符
• 异常现象：
  • 输入≤8字母时：结果按单词长度升序（2→8）展示，同长度单词严格遵循字母序
  • 输入≥9字母时：结果仍按长度升序，但同长度单词不再按字母序排列
• 历史修复：数年前曾出现相同问题，通过重新导入CSV文件解决，但此次无效
• 排除代码问题的依据：
  • 将故障站点代码指向法语站点数据库时，功能完全正常
  • 采用相同代码的法语站点运行无异常，故障站点为letterunscramble.com
• 技术栈：HTML + PHP + 少量JS

当前使用的查询语句 #

$result = mysql_query("SELECT * FROM word WHERE LENGTH(word) <= $wordlength order by CHAR_length(word) ASC; ") 
or die(mysql_error());

可能原因分析 #

• 数据库排序规则不匹配：英语站word字段的排序规则未设置为字典序（比如误用二进制排序utf8_bin，或字符集排序规则不兼容），而法语站使用了正确的排序规则（如utf8mb4_unicode_ci），导致长单词排序异常
• 查询缺少二级排序规则：原查询仅按单词长度排序，未明确指定同长度时按word字段排序，MySQL可能根据索引或存储顺序返回结果，而非字母序
• 索引影响排序逻辑：如果CHAR_LENGTH(word)字段存在索引，MySQL可能利用索引优化排序，跳过了按单词本身的字母序排序；短长度数据量小时MySQL选择全表排序，长长度时走索引导致顺序异常
• 数据导入异常：重新导入CSV时可能出现字符编码转换错误，导致长单词存储有不可见字符或编码问题，干扰排序

解决思路 #

1. 修改查询语句，强制指定二级排序
   补充word ASC作为二级排序条件，确保同长度单词按字母序排列：

   $result = mysql_query("SELECT * FROM word WHERE CHAR_LENGTH(word) <= $wordlength ORDER BY CHAR_LENGTH(word) ASC, word ASC; ") 
   or die(mysql_error());
   

   注：统一使用CHAR_LENGTH()替代LENGTH()，避免字节数与字符数差异导致的潜在问题

2. 检查并修正数据库字段排序规则

   • 执行SQL查看表结构：SHOW CREATE TABLE word;，确认word字段的排序规则
   • 若排序规则非字典序，修改为适合英语的排序规则：

     ALTER TABLE word MODIFY COLUMN word VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
     

3. 排查索引对排序的影响

   • 若存在CHAR_LENGTH(word)相关索引，尝试临时删除后测试排序是否正常，验证是否是索引导致的排序逻辑异常
   • 若需要保留索引，可在查询中添加FORCE INDEX()强制全表扫描排序（仅用于排查，长期建议调整索引或查询逻辑）

4. 重新验证数据导入流程

   • 确保CSV文件编码为UTF-8，导入时指定对应字符集，避免乱码
   • 抽样检查9+字符的单词，确认无不可见字符或编码错误

内容的提问来源于stack exchange，提问作者Mark Bajanen