MySQL/MariaDB不同排序规则列比较的兼容性与性能问题

1. utf8与utf8mb4列在WHERE子句中比较是否会报错？ #

• 不会直接报错，但会触发隐式字符集转换：MySQL会把utf8（实际为utf8mb3）列转换为utf8mb4类型再执行比较。这种转换可能导致该列的索引失效，引发全表扫描，拖慢查询性能。

2. utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_unicode_520_ci列比较是否有显著性能问题？关联表时仅关联列排序规则一致是否受影响？ #

• 性能差异：
  • utf8mb4_general_ci排序规则实现简单，比较速度最快，但对特殊字符（如重音、大小写）的处理准确性最低；
  • utf8mb4_unicode_ci遵循Unicode标准排序规则，处理特殊字符更准确，性能略低于utf8mb4_general_ci；
  • utf8mb4_unicode_520_ci基于Unicode 5.20版本，准确性进一步提升，性能和utf8mb4_unicode_ci接近。
    数据量不大时三者性能差异可忽略；百万级以上数据量场景下，utf8mb4_general_ci的优势才会显现。
• 关联表影响：只要关联列的排序规则完全一致，即使字符集不同（如utf8mb3和utf8mb4，二者为兼容子集关系），关联操作不会出现性能问题或报错；若排序规则不一致，会触发隐式转换，可能导致索引失效，关联效率下降。

3. 默认情况下utf8mb4_unicode_ci与utf8mb4_general_ci列比较是否需指定排序规则？ #

• 默认不需要手动指定，MySQL会根据**排序规则优先级（coercibility）**自动选择主导排序规则进行比较。但这种隐式选择可能导致比较结果不符合预期，且会触发隐式转换，存在索引失效风险。建议要么统一表/列的排序规则，要么在查询中显式指定COLLATE子句（如WHERE col1 = col2 COLLATE utf8mb4_unicode_ci），避免意外问题。

4. 为何utf8mb4_general_ci与utf8mb3_unicode_ci列可正常比较？ #

• 确实和**排序规则的可压缩性（coercibility）**以及字符集兼容性有关：
  • utf8mb3是utf8mb4的子集，MySQL可安全将utf8mb3字符转换为utf8mb4，不会丢失数据；
  • 排序规则的coercibility规则会决定转换方向：列的coercibility低于常量，当两个不同排序规则的列比较时，MySQL会选择coercibility更低的排序规则作为主导，将另一方转换为该规则对应的字符集和排序规则，只要字符集兼容（子集关系），转换就能正常完成，不会报错。

5. 隐式/显式转换是否存在不触发索引失效的场景？ #

• 存在以下几种场景：
  • 转换发生在常量值而非列上：比如WHERE col_utf8mb4 = 'abc' COLLATE utf8mb3_general_ci，此时是转换常量为列的字符集/排序规则，列的索引仍可被使用；
  • 列的字符集是目标字符集的子集，且为无损转换：比如将utf8mb3列转换为utf8mb4，这种转换不会改变数据的字节表示，MySQL可利用原索引（但实际场景中仍建议统一字符集，避免依赖这种特殊情况）；
  • 显式转换后的表达式与索引匹配：比如WHERE CAST(col_utf8mb3 AS CHAR CHARACTER SET utf8mb4) = col_utf8mb4，但这种情况需要确保转换后的表达式能命中索引，可靠性远不如直接统一字符集。

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