Re: collation in mysql

[Rick James]

Here's a comparison of the collation of the alphabet characters for the utf8 collations you might consider.

Notes:
* spanish2 puts ch after cz and ll after lz and Ñ,ñ after nz
* utf8_general_ci treats sharp-s as 's'; the rest treat it as 'ss'.
* I don't know whether the Spanish 'ordinals' are located somewhere in utf8_general_ci, and this script failed to find them.

utf8 : utf8_general_ci                A=a=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å    ae  az
utf8 : utf8_unicode_ci                A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å  ae  az  Æ=æ
utf8 : utf8_spanish_ci                A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å  ae  az  Æ=æ
utf8 : utf8_spanish2_ci               A=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å  ae  az  Æ=æ

utf8 : utf8_general_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_spanish_ci                B=b  C=c=Ç=ç      ch  cz  D=d  dz  Ð=ð
utf8 : utf8_spanish2_ci               B=b  C=c=Ç=ç  cz  ch      D=d  dz  Ð=ð

utf8 : utf8_general_ci                E=e=È=É=Ê=Ë=è=é=ê=ë
utf8 : utf8_unicode_ci                E=e=È=É=Ê=Ë=è=é=ê=ë
utf8 : utf8_spanish_ci                E=e=È=É=Ê=Ë=è=é=ê=ë
utf8 : utf8_spanish2_ci               E=e=È=É=Ê=Ë=è=é=ê=ë

utf8 : utf8_general_ci                F=f  fz     G=g  H=h
utf8 : utf8_unicode_ci                F=f  fz  ƒ  G=g  H=h
utf8 : utf8_spanish_ci                F=f  fz  ƒ  G=g  H=h
utf8 : utf8_spanish2_ci               F=f  fz  ƒ  G=g  H=h

utf8 : utf8_general_ci                I=i=Ì=Í=Î=Ï=ì=í=î=ï
utf8 : utf8_unicode_ci                I=i=Ì=Í=Î=Ï=ì=í=î=ï
utf8 : utf8_spanish_ci                I=i=Ì=Í=Î=Ï=ì=í=î=ï
utf8 : utf8_spanish2_ci               I=i=Ì=Í=Î=Ï=ì=í=î=ï

utf8 : utf8_general_ci                J=j  K=k  L=l      ll  lz  M=m  N=n=Ñ=ñ  nz
utf8 : utf8_unicode_ci                J=j  K=k  L=l      ll  lz  M=m  N=n=Ñ=ñ  nz
utf8 : utf8_spanish_ci                J=j  K=k  L=l      ll  lz  M=m  N=n      nz  Ñ=ñ
utf8 : utf8_spanish2_ci               J=j  K=k  L=l  lz  ll      M=m  N=n      nz  Ñ=ñ

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

utf8 : utf8_general_ci                P=p  Q=q  R=r  S=s=ß=Š=š  sh  ss    sz  T=t  TM=tm    tz
utf8 : utf8_unicode_ci                P=p  Q=q  R=r  S=s=Š=š    sh  ss=ß  sz  T=t  TM=tm=™  tz
utf8 : utf8_spanish_ci                P=p  Q=q  R=r  S=s=Š=š    sh  ss=ß  sz  T=t  TM=tm=™  tz
utf8 : utf8_spanish2_ci               P=p  Q=q  R=r  S=s=Š=š    sh  ss=ß  sz  T=t  TM=tm=™  tz

utf8 : utf8_general_ci                U=u=Ù=Ú=Û=Ü=ù=ú=û=ü  ue  uz
utf8 : utf8_unicode_ci                U=u=Ù=Ú=Û=Ü=ù=ú=û=ü  ue  uz
utf8 : utf8_spanish_ci                U=u=Ù=Ú=Û=Ü=ù=ú=û=ü  ue  uz
utf8 : utf8_spanish2_ci               U=u=Ù=Ú=Û=Ü=ù=ú=û=ü  ue  uz

utf8 : utf8_general_ci                V=v  W=w  X=x  Y=y=Ý=ý=ÿ=Ÿ  yz  Z=z=Ž=ž  zh  zz
utf8 : utf8_unicode_ci                V=v  W=w  X=x  Y=y=Ý=ý=ÿ=Ÿ  yz  Z=z=Ž=ž  zh  zz  Þ=þ
utf8 : utf8_spanish_ci                V=v  W=w  X=x  Y=y=Ý=ý=ÿ=Ÿ  yz  Z=z=Ž=ž  zh  zz  Þ=þ
utf8 : utf8_spanish2_ci               V=v  W=w  X=x  Y=y=Ý=ý=ÿ=Ÿ  yz  Z=z=Ž=ž  zh  zz  Þ=þ