“utf8_unicode_ci”和“utf8_unicode_520_ci”的区别是什么?

I recently upgraded WAMPServer and now in phpMyAdmin, I see both collations utf8_unicode_ci and utf8_unicode_520_ci. I was using "utf8_unicode_ci" for Turkish applications, but I wonder what is different in the new *_520 collation?

我最近升级了WAMPServer，现在在phpMyAdmin中看到了utf8_unicode_ci和utf8_unicode_520_ci。我在土耳其应用程序中使用了“utf8_unicode_ci”，但是我想知道在新的*_520排序中有什么不同?

3 个解决方案

#1

As documented under Collation Names:

在校勘名称下记录:

Unicode collation names may include a version number to indicate the version of the Unicode Collation Algorithm (UCA) on which the collation is based. UCA-based collations without a version number in the name use the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. A collation name such as utf8_unicode_520_ci is based on UCA 5.2.0 weight keys: http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt.

Unicode排序规则名称可能包含一个版本号，以指示作为排序规则基础的Unicode排序规则算法(UCA)的版本。使用版本号为4.0.0的UCA权重键:http://www.unicode.org/public/uca/4.0.0/allkeys-4.0.0.0.txt。诸如utf8_unicode_520_ci之类的排序名称基于UCA 5.2.0权重键:http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt。

#2

If you want only Turkish, then consider utf8_turkish_ci. Here are most of the differences (taken from http://mysql.rjweb.org/utf8_collations.html ):

如果您只想要土耳其语，那么考虑utf8_turkish_ci。以下是大多数不同之处(来自http://sqmyl.rjweb.org/utf8_collations.html):

utf8 : utf8_turkish_ci             A=a=ª=À=Á=Á=Â=Ã=Ä=Å=à=á=á=â=ã=ä=å=Ā=ā=Ą=ą  Aa  ae          az       Æ=æ
utf8 : utf8_unicode_520_ci         A=a=ª=À=Á=Á=Â=Ã=Ä=Å=à=á=á=â=ã=ä=å=Ā=ā=Ą=ą  Aa  ae=Æ=æ      az
utf8 : utf8_unicode_ci             A=a=ª=À=Á=Á=Â=Ã=Ä=Å=à=á=á=â=ã=ä=å=Ā=ā=Ą=ą  Aa  ae          az       Æ=æ

utf8 : utf8_turkish_ci             B=b  C=c=Č=č          ch  cz  Ç=ç  D=d=Ď=ď      dz  Ð=ð
utf8 : utf8_unicode_520_ci         B=b  C=c=Ç=ç=Č=č      ch  cz       D=d=Ð=ð=Ď=ď  dz
utf8 : utf8_unicode_ci             B=b  C=c=Ç=ç=Č=č      ch  cz       D=d=Ď=ď      dz  Ð=ð

utf8 : utf8_turkish_ci             F=f  fz  ƒ  G=g=Ģ=ģ      gz  Ğ=ğ  H=h  hz
utf8 : utf8_unicode_520_ci         F=f  fz  ƒ  G=g=Ğ=ğ=Ģ=ģ  gz       H=h  hz
utf8 : utf8_unicode_ci             F=f  fz  ƒ  G=g=Ğ=ğ=Ģ=ģ  gz       H=h  hz

utf8 : utf8_turkish_ci             I=ı  i=Ì=Í=Í=Î=Ï=ì=í=í=î=ï=Ī=ī=Į=į=İ   ij=ĳ   iz              J=j
utf8 : utf8_unicode_520_ci         I=i=Ì=Í=Í=Î=Ï=ì=í=í=î=ï=Ī=ī=Į=į=İ      ij=ĳ   iz           ı  J=j
utf8 : utf8_unicode_ci             I=i=Ì=Í=Í=Î=Ï=ì=í=í=î=ï=Ī=ī=Į=į=İ      ij=ĳ   iz           ı  J=j

utf8 : utf8_turkish_ci             K=k=Ķ=ķ   L=l=Ĺ=ĺ=Ļ=ļ          lj=Ǉ=ǈ=ǉ      ll  lz  Ł=ł  M=m  N=n=Ñ=ñ=Ń=ń=Ņ=ņ=Ň=ň  nz
utf8 : utf8_unicode_520_ci         K=k=Ķ=ķ   L=l=Ĺ=ĺ=Ļ=ļ=Ł=ł      lj=Ǉ=ǈ=ǉ      ll  lz       M=m  N=n=Ñ=ñ=Ń=ń=Ņ=ņ=Ň=ň  nz
utf8 : utf8_unicode_ci             K=k=Ķ=ķ   L=l=Ĺ=ĺ=Ļ=ļ          lj=Ǉ=ǈ=ǉ      ll  lz  Ł=ł  M=m  N=n=Ñ=ñ=Ń=ń=Ņ=ņ=Ň=ň  nz

utf8 : utf8_turkish_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=ò=ó=ó=ô=õ          oe=Œ=œ      oz  Ö=ö      Ø=ø
utf8 : utf8_unicode_520_ci         O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=Ø=ò=ó=ó=ô=õ=ö=ø  oe=Œ=œ      oz
utf8 : utf8_unicode_ci             O=o=º=Ò=Ó=Ó=Ô=Õ=Ö=ò=ó=ó=ô=õ=ö      oe=Œ=œ      oz           Ø=ø

utf8 : utf8_turkish_ci             P=p  Q=q  R=r=Ř=ř   S=s=Š=Š=š=š        sh  ss=ß  sz  Ş=ş
utf8 : utf8_unicode_520_ci         P=p  Q=q  R=r=Ř=ř   S=s=Ş=ş=Š=Š=š=š    sh  ss=ß  sz
utf8 : utf8_unicode_ci             P=p  Q=q  R=r=Ř=ř   S=s=Ş=ş=Š=Š=š=š    sh  ss=ß  sz

utf8 : utf8_turkish_ci             T=t=Ť=ť  TM=tm=™  tz  U=u=Ù=Ú=Ú=Û=ù=ú=ú=û=Ū=ū=Ů=ů=Ų=ų      ue      uz  Ü=ü      V=v
utf8 : utf8_unicode_520_ci         T=t=Ť=ť  TM=tm=™  tz  U=u=Ù=Ú=Ú=Û=Ü=ù=ú=ú=û=ü=Ū=ū=Ů=ů=Ų=ų  ue      uz           V=v
utf8 : utf8_unicode_ci             T=t=Ť=ť  TM=tm=™  tz  U=u=Ù=Ú=Ú=Û=Ü=ù=ú=ú=û=ü=Ū=ū=Ů=ů=Ų=ų  ue      uz           V=v

utf8 : utf8_turkish_ci             W=w  X=x  Y=y=Ý=Ý=ý=ý=ÿ=Ÿ      yz
utf8 : utf8_unicode_520_ci         W=w  X=x  Y=y=Ý=Ý=ý=ý=ÿ=Ÿ      yz
utf8 : utf8_unicode_ci             W=w  X=x  Y=y=Ý=Ý=ý=ý=ÿ=Ÿ      yz

utf8 : utf8_turkish_ci             Z=z=Ž=Ž=ž=ž  zh  zz           Þ=þ
utf8 : utf8_unicode_520_ci         Z=z=Ž=Ž=ž=ž  zh  zz           Þ=þ
utf8 : utf8_unicode_ci             Z=z=Ž=Ž=ž=ž  zh  zz           Þ=þ

Notice how Æ, Ç, Ð, Ğ, etc, are sorted differently in the different collations.

注意ÆCÐĞ,等等,不同的排序不同的排序。

520 probably will not impact normal text, especially just Turkish. For example, Turkish and 520 consider Ð to be a separate letter, but utf8_unicode_ci treats it equal to D. It looks like 520 is 'closer' to turkish, but not identical for all the letters.

520可能不会影响正常的文本，尤其是土耳其语。例如,520年土耳其和认为Ð是一个单独的字母,但是utf8_unicode_ci将它等于d . 520年看起来是“接近”土耳其,但不完全相同的字母。

#3

As you can read here (thanks user3399549 for link) there is problem with sorting/comparing polish letter "Ł" (L with stroke) (lower case: "ł"; html esc: ł and Ł ) here Peter Gulutzan explain differences between collocations:

你可以在这里阅读(感谢user3399549链接)有问题排序/比较波兰字母“Ł”与中风(L)(小写:“ł”;html esc:& # 322;和& # 321;在这里，Peter Gulutzan解释了搭配之间的差异:

We have these collations and rules for Ł :

我们有这些Ł排序和规则:

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

In polish language letter Ł is after letter L and before M. We can write this in following way (for clarify):

在波兰语言字母Ł字母L和m .我们可以写这个问题之前(澄清):

L < Ł < M 

and 

L != Ł

So to avoid this kind of problems (with sorting/comapring) use utf8_unicode_ci.

因此，为了避免这种问题(使用排序/comapring)，请使用utf8_unicode_ci。

#1