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

本文详细对比了utf8mb4与utf8的不同之处,解析了utf8mb4_unicode_ci与utf8mb4_general_ci的区别,并介绍了如何在MySQL中实现大小写不敏感的查询。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

转载:https://my.oschina.net/u/1245414/blog/1831347

一、utf8mb4 和 utf8 比较


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/utf8mb4_collations.html

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. 插入的时候Uman 和Umānuman 看做不同的单词。
  2. 查询的时候Uman 和Umā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'),('Ä'),('Å'),('Â'),('Á'),('À');

1529141523561

根据需求,插入数据时:

  • Uman 和Umān 看做不同的单词,所以要utf8_bin.

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

1529142760719

查询时:

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

1529142689449




### MySQL 5.7 中 utf8mb4_zh_0900_as_cs 字符集的兼容性与使用方法 #### 1. utf8mb4_zh_0900_as_cs 的定义与特性 字符集 `utf8mb4_zh_0900_as_cs` 是一种用于中文环境下的排序规则(collation)。其中: - `utf8mb4` 表示支持完整的 Unicode 字符集,包括 BMP 补充字符平面。 - `_zh_` 表示该排序规则专为中文设计。 - `_0900_` 表示基于 Unicode 9.0.0 标准。 - `_as_` 表示排序时区分重音符号(Accent-Sensitive)。 - `_cs_` 表示排序时区分大小写(Case-Sensitive)。 需要注意的是,`utf8mb4_zh_0900_as_cs` 排序规则是在 MySQL 8.0 中引入的[^4]。因此,在 MySQL 5.7 中并支持此排序规则。 #### 2. MySQL 5.7 对 `utf8mb4` 的支持 MySQL 5.7 支持 `utf8mb4` 字符集,并提供了一些常用的排序规则,例如: - `utf8mb4_general_ci`:区分大小写区分重音符号的通用排序规则。 - `utf8mb4_unicode_ci`:基于 Unicode 标准的排序规则,适用于多语言环境。 - `utf8mb4_bin`:二进制排序规则,严格区分大小写字符顺序。 在 MySQL 5.7 中,如果需要对中文进行区分大小写的排序,可以使用 `utf8mb4_bin` 排序规则作为替代方案[^3]。 #### 3. 使用方法 以下是设置表或使用 `utf8mb4` 字符集排序规则的示例代码: ```sql -- 创建数据库时指定字符集排序规则 CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; -- 创建表时指定字符集排序规则 CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(255) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; -- 修改现有表的字符集排序规则 ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; -- 修改现有字符集排序规则 ALTER TABLE mytable MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; ``` #### 4. 升级到 MySQL 8.0 的建议 由于 `utf8mb4_zh_0900_as_cs` 是 MySQL 8.0 中新增的功能,若需要使用该排序规则,建议升级到 MySQL 8.0 或更高版本。升级前需注意以下事项: - 检查当前数据库中的数据是否兼容新的字符集排序规则。 - 测试升级后的查询性能排序行为是否符合预期。 ####
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值