問(wèn)題描述

utf8_general_ci 和 utf8_unicode_ci 在性能上有區(qū)別嗎?

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

推薦答案

對(duì)于那些在 2020 年或之后仍然遇到這個(gè)問(wèn)題的人，有更新的選項(xiàng)可能比兩者都更好這些.例如，utf8mb4_0900_ai_ci.

For those people still arriving at this question in 2020 or later, there are newer options that may be better than both of these. For example, utf8mb4_0900_ai_ci.

所有這些排序規(guī)則都是針對(duì) UTF-8 字符編碼的.不同之處在于文本的排序和比較方式.

All these collations are for the UTF-8 character encoding. The differences are in how text is sorted and compared.

_unicode_ci 和 _general_ci 是兩組不同的規(guī)則，用于按照我們期望的方式對(duì)文本進(jìn)行排序和比較.較新版本的 MySQL 也引入了新的規(guī)則集，例如 _0900_ai_ci 用于基于 Unicode 9.0 的等效規(guī)則 - 并且沒(méi)有等效的 _general_ci 變體.現(xiàn)在閱讀本文的人可能應(yīng)該使用這些較新的排序規(guī)則之一，而不是 _unicode_ci 或 _general_ci.以下對(duì)這些舊排序規(guī)則的描述僅供參考.

_unicode_ci and _general_ci are two different sets of rules for sorting and comparing text according to the way we expect. Newer versions of MySQL introduce new sets of rules, too, such as _0900_ai_ci for equivalent rules based on Unicode 9.0 - and with no equivalent _general_ci variant. People reading this now should probably use one of these newer collations instead of either _unicode_ci or _general_ci. The description of those older collations below is provided for interest only.

MySQL 目前正在擺脫舊的、有缺陷的 UTF-8 實(shí)現(xiàn).目前，您需要使用 utf8mb4 而不是 utf8 作為字符編碼部分，以確保您獲得固定版本.有缺陷的版本仍然是為了向后兼容，盡管它已被棄用.

MySQL is currently transitioning away from an older, flawed UTF-8 implementation. For now, you need to use utf8mb4 instead of utf8 for the character encoding part, to ensure you are getting the fixed version. The flawed version remains for backward compatibility, though it is being deprecated.

主要區(qū)別

• utf8mb4_unicode_ci 基于官方的 Unicode 規(guī)則進(jìn)行通用排序和比較，可在多種語(yǔ)言中準(zhǔn)確排序.

• utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ci 是一組簡(jiǎn)化的排序規(guī)則，旨在盡其所能，同時(shí)采取許多旨在提高速度的捷徑.它不遵循 Unicode 規(guī)則，在某些情況下會(huì)導(dǎo)致不受歡迎的排序或比較，例如在使用特定語(yǔ)言或字符時(shí).

utf8mb4_general_ci is a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations, such as when using particular languages or characters.

在現(xiàn)代服務(wù)器上，這種性能提升幾乎可以忽略不計(jì).它是在服務(wù)器的 CPU 性能僅達(dá)到當(dāng)今計(jì)算機(jī)的一小部分時(shí)設(shè)計(jì)的.

On modern servers, this performance boost will be all but negligible. It was devised in a time when servers had a tiny fraction of the CPU performance of today's computers.

utf8mb4_unicode_ci 優(yōu)于 utf8mb4_general_ci

Benefits of utf8mb4_unicode_ci over utf8mb4_general_ci

utf8mb4_unicode_ci 使用 Unicode 規(guī)則進(jìn)行排序和比較，它采用相當(dāng)復(fù)雜的算法來(lái)在多種語(yǔ)言和使用多種特殊字符時(shí)進(jìn)行正確排序.這些規(guī)則需要考慮特定語(yǔ)言的約定；不是每個(gè)人都按照我們所說(shuō)的字母順序"對(duì)他們的字符進(jìn)行排序.

utf8mb4_unicode_ci, which uses the Unicode rules for sorting and comparison, employs a fairly complex algorithm for correct sorting in a wide range of languages and when using a wide range of special characters. These rules need to take into account language-specific conventions; not everybody sorts their characters in what we would call 'alphabetical order'.

就拉丁語(yǔ)(即歐洲")語(yǔ)言而言，Unicode 排序與 MySQL 中簡(jiǎn)化的 utf8mb4_general_ci 排序沒(méi)有太大區(qū)別，但仍有一些區(qū)別:

As far as Latin (ie "European") languages go, there is not much difference between the Unicode sorting and the simplified utf8mb4_general_ci sorting in MySQL, but there are still a few differences:

• 例如，Unicode 排序規(guī)則對(duì)?"進(jìn)行排序；像ss"和?"像OE"正如人們通常想要的那樣，而 utf8mb4_general_ci 將它們作為單個(gè)字符進(jìn)行排序(大概分別像s"和e").

• For examples, the Unicode collation sorts "?" like "ss", and "?" like "OE" as people using those characters would normally want, whereas utf8mb4_general_ci sorts them as single characters (presumably like "s" and "e" respectively).

某些 Unicode 字符被定義為可忽略的，這意味著它們不應(yīng)計(jì)入排序順序，而應(yīng)將比較轉(zhuǎn)移到下一個(gè)字符.utf8mb4_unicode_ci 正確處理這些.

Some Unicode characters are defined as ignorable, which means they shouldn't count toward the sort order and the comparison should move on to the next character instead. utf8mb4_unicode_ci handles these properly.

在非拉丁語(yǔ)言中，例如亞洲語(yǔ)言或具有不同字母表的語(yǔ)言，Unicode 排序和簡(jiǎn)化的utf8mb4_general_ci 排序之間可能存在很多更多差異.utf8mb4_general_ci 的適用性在很大程度上取決于所使用的語(yǔ)言.對(duì)于某些語(yǔ)言，這將是相當(dāng)不夠的.

In non-latin languages, such as Asian languages or languages with different alphabets, there may be a lot more differences between Unicode sorting and the simplified utf8mb4_general_ci sorting. The suitability of utf8mb4_general_ci will depend heavily on the language used. For some languages, it'll be quite inadequate.

你應(yīng)該使用什么?

幾乎可以肯定沒(méi)有理由再使用 utf8mb4_general_ci，因?yàn)槲覀円呀?jīng)忘記了 CPU 速度足夠低以至于性能差異很重要的點(diǎn).您的數(shù)據(jù)庫(kù)幾乎肯定會(huì)受到除此之外的其他瓶頸的限制.

There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.

過(guò)去，有些人建議使用 utf8mb4_general_ci，除非準(zhǔn)確排序非常重要以證明性能成本是合理的.如今，這種性能成本幾乎消失了，開(kāi)發(fā)人員更加重視國(guó)際化.

In the past, some people recommended to use utf8mb4_general_ci except when accurate sorting was going to be important enough to justify the performance cost. Today, that performance cost has all but disappeared, and developers are treating internationalization more seriously.

有一種觀點(diǎn)認(rèn)為，如果速度對(duì)您來(lái)說(shuō)比準(zhǔn)確性更重要，那么您最好根本不進(jìn)行任何排序.如果您不需要算法準(zhǔn)確，那么使算法更快是微不足道的.因此，utf8mb4_general_ci 是一種折衷方案，出于速度原因可能不需要，也可能出于準(zhǔn)確性原因也不適合.

There's an argument to be made that if speed is more important to you than accuracy, you may as well not do any sorting at all. It's trivial to make an algorithm faster if you do not need it to be accurate. So, utf8mb4_general_ci is a compromise that's probably not needed for speed reasons and probably also not suitable for accuracy reasons.

我要補(bǔ)充的另一件事是，即使您知道您的應(yīng)用程序僅支持英語(yǔ)，它可能仍需要處理人名，其中通常可能包含其他語(yǔ)言中使用的字符，在這些語(yǔ)言中它也同樣重要正確排序.對(duì)所有內(nèi)容使用 Unicode 規(guī)則有助于讓您高枕無(wú)憂，因?yàn)榉浅Ｂ斆鞯?Unicode 人員已經(jīng)非常努力地使排序正常工作.

One other thing I'll add is that even if you know your application only supports the English language, it may still need to deal with people's names, which can often contain characters used in other languages in which it is just as important to sort correctly. Using the Unicode rules for everything helps add peace of mind that the very smart Unicode people have worked very hard to make sorting work properly.

各部分的含義

首先，ci 用于不區(qū)分大小寫的排序和比較.這意味著它適用于文本數(shù)據(jù)，大小寫并不重要.其他類型的排序規(guī)則是 cs(區(qū)分大小寫)，用于區(qū)分大小寫的文本數(shù)據(jù)，以及 bin，用于編碼需要匹配的地方，逐位，這適用于真正編碼的二進(jìn)制數(shù)據(jù)的字段(包括，例如，Base64).區(qū)分大小寫的排序會(huì)導(dǎo)致一些奇怪的結(jié)果，區(qū)分大小寫的比較可能會(huì)導(dǎo)致重復(fù)值僅在字母大小寫中不同，因此區(qū)分大小寫的排序規(guī)則對(duì)文本數(shù)據(jù)不再受歡迎——如果大小寫對(duì)您很重要，那么其他情況下的標(biāo)點(diǎn)符號(hào)可以忽略等等可能也很重要，二進(jìn)制排序可能更合適.

Firstly, ci is for case-insensitive sorting and comparison. This means it's suitable for textual data, and case is not important. The other types of collation are cs (case-sensitive) for textual data where case is important, and bin, for where the encoding needs to match, bit for bit, which is suitable for fields which are really encoded binary data (including, for example, Base64). Case-sensitive sorting leads to some weird results and case-sensitive comparison can result in duplicate values differing only in letter case, so case-sensitive collations are falling out of favor for textual data - if case is significant to you, then otherwise ignorable punctuation and so on is probably also significant, and a binary collation might be more appropriate.

接下來(lái)，unicode 或 general 指的是特定的排序和比較規(guī)則——特別是文本被規(guī)范化或比較的方式.utf8mb4 字符編碼有許多不同的規(guī)則集，unicode 和 general 是兩種試圖在所有可能的語(yǔ)言而不是一種特定語(yǔ)言中都能很好地工作的規(guī)則.這兩組規(guī)則之間的差異是本答案的主題.請(qǐng)注意，unicode 使用來(lái)自 Unicode 4.0 的規(guī)則.MySQL 的最新版本使用來(lái)自 Unicode 5.2 的規(guī)則添加了規(guī)則集 unicode_520，并使用來(lái)自 Unicode 9.0 的規(guī)則添加了 0900(刪除了unicode_"部分).

Next, unicode or general refers to the specific sorting and comparison rules - in particular, the way text is normalized or compared. There are many different sets of rules for the utf8mb4 character encoding, with unicode and general being two that attempt to work well in all possible languages rather than one specific one. The differences between these two sets of rules are the subject of this answer. Note that unicode uses rules from Unicode 4.0. Recent versions of MySQL add the rulesets unicode_520 using rules from Unicode 5.2, and 0900 (dropping the "unicode_" part) using rules from Unicode 9.0.

最后，utf8mb4當(dāng)然是內(nèi)部使用的字符編碼.在這個(gè)答案中，我只談?wù)摶?Unicode 的編碼.

And lastly, utf8mb4 is of course the character encoding used internally. In this answer I'm talking only about Unicode based encodings.

這篇關(guān)于utf8_general_ci 和 utf8_unicode_ci 有什么區(qū)別?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！