MySQL8.0学习记录10 - 字符集与校对规则

最新推荐文章于 2025-02-21 09:10:02 发布

wisfy_21

最新推荐文章于 2025-02-21 09:10:02 发布

阅读量2.5k

点赞数

分类专栏： MySQL学习笔记文章标签：学习 mysql 数据库

本文链接：https://blog.youkuaiyun.com/wisfy_21/article/details/124641637

版权

MySQL学习笔记专栏收录该内容

22 篇文章

订阅专栏

MySQL8.0默认的字符集是utf8mb4 ，校对规则是utf8mb4_0900_ai_ci。
注意utf8mb4 最大是4字节一个字符，但不是所有字符都需要四字节：

  create table t_c(
  	n varchar(10)
  );
  insert into t_c values('A'),('中'),('中A');
  select n,LENGTH (n) from t_c;

n |LENGTH (n)|
--+----------+
A |         1|
中 |         3|
中A|         4|

MySQL可以在五个级别设置字符集，服务器、数据库、表、列和字符串字面量级别。

客户端也可以指定与数据库服务器的交流使用的字符集，举个例子：

SET NAMES 'latin1';
select '中午字符';

SET NAMES 'utf8';
select '中午字符';

前者显示乱码，后者正常显示：

中午字符        |
------------+
ä¸å�ˆå—ç¬¦|

中午字符|
----+
中午字符|

查看MySQL所有字符集两种方式：

select * from information_schema.CHARACTER_SETS;
show character set;

也可以加like限定查询范围

SHOW CHARACTER SET LIKE 'utf%';
-- 输出如下：
Charset|Description     |Default collation |Maxlen|
-------+----------------+------------------+------+
utf16  |UTF-16 Unicode  |utf16_general_ci  |     4|
utf16le|UTF-16LE Unicode|utf16le_general_ci|     4|
utf32  |UTF-32 Unicode  |utf32_general_ci  |     4|
utf8   |UTF-8 Unicode   |utf8_general_ci   |     3|
utf8mb4|UTF-8 Unicode   |utf8mb4_0900_ai_ci|     4|

查看MySQL所有校对规则
```
select * from information_schema.COLLATIONS;

SHOW COLLATION;
```
也可以限定查询某个字符集的COLLATION：
```
SHOW COLLATION WHERE Charset = 'utf8mb4';
```
这里提一下校对规则的一个特征：校对规则的名称一般以对应的字符集开始。

字符串表达式的属性 repertoire

repertoire （字元集或字元库）指的是字符串的字符集合；字符串表达式都有一个repertoire属性，它的值有两个ASCII和UNICODE。

也是repertoire 属性的存在，使得很多时候字符集之间可以转换。

repertoire 属性的值由字符串内容本身决定，而不是字符集。比如：

SET NAMES utf8mb4; SELECT 'abc';
SELECT _utf8mb4'def';

尽管指明当前字符集是utf8mb4，‘abc’，'def’都是ASCII可以表示的范畴，所以两个字符串的repertoire 属性的值都是ASCII。

接受一个参数并返回一个字符串的，返回结果的repertoire 属性跟入参一样；

对于返回一个字符串的FUNCTION，但是参数不是字符串的，返回结果的repertoire 属性值有变量character_set_connection决定：

show  VARIABLES like 'character_set_connection' ;

Variable_name           |Value|
------------------------+-----+
character_set_connection|ascii|

对于接受两个参数的函数来说，repertoire 跟更宽的那个一致，所以如果出现任一个UNICODE，那么结果就是UNICODE，否则就是ASCII。

目前只知道repertoire 属性的存在，使得不同字符集之间的字符串可以转换。但是很多时候不同字符集转换会出现问题，具体等到了解校对规则时候再学习一下。

元数据的字符集UTF-8

元数据（Metadata ）指的是描述数据库信息的数据，比如通过SHOW 或者INFORMATION_SCHEMA 表获取到的数据，像列名、数据库名、用户名等等。

因为元数据的要求（如下），MySQL采用Unicode 编码存储元数据信息。

元数据需要可以包含所有语言的字符
所有元数据的字符集必须一致，无论是通过SHOW 或SELECT INFORMATION_SCHEMA 取得的信息

所以，像 USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE(), and VERSION() 这些函数获取到的字符集也全是一样的。

metadata 的字符集可以通过 character_set_system 变量来看：

SHOW VARIABLES LIKE 'character_set_system'; 
-- 结果如下：
Variable_name       |Value  |
--------------------+-------+
character_set_system|utf8mb3|

需要注意的是，通过 DESCRIBE 或者SELECT column1 FROM t这样的查询取到的列名，其字符集不完全由 character_set_system控制，而是character_set_results 。可以通过SET NAMES 改变character_set_results ：

SHOW VARIABLES LIKE 'character_set_results';
-- 输出为：
Variable_name        |Value|
---------------------+-----+
character_set_results|     |

set NAMES 'utf8mb4';
SHOW VARIABLES LIKE 'character_set_results';
-- 输出为：
Variable_name        |Value  |
---------------------+-------+
character_set_results|utf8mb4|

如果character_set_results 为null，那么返回数据的字符集还是看character_set_system。

返回值客户端的Error messages 的字符集同metadata。

当在SQL中使用metadata且字符集不同时，会发生自动转换：

SELECT * FROM t1 WHERE USER() = latin1_column;
-- 这里latin1_column的值，会先转成UTF-8 再比较

INSERT INTO t1 (latin1_column) SELECT USER();
-- 这里User()的结果会先转成latin1

校对规则 Collation

命名约定

Collation的命名后缀反映了其具体规则，主要分四个方面：

_bin后缀，基于字符编码的二进制值
_ai、_as后缀，是否区分重音，a指Accent,i指insensitive,s指sensitive
_ci、_cs后缀，是否区分大小写，c指Case
_ks,对假名敏感, k指Kana，用于日语

除_bin外，所有Collation都有显式或隐含的是否区分重音以及是否区分大小写属性。如果没有显式指定_ai、_as，那_ci隐含了_ai,_cs隐含了_as。

服务器级别的Collation

可以在启动时指定：

mysqld --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_0900_ai_ci

可以通过两个变量来查看系统级别的字符集与校对规则：

show variables like 'character_set_server';
show variables like 'collation_server';

数据库级别的Collation

可以通过以下语句创建或修改：

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

可以通过以下几种方式查看数据库的字符集和校对规则：

-- 方式一
show variables like 'character_set_database';
show variables like 'collation_database';

-- 方式二
SELECT @@character_set_database, @@collation_database;

-- 方式三
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

创建函数或存储过程，如果没有为参数显式指定字符集和校对规则，将采用数据库级别的；LOAD DATA也是。

表级别Collation

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

在information_schema.tables查看TABLE_COLLATION

select TABLE_NAME,TABLE_COLLATION 
from information_schema.TABLES t  where TABLE_NAME  ='t_book'

列级别Collation

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

查看已有列字符集：

select CHARACTER_SET_NAME,COLLATION_NAME 
from information_schema.`COLUMNS` c  
where TABLE_NAME  = 'test'

需要注意的是，修改列级别的字符集时，如果字符集不兼容，可能会导致数据丢失。

字面量级别的Collation

字符字面量、十六进制字符串与二进制字符串可以使用Introducer来告诉解析器以什么样的方式解析字面量，但是实际没有发生类似CONVERT()函数那样的转换。其格式是：

[_charset_name] literal [COLLATE collation_name]

SELECT _latin1'中文A';
SELECT _utf8'中文A';

因为解析器使用给的的字符集来解析，所以第一个显示的不对：

ä¸æ–‡A|
-------+
ä¸æ–‡A|

中文A|
---+
中文A|

但是，这个只是解析器使用latin1来解析unicode字符，并不是改变字符串。可以对比convert()转换:

select convert('中文A' using latin1);

convert('中文A' using latin1)|
---------------------------+
??A                        |