中文拼音声调不区分的SQL查询匹配异常及优化咨询

问题成因 #

核心原因是数据库字符集的排序规则忽略了拼音声调差异。
多数默认的MySQL字符集排序规则（比如utf8_general_ci、utf8mb4_general_ci）属于「不区分重音（accent-insensitive）」的规则，这类规则会把带不同声调的拼音字符（如ā、á、ǎ、à）视为完全等价的字符。当你用LIKE进行查询时，数据库会基于这套规则做匹配，自然会返回所有声调的变体。

另外，你的SQL里用了LIKE但未添加通配符，实际效果和=类似，但本质问题还是排序规则导致的字符等价判断逻辑。

优化方案 #

1. 修改字段排序规则（永久方案） #

把py字段的排序规则改成区分声调的类型，常用选项有：

• utf8mb4_bin：二进制排序规则，严格按照字符的Unicode编码值匹配，完全区分声调、大小写等差异。
• utf8mb4_uca1400_as_cs：基于Unicode 14.0的排序规则，明确区分重音（AS）和大小写（CS），语义上更贴合拼音声调的区分需求。

修改字段的SQL示例：

ALTER TABLE words_ch MODIFY COLUMN py VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_as_cs;

2. 查询时临时指定排序规则（无需改表） #

如果不能修改表结构，可以在查询语句中临时指定区分声调的排序规则，强制数据库严格匹配字符：

cursor.execute(''' SELECT * FROM words_ch WHERE py LIKE %s COLLATE utf8mb4_bin''', (str(word),))

如果是精确匹配需求，直接用=代替LIKE效率更高：

cursor.execute(''' SELECT * FROM words_ch WHERE py = %s COLLATE utf8mb4_bin''', (str(word),))

3. 新增无声调拼音字段（兼顾多场景需求） #

如果需要同时支持「带声调精确匹配」和「无声调模糊查询」，可以新增一个py_no_tone字段，存储去掉声调的拼音（如mao）：

• 精确匹配带声调的记录：用py字段配合区分声调的规则。
• 模糊匹配所有声调的记录：用py_no_tone字段查询。

内容的提问来源于stack exchange，提问作者Yaroslav Cherkaev