mysql中utf8_general_ci和utf8_unicode_ci的区别

技术背景

在 MySQL 数据库中，字符集和排序规则是处理文本数据的重要概念。utf8_general_ci 和 utf8_unicode_ci 是两种常见的 UTF - 8 字符集的排序规则。排序规则决定了如何对文本进行排序和比较，不同的排序规则会影响到数据的存储、查询和显示。了解它们之间的区别，有助于开发者根据具体需求选择合适的排序规则，以优化数据库性能和保证数据处理的准确性。

实现步骤

1. 创建测试表

CREATE TABLE test(
  ID INT(11) DEFAULT NULL,
  Description VARCHAR(20) DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

2. 填充随机数据

CREATE PROCEDURE randomizer()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE random CHAR(20) ;
  theloop: loop
    SET random = CONV(FLOOR(RAND() * 99999999999999), 20, 36);
    INSERT INTO test VALUES (i+1, random);
    SET i=i+1;
    IF i = 500000 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END

3. 创建基准测试存储过程

简单查询基准测试

CREATE PROCEDURE benchmark_simple_select()
BEGIN
  DECLARE i INT DEFAULT 0;
  theloop: loop
    SELECT *
    FROM test
    WHERE Description = 'test' COLLATE utf8_general_ci;
    SET i = i + 1;
    IF i = 30 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END;

LIKE 查询基准测试

CREATE PROCEDURE benchmark_select_like()
BEGIN
  DECLARE i INT DEFAULT 0;
  theloop: loop
    SELECT *
    FROM test
    WHERE Description LIKE '%test' COLLATE utf8_general_ci;
    SET i = i + 1;
    IF i = 30 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END;

排序查询基准测试

CREATE PROCEDURE benchmark_order_by()
BEGIN
  DECLARE i INT DEFAULT 0;
  theloop: loop
    SELECT *
    FROM test
    WHERE ID > FLOOR(1 + RAND() * (400000 - 1))
    ORDER BY Description COLLATE utf8_general_ci LIMIT 1000;
    SET i = i + 1;
    IF i = 10 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END;

核心代码

基准测试调用示例

-- 对于 utf8_general_ci 排序规则
CALL benchmark_simple_select();
CALL benchmark_select_like();
CALL benchmark_order_by();

-- 修改表的排序规则为 utf8_unicode_ci
ALTER TABLE test CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

-- 再次进行基准测试
CALL benchmark_simple_select();
CALL benchmark_select_like();
CALL benchmark_order_by();

最佳实践

选择排序规则的建议

• 准确性优先：如果应用程序需要处理多种语言，并且对排序和比较的准确性要求较高，建议使用 utf8mb4_unicode_ci。例如，处理人名、地名等包含特殊字符的文本时，该排序规则能保证更准确的排序结果。
• 性能优先：如果应用程序主要处理英语或其他简单文本，对排序准确性要求不高，且对性能有较高要求，可以考虑使用 utf8mb4_general_ci。但在现代服务器上，性能差异通常可以忽略不计。

注意事项

• 字符集使用：由于 MySQL 旧版的 UTF - 8 实现存在缺陷，建议使用 utf8mb4 字符集代替 utf8，以确保获得正确的字符编码处理。
• 国际业务考虑：即使应用程序目前只支持英语，也应考虑使用 utf8mb4_unicode_ci，因为可能会涉及到包含其他语言字符的人名等数据，使用 Unicode 规则能增加排序正确性的保障。

常见问题

性能差异问题

在一些基准测试中，utf8_unicode_ci 可能会比 utf8_general_ci 稍慢。例如，在简单查询中慢 3.2%，在 LIKE 查询中慢 12%，在排序查询中慢 7.9%。但在现代服务器上，这种性能差异通常可以忽略不计，数据库性能可能更多地受其他因素影响。

排序结果不准确问题

utf8_general_ci 是一种简化的排序规则，不遵循 Unicode 规则，在某些情况下可能会导致排序或比较结果不理想。例如，在处理波兰语字母 “Ł” 时，utf8_general_ci 会将其排在 “Z” 之后，而在波兰语中 “Ł” 应排在 “L” 之后、“M” 之前。

字符匹配问题

两种排序规则对字符的匹配方式不同。例如，在 utf8mb4_unicode_ci 中 i != ı，而在 utf8mb4_general_ci 中 ı = i；在 utf8mb4_unicode_ci 中 a = ª 且 ß = ss，但在 utf8mb4_general_ci 中并非如此。这可能会影响到查询结果，开发者需要根据具体需求选择合适的排序规则。

日语处理问题

utf8mb4_unicode_ci 在处理日语时存在问题，会将有声和无声假名视为相同。例如，会将 “びよういん”（美容院）和 “びょういん”（医院）视为相同的单词。在这种情况下，utf8mb4_general_ci 可能是更好的选择，或者使用 MariaDB 较新版本中可用的 uca1400_ai_cs 排序规则。