utf8_general_ci和utf8_unicode_ci有什么区别？

Question

utf8_general_ci和utf8_unicode_ci有什么区别？

mysqlunicodeutf-8collationcharacter-set

1308

utf8_general_ci和utf8_unicode_ci之间，在性能方面是否有任何区别？

- KahWee Teng

3

请参见https://dev59.com/t3NA5IYBdhLWcg3wQ7Uw。 - unor

9

如果你喜欢 utf8[mb4]_unicode_ci，那么你可能更喜欢 utf8[mb4]_unicode_520_ci。 - Rick James

11

8.0版本默认采用utf8mb4_0900_ai_ci是更好的选择。 - Rick James

8.0显著加快了utf8比较的速度。(可能是utf8/utf8mb4的所有排序规则) - Rick James

2

utf8_unicode_ci 对于日语存在问题。https://bugs.mysql.com/bug.php?id=79977 将“美容院”和“病院”视为同一单词。选项520同样如此。 - Gazzer

9个回答

207

我想知道在使用utf8_general_ci和utf8_unicode_ci时性能上的区别，但是我没有在互联网上找到任何列出基准的数据，所以我决定自己创建基准测试。

我创建了一个非常简单的表格，里面包含500,000行：

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

然后我通过运行这个存储过程，填充了随机数据：

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

接下来，我创建了以下存储过程来测试简单的SELECT、带有LIKE的SELECT以及排序（ORDER BY的SELECT）：

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;

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 校对规则，在测试期间，我当然同时使用了 utf8_general_ci 和 utf8_unicode_ci 两种规则。

我为每个校对规则的每个存储过程调用了5次（utf8_general_ci 分别调用5次，utf8_unicode_ci 分别调用5次），然后计算了平均值。

我的结果如下： benchmark_simple_select() - 使用 utf8_general_ci 的时间为：9,957 毫秒 - 使用 utf8_unicode_ci 的时间为：10,271 毫秒

在这个基准测试中，使用 utf8_unicode_ci 比使用 utf8_general_ci 慢了3.2%。 benchmark_select_like() - 使用 utf8_general_ci 的时间为：11,441 毫秒 - 使用 utf8_unicode_ci 的时间为：12,811 毫秒

在这个基准测试中，使用 utf8_unicode_ci 比使用 utf8_general_ci 慢了12%。 benchmark_order_by() - 使用 utf8_general_ci 的时间为：11,944 毫秒 - 使用 utf8_unicode_ci 的时间为：12,887 毫秒

在这个基准测试中，使用 utf8_unicode_ci 比使用 utf8_general_ci 慢了7.9%。

- nightcoder

22

好的基准测试结果，谢谢分享。我在使用Windows上的MySQL v5.6.12时得到了类似的数字：分别为10％、4％和8％。我同意：使用utf8_general_ci带来的性能提升实在是太小了，不值得使用。 - RandomSeed

12

1）但是按照定义，这个基准测试不应该为两种排序生成类似的结果吗？我的意思是CONV(FLOOR(RAND() * 99999999999999), 20, 36)只会生成ASCII字符，没有Unicode字符需要由排序算法处理。 2）Description = 'test' COLLATE ...和Description LIKE 'test%' COLLATE...在运行时只处理单个字符串("test")，对吗？ 3）在实际应用中，用于排序的列可能已被索引，并且不同排序规则在处理实际的非ASCII文本时的索引速度可能会有所不同。 - Halil Özgür

2

@HalilÖzgür - 你的观点部分是错误的。我猜这不是关于编码值是否在ASCII范围之外（general_ci会正确处理），而是关于特定特性，比如将写成“Umleaute”的umlauts进行处理或其他一些细微之处。 - Tomasz Gandor

2

因此，尽管这些性能提升看起来很有吸引力，但我想知道这是否适用于真实世界的数据。您正在使用随机字符填充这些字段，但在现实世界中，数据具有更多的结构，并且结构与排序相关。我的大多数数据库中有绝大多数字符都是基本拉丁编码，只有少量其他字符经常出现在某个字段中。在这种情况下，不清楚是否会有任何性能提升。会有吗？我很想在一些真实数据上运行这个程序。 - cazort

58

这篇帖子描述得非常清楚。

简而言之：utf8_unicode_ci使用Unicode标准中定义的Unicode排序算法，而utf8_general_ci则是一种更简单的排序顺序，其结果为“不够准确”的排序结果。

- Michael Madsen

18

如果你不在意正确性，那么让任何算法变得无限快是微不足道的。只需使用“utf8_unicode_ci”，假装另一个不存在即可。 - tchrist

3

如果你在乎正确性和速度之间的平衡，那么utf8_general_ci可能适合你。 - Shelvacu

5

@tchrist永远不要成为游戏程序员 ;) - Stijn de Witt

1

@onassar - MySQL 8.0声称已经显著提高了所有排序规则的性能。 - Rick James

14

请参阅mysql手册中的Unicode字符集部分：Unicode Character Sets。对于任何Unicode字符集，使用_general_ci排序规则执行的操作比使用_unicode_ci排序规则执行的操作更快。例如，使用utf8_general_ci排序规则进行比较更快，但是比使用utf8_unicode_ci排序规则进行比较略微不正确。这是因为utf8_unicode_ci支持映射，例如扩展; 即，当一个字符与其他字符的组合相等时。例如，在德语和其他一些语言中，“ß”等于“ss”。utf8_unicode_ci还支持缩写和可忽略字符。utf8_general_ci是一种不支持扩展、缩写或可忽略字符的传统排序规则。它只能在字符之间进行一对一比较。因此，总的来说，utf_general_ci使用比utf_unicode_ci更小且不太正确（根据标准）的比较集合，general_ci集合将更快，因为要计算的内容更少。

- Dana the Sane

20

“稍微不正确”这种说法是不存在的。正确性是一种布尔特征；它不允许使用程度修饰语。只需使用 utf8_unicode_ci，假装有缺陷的版本不存在即可。 - tchrist

2

我在使用5.6.15版本时遇到了问题，无法设置collation_connection参数。后来发现需要在SET语句中传递' SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'参数。感谢Mathias Bynens提供的解决方案，这是他非常有用的指南：http://mathiasbynens.be/notes/mysql-utf8mb4 - Steve Hibbert

6

@tchrist说正确性是布尔值的问题在于它没有考虑到不依赖于绝对正确性的情况。你的基本观点并不无效，我也不打算赞扬general_ci的好处，但是你关于正确性的一般陈述很容易被证明是错误的。在我的职业中，我每天都这样做。开玩笑的话，Stuart提出了一个很好的观点此处链接。 - Anthony

6

在地理定位或游戏开发中，我们经常在正确性和性能之间做出权衡。当然，正确性是一个实数，在0到1之间，而不是布尔值。例如，在边界框中选择地理位置点是“附近点”的近似，这并不像计算点与参考点之间的距离并根据此进行过滤那样好。但是，两者都是一种近似，事实上，完全正确往往是无法实现的。请参阅海岸线悖论和IEEE 754。 - Stijn de Witt

4

请提供一个程序，打印出1/3的正确结果。 - Stijn de Witt

就“稍微不正确”这种事情而言，我必须表示不同意。如果我手里拿着一个桃子，有人问我我在拿什么，我可以回答“一个桃子”，这是正确的。我也可以回答“一块水果”，这也是正确的，但略微不正确。我可以回答“食物”，这也是正确的，但稍微不那么正确。看到过度苛求会让人讨厌吗？我是这样认为的。 - Jeffrey Tackett

12

一些细节（PL）

正如我们可以在这里读到的Peter Gulutzan所说，排序/比较波兰字母“Ł”（带划线的L- HTML转义：Ł）（小写字母：“ł”-HTML转义码： ł）有所不同 - 我们有以下假设：

utf8_polish_ci      Ł greater than L and less than M
utf8_unicode_ci     Ł greater than L and less than M
utf8_unicode_520_ci Ł equal to L
utf8_general_ci     Ł greater than Z

在波兰语中，字母Ł在字母L之后，在字母M之前。这两种编码方式都没有优劣之分，具体取决于您的需求。

- Kamil Kiełczewski

很好的观察。非常感谢！ - konieckropka

12

在排序和字符匹配方面有两个重大区别：

排序：

utf8mb4_general_ci 移除所有的重音符号并一个一个地排序，可能会导致排序结果不正确。
utf8mb4_unicode_ci 排序准确无误。

字符匹配

它们以不同的方式匹配字符。

例如，在 utf8mb4_unicode_ci 中，你有 i != ı，但在 utf8mb4_general_ci 中它成立 ı=i。

举例来说，假设你有一行数据是 name="Yılmaz"，那么

select id from users where name='Yilmaz';

如果单元格的排序规则为utf8mb4_general_ci，则会返回该行，但如果与utf8mb4_unicode_ci排序规则一起排序，则不会返回该行！

另一方面，在utf8mb4_unicode_ci中，a=ª和ß=ss，而在utf8mb4_general_ci中则不是这样。因此，想象一下你有一行数据：name="ªßi"，那么

select id from users where name='assi';

如果协同排序规则设置为utf8mb4_unicode_ci，则会返回该行，但如果协同排序规则设置为utf8mb4_general_ci，则不会返回该行。

每个协同排序规则的完整匹配列表可以在此处找到。

- Adam

11

简言之：

如果您需要更好的排序顺序，请使用utf8_unicode_ci（这是首选方法），

但如果您非常关注性能，请使用utf8_general_ci，但要知道它有点过时。

从性能方面来看，差异非常微小。

- simhumileco

2

两者现在都已过时 - 请参阅被接受的答案以获取更多信息。 - thomasrutter

1

上述评论表明，没有理由使用``utf8_general*``。然而对于日语来说，这可能不是真的。

使用MariaDB时，``utf8mb4_ja_0900_as_cs``不可用，因此必须使用其中一个unicode或general选项。然而，``unicode``将浊音和清音等视为相同。例如``びよういん``（美容师）被视为等同于``びょういん``（医院）。这显然是不正确的行为。

> select strcmp('が', 'か' collate utf8mb4_unicode_ci); #0
> strcmp('びよういん', 'びょういん' collate utf8mb4_unicode_520_ci); #0

相比之下，一般的操作提供

> select strcmp('が', 'か' collate utf8mb4_general_ci); #1

换句话说，Unicode将浊音和清音假名视为相同。在我看来，这并不理想。

编辑：使用uca1400_ai_cs可能更好，该选项在较新版本的MariaDB中可用，并且可以正确获取上述排序规则。

- Gazzer

1

根据这篇文章，使用utf8mb4_general_ci而不是utf8mb4_unicode_ci在MySQL 5.7上有相当大的性能优势： https://www.percona.com/blog/2019/02/27/charset-and-collation-settings-impact-on-mysql-performance/

- DavidH

需要注意的是，相关分析表明MySQL 8.0并没有任何显著的优势。因此，对于这个问题的答案似乎高度依赖于版本。 - cazort

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- thomasrutter · Accepted Answer

对于在2020年或之后找到此问题的人，有比这两个更好的新选项，例如 utf8_unicode_520_ci 。

所有这些排序规则都适用于UTF-8字符编码。差异在于如何对文本进行排序和比较。

_unicode_ci和_general_ci是两种不同的规则集，用于根据我们的期望对文本进行排序和比较。MySQL的新版本也引入了新的规则集，例如基于Unicode 5.2的_unicode_520_ci或基于Unicode 9.0的MySQL 8.x特定_0900_ai_ci（没有相应的_general_ci变体）等等。现在阅读此内容的人应该使用其中一种较新的排序规则，而不是_unicode_ci或_general_ci。以下介绍旧排序规则仅供参考。

MySQL目前正在逐步摆脱旧版有缺陷的UTF-8实现。但是，为了向后兼容，仍需要使用utf8mb4代替utf8作为字符编码部分，以确保获得修复过的版本。

关键区别

utf8mb4_unicode_ci基于官方的Unicode规则进行通用排序和比较，可在广泛的语言中精确排序。
utf8mb4_general_ci是一组简化的排序规则，旨在尽可能做得好，同时采取许多旨在提高速度的捷径。它不遵循Unicode规则，并且在某些情况下会导致不良的排序或比较，例如使用特定语言或字符时。

在现代服务器上，这种性能提升几乎可以忽略不计。它是在服务器的CPU性能只有今天电脑的一小部分的时候设计的。

utf8mb4_unicode_ci相对于utf8mb4_general_ci的优点

utf8mb4_unicode_ci使用Unicode的排序和比较规则，在广泛的语言和使用广泛的特殊字符时采用相当复杂的算法以实现正确的排序。这些规则需要考虑特定于语言的惯例；并非所有人都按照我们所说的“字母顺序”对其字符进行排序。

就拉丁（即“欧洲”）语言而言，在MySQL中，Unicode排序和简化的utf8mb4_general_ci排序之间没有太大的区别，但仍然存在一些差异:

例如，Unicode排序将“ß”排序为“ss”，将“Œ”排序为“OE”，这符合使用这些字符的人的预期。而utf8mb4_general_ci排序将它们视为单个字符（大概相当于“s”和“e”）。
一些Unicode字符被定义为可忽略的，这意味着它们不应计入排序顺序，比较应该转移到下一个字符。 utf8mb4_unicode_ci可以正确处理这些字符。

在非拉丁语言中，如亚洲语言或使用不同字母表的语言中，Unicode排序和简化的utf8mb4_general_ci排序之间可能存在更多差异。对于某些语言来说，utf8mb4_general_ci已经远远不足够。

应该使用什么？

现在几乎没有理由再使用utf8mb4_general_ci了，因为我们已经超越了CPU速度低到性能差异重要的点。你的数据库几乎肯定会受到其他瓶颈的限制，而不是这个。

过去，有些人建议只有在精确排序足以证明性能成本时才使用utf8mb4_general_ci。如今，这种性能成本几乎已经消失了，开发人员更加重视国际化。

有一种观点认为，如果速度对您更重要，而不是准确性，那么您可能干脆不排序。如果您不需要精度，则可以轻松使算法更快。因此，utf8mb4_general_ci是一种折衷方案，可能不需要出于速度原因，并且可能也不适合出于准确性原因。

我想补充的另一件事是，即使您知道您的应用程序只支持英语，它仍然可能需要处理人们的姓名，这些姓名通常包含在其他语言中使用的字符，而正确排序同样重要。对于所有内容使用Unicode规则可以增加安心感，因为非常聪明的Unicode专家们努力使排序正常工作。

各部分含义

首先，ci 是指不区分大小写的排序和比较。这意味着它适用于文本数据，大小写不重要。其他类型的排序规则是cs（区分大小写）适用于大小写重要的文本数据，以及bin，适用于需要匹配编码位的二进制数据字段（包括例如Base64）。大小写敏感的排序会导致一些奇怪的结果，而大小写敏感的比较可能导致仅有字母大小写不同的重复值，因此对于文本数据，大小写敏感的排序规则正在被淘汰 - 如果大小写对您很重要，则无关紧要的标点符号等也可能很重要，二进制排序规则可能更合适。

其次，unicode或general是特定的排序和比较规则 - 特别是文本如何标准化或比较。对于utf8mb4字符编码，有许多不同的规则集，其中unicode和general是两个试图在所有可能的语言中工作得很好而不是特定语言的规则集。这两个规则集之间的差异是本答案的主题。请注意，unicode使用Unicode 4.0的规则。MySQL和MariaDB的最新版本添加了规则集unicode_520，使用Unicode 5.2的规则，而MySQL 8.x添加了0900（删除“unicode_”部分），使用Unicode 9.0的规则。

最后，utf8mb4当然是内部使用的字符编码。在本答案中，我只讨论基于Unicode的编码。