MySQL中utf8字符集、排序规则及utf8mb4_bin列大小写不敏感方法

jingxiao 2019-06-27

utf8mb4 和 utf8 比较

  • utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.
  • utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character.
  • utf8: An alias for utfmb3.

    (https://dev.mysql.com/doc/ref...

UTF-8是使用1~4个字节,一种变长的编码格式。(字符编码

mb4即 most bytes 4,使用4个字节来表示完整的UTF-8。而MySQL中的utf8是utfmb3,只有三个字节,节省空间但不能表达全部的UTF-8,只能支持“基本多文种平面”(Basic Multilingual Plane,BMP)。

推荐使用utf8mb4。

utf8mb4_unicode_ci 和 utf8mb4_general_ci 比较

general_ci 更快,unicode_ci 更准确

 in German and some other languages ß is equal to ss.

这种情况unicode_ci能准确判断。

具体有什么差别呢?参见下面的连接。

http://mysql.rjweb.org/utf8mb...

utf8mb4_general_ci           P=p  Q=q  R=r=Ř=ř   S=s=ß=Ś=ś=Ş=ş=Š=š  sh  ss    sz
utf8mb4_unicode_ci           P=p  Q=q  R=r=Ř=ř   S=s=Ś=ś=Ş=ş=Š=š    sh  ss=ß  sz

可以看到utf8mb4_general_ci中S=ß,而utf8mb4_unicode_ci中ss=ß 。

使用utf8mb4_bin可以将上面的字符区分开来。

貌似general_ci 也快不了多少,所以更推荐unicode_ci。

大小写敏感

utf8mb4_general_cs 大小写敏感

utf8mb4_bin 大小写敏感

但貌似不存在utf8_unicode_cs ,可能是算法决定的吧?

utf8mb4_bin 列大小写不敏感方法

需求

  1. 插入的时候UmanUmānUman 看做不同的单词。
  2. 查询的时候UmanUmānUman 都能同时查出来。

解决方案

使用MySQL虚拟生成列。MYSQL UTF8_bin case insensitive unique index

create table test_utf8_bin_ci
( u8 varchar(50) charset utf8mb4 collate utf8mb4_unicode_ci,
  u8_bin_ci varchar(50) charset utf8mb4 collate utf8mb4_bin as (lower(u8)) unique
);

insert into test_utf8_bin_ci (u8)
values ('A'),('Ä'),('Å'),('Â'),('Á'),('À');

MySQL中utf8字符集、排序规则及utf8mb4_bin列大小写不敏感方法

根据需求,插入数据时:

  • UmanUmān 看做不同的单词,所以要utf8_bin.
  • UmanUman看做相同的单词,所以添加unique约束,在utf8_bin区分大小写的情况下,使用low() 函数使其不区分大小写。

MySQL中utf8字符集、排序规则及utf8mb4_bin列大小写不敏感方法

查询时:

  • 查询u8列,utf8mb4_unicode_ci不区分大小写、不区分a和ā。都能查出来。

MySQL中utf8字符集、排序规则及utf8mb4_bin列大小写不敏感方法

相关推荐