- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

MySQL CHARACTER SET Binary分析

jackcao 发表于 2022/10/31 10:42:18 2022/10/31

【摘要】本文针对MySQL 的character set binary进行了详细分析。对于MySQL 与其他数据库之间进行字符集映射，是很好的参考材料。

Jack Cao

1 基础知识：MySQL中的字符集和collation

1.1 字符集和collation

字符集是一套符号和编码规则。

collation是一套规则，用来比较字符集中的字符。

例如，有A,B,a,b四个字母，字母对应一个数字，例如

A:0

B:1

a:2

b:3

字符A就是个符号，数字0是A对应的编码，四个字符和字符对应的编码，就是一套字符集。

假设我们要比较连个字符A和B，简单方式是看他们对应的编码，0（A）和1(B)。

例如0<1 ，则我们可以说A是小于B的。

什么是binary collation：

The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.

如上例（A:0,B:1,a:2,b:3），在collation中只有一个规则，就是用来对字符进行比较和编码，这个简单的规则collation叫binary collation。

1.2 Server的字符集和collation

MySQL Server具有server级别的字符集和server级别的collation。

Server默认字符集：utf8mb4

Server默认collation：utf8mb4_0900_ai_ci

Server的字符集和collation可以再server启动时通过命令行参数或文件时进行运行态时改变。

例如：

mysqld --character-set-server=utf8mb4

mysqld --character-set-server=utf8mb4 \

--collation-server=utf8mb4_0900_ai_ci

一个改变设置的方法是重新编译，来改变默认的server的字符集和collation。

例如

cmake . -DDEFAULT_CHARSET=latin1

或者:

cmake . -DDEFAULT_CHARSET=latin1 \

-DDEFAULT_COLLATION=latin1_german1_ci

1.3 数据库的字符集和collation

每个database可以有database的字符集和collation。

在创建database和修改databse的语句中指定，例如

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]

关键字database，也可以替换为schema

MySQL按如下规则处理database的字符集和collation：

字符集和COLLATE 都指定了，按指定的字符集和指定的COLLATE 。
字符集指定了，没有指定COLLATE，使用指定的字符集和指定字符集对应的默认collation
COLLATE 指定了，字符集没有指定，则使用指定的COLLATE 对应的字符集和指定的COLLATE
字符集和COLLATE 都没指定，则使用server的默认字符集和默认collation

1.4 表的字符集和collation

每个表都有标的字符集和表的collation。在建表语言和更新表语句中可以指定表的字符集和表的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]

MySQL按如下方式，来选择表的字符集和表的collation：

字符集和COLLATE 都指定了，按指定的字符集和指定的COLLATE 。
字符集指定了，没有指定COLLATE，使用指定的字符集和指定字符集对应的默认collation
COLLATE 指定了，字符集没有指定，则使用指定的COLLATE 对应的字符集和指定的COLLATE
字符集和COLLATE 都没指定，则使用数据库的默认字符集和默认collation

在表的定义中，如果字段的字符集和collation 都没有指定，则使用表的字符集和collation作为默认字符集和默认collation。

表的字符集和collation是MySQL的扩展，标准SQL中没有。

1.5 字段的字符集和collation

每个字符型的列（例如，char，varchar，text的数据类型）都有字段的字符集和字段的collation。

字段的定义语法在 create table 和alter table中有选项

例如

col_name {CHAR | VARCHAR | TEXT} (col_length)

[CHARACTER SET charset_name]

[COLLATE collation_name]

也适用枚举和集合类型的字段：

col_name {ENUM | SET} (val_list)

[CHARACTER SET charset_name]

[COLLATE collation_name]

CREATE TABLE caotest1

(

col1 VARCHAR(5)

CHARACTER SET latin1

COLLATE latin1_german1_ci

);

ALTER TABLE caotest1 MODIFY

col1 VARCHAR(5)

CHARACTER SET latin1

COLLATE latin1_swedish_ci;

MySQL 按如下规则选择列的字符集和collation

字符集和COLLATE 都指定了，按指定的字符集和指定的COLLATE 。
字符集指定了，没有指定COLLATE，使用指定的字符集和指定字符集对应的默认collation
COLLATE 指定了，字符集没有指定，则使用指定的COLLATE 对应的字符集和指定的COLLATE
字符集和COLLATE 都没指定，则使用表的默认字符集和默认collation，目前表默认的字符集和默认collation分别是latin1 and collation latin1_bin。

字符集和COLLATE语法都是标准SQL的语法。

1.6 字符集和默认的COLLATE

查询字符集和默认的COLLATE，使用show character set; 命令，例如：

mysql> show character set;

+----------+---------------------------------+---------------------+--------+

+----------+---------------------------------+---------------------+--------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

+----------+---------------------------------+---------------------+--------+

41 rows in set (0.00 sec)

2 CHARACTER SET binary是什么

最近在分析MySQL的字符集和openGauss字符集的映射关系，在MySQL中可以运行SHOW CHARACTER SET;

命令，查看数据库相关的字符集

其中binary字符集和binary的collation，在openGauss/PostgreSQL中没有对应的类型。

MySQL的官方文档中针对character set binary的描述：

https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html

针对红框中的描述：

Specifying the CHARACTER SET binary attribute for a character string data type causes the column to be created as the corresponding binary string data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB.

数据类型为字符串类指定CHARACTER SET binary 属性时，会导致列在被创建时变成对应的二进制串数据类型：CHAR变成BINARY，VARCHAR变成VARBINARY，TEXT会变成BLOG.

随后又以创建一个表为例：

CREATE TABLE t

(

c1 VARCHAR(10) CHARACTER SET binary,

c2 TEXT CHARACTER SET binary,

c3 ENUM('a','b','c') CHARACTER SET binary

);

表会变成下面的定义：

CREATE TABLE t

(

c1 VARBINARY(10),

c2 BLOB,

c3 ENUM('a','b','c') CHARACTER SET binary

);

从英文的描述来看，这两种表的创建语法，是等同的。

验证：

CREATE TABLE tt1

(

c1 VARCHAR(10) CHARACTER SET binary,

c2 TEXT CHARACTER SET binary,

c3 ENUM('a','b','c') CHARACTER SET binary

);

CREATE TABLE tt2

(

c1 VARBINARY(10),

c2 BLOB,

c3 ENUM('a','b','c') CHARACTER SET binary

);

查看表字段的定义信息

show full columns from tt1;

show full columns from tt2;

截图：

从上图可知，表tt1和tt2的创建语法和字段类型的确不一样，如图中红框所示，通过show full columns from [table_name]命令查询后，2个表除了表名不一样，其他信息都一样，如上图黄匡中所示。

3 CHARACTER SET+具体字符集+binary是什么

BINARY属性（注意，是属性）是非标准MySQL扩展，是用来指定字段的字符集的binary (_bin) collation的快捷方式（如果字段的字符集没有指定，则使用表的字符集，即上例中的c2字段）。在这种情况下，字段值的比较和排序就基于数值字符编码值。例如按如下语句建表:

CREATE TABLE t

(

c1 VARCHAR(10) CHARACTER SET latin1 BINARY,

c2 TEXT BINARY

) CHARACTER SET utf8mb4;

则表建好后有如下的定义:

CREATE TABLE t (

c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,

c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

) CHARACTER SET utf8mb4;

验证：

CREATE TABLE tt3

(

c1 VARCHAR(10) CHARACTER SET latin1 BINARY,

c2 TEXT BINARY

) CHARACTER SET utf8mb4;

CREATE TABLE tt4 (

c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin,

c2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

) CHARACTER SET utf8mb4;

show full columns from tt3;

show full columns from tt4;

从图可以看出：

字符型字段（CHAR/VARCHA/TEXT）后加上CHARACTER SET+具体的字符集+ BINARY这种语法，仅仅用来指定字段的collation为具体的字符集_bin这种collation，不改变字段类型（CHAR/VARCHA/TEXT）。
字符型字段（CHAR/VARCHA/TEXT）后直接加上BINARY这种语法，仅仅用来指定字段的collation为具体的字符集_bin这种collation，不改变字段类型（CHAR/VARCHA/TEXT），这时字段的字符集群使用表的字符集。

注意：8.0.17版本后，通过BINARY属性来指定 _bin collation的方法被废弃，请明确指定具体的字符集的_bin collation。

4 binary Collation 和 _bin Collations比较

https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html

Binary strings（字段类型为BIANRY,VARBIANRY,BLOB），其字符集为bianry，collation 为binary。二进制字符串是一系列的二进制字节，比较和排序基于对应数值。

Nonbinary strings（字段类型为CHAR VARCHA TEXT）拥有多个collation。

一个非二进制的字符集，可以有多个collation。其中一个collation是二进制的，通过_bin后缀来命名。

例如latin1和big5字符集的二进制collation分别为latin1_bin 和 big5_bin。utf8mb4字符集例外，有2个_bin 类型的collation，分别为utf8mb4_bin 和 utf8mb4_0900_bin。

binary collation 从如下多个方面与_bin collations不一样：

比较和排序的单位
字符集转换
大小写转换
尾随空格（Trailing Space）的比较
插入和查询尾随空格的处理

4.1 比较和排序的单位

Binary strings是一系列的字节（byte），对binary collation来说，比较和排序是基于字节的数值值（具体可以参考第五章）。Nonbinary strings是一系列的字符（characters）或多字节。nonbinary strings的collation定义了字符值得排列和比较顺序。对_bin collation来讲，顺序是基于numeric character code values数值字符编码值（具体可以参考第五章），这一点和binary string的顺序类似，除了字符编码值可能为多字节。

4.2 字符集转换

nonbinary string的字符集可以自动转换成其他字符集，即使字符串采用的是_bin的collation：

例如，不同字符集之间赋值：

UPDATE t1 SET utf8mb4_bin_column=latin1_column;

INSERT INTO t1 (latin1_column) SELECT utf8mb4_bin_column FROM t2;

插入或更新字段值时采用字符串字面量：

SET NAMES latin1;

INSERT INTO t1 (utf8mb4_bin_column) VALUES ('string-in-latin1');

从server端发送信息到client端

SET NAMES latin1;

SELECT utf8mb4_bin_column FROM t2;

Binary string类型的字段不会发生字符集转换。

4.3 大小写转换

Nonbinary 字符集的collation包含字符的大小写信息，nonbinary string字符可以大小写转换，即使_bin collation排序时忽略大小写。

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;

mysql> SELECT LOWER('aA'), UPPER('zZ');

+-------------+-------------+

| LOWER('aA') | UPPER('zZ') |

+-------------+-------------+

| aa | ZZ |

+-------------+-------------+

大小写的概念在 binary string不适用。在进行大小写转换时，字符串必须先转到nonbinary的字符串的字符集。

mysql> SET NAMES binary;

mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));

+-------------+------------------------------------+

| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |

+-------------+------------------------------------+

| aA | aa |

+-------------+------------------------------------+

4.4 尾随空格（Trailing Space）的比较

MySQL的collation有填充属性，要么适用空格填充，要么不填充。

大部分collation使用空格填充。

nonbinary strings (CHAR, VARCHAR, TEXT )，字符串天真属性决定了字符串尾部的空间比较是的处理方式

空格填充的collation，尾随空格比较时忽略。字符串被走位无尾随空格来比较。

非填充的collation，在比较时尾随空格不被忽略，作为正常的字符来处理。

例如：

SELECT COLLATION_NAME, PAD_ATTRIBUTE

FROM INFORMATION_SCHEMA.COLLATIONS

WHERE COLLATION_NAME LIKE 'utf8mb4%bin';

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;

mysql> SELECT 'a ' = 'a';

+------------+

| 'a ' = 'a' |

+------------+

| 1 |

+------------+

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;

mysql> SELECT 'a ' = 'a';

+------------+

| 'a ' = 'a' |

+------------+

| 0 |

+------------+

针对binary strings (BINARY, VARBINARY, BLOB )，所有的字节，包括尾随空格，比较时都参与比较，例如：

 mysql> SET NAMES binary;

mysql> SELECT 'a ' = 'a';

+------------+

| 'a ' = 'a' |

+------------+

|          0 |

+------------+

4.5 插入和查询尾随空格的处理

CHAR(N)字段，保存非二进制的N个字符，插入时，小于N个字符时自动补充空格。查询时，尾随空格会被去掉。

BINARY(N)字段，保存N个二进制字节。插入时，小于N个字节时，自动扩展0x00字节。

查询时，不删除填充的信息。返回N个字符的信息。

mysql> CREATE TABLE t1 (

         a CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,

         b BINARY(10)

);

mysql> INSERT INTO t1 VALUES ('x','x');

mysql> INSERT INTO t1 VALUES ('x ','x ');

mysql> SELECT a, b, HEX(a), HEX(b) FROM t1;

+------+------------------------+--------+----------------------+

| a    | b                        | HEX(a) | HEX(b)                |

+------+------------------------+--------+----------------------+

| x    | 0x78000000000000000000 | 78     | 78000000000000000000 |

| x    | 0x78200000000000000000 | 78     | 78200000000000000000 |

+------+------------------------+--------+----------------------+

5 numeric character code values 数值字符编码值

以ascii字符集为例

A的ascii编码为65

a的ascii编码为97

则A(65)<a(97)

CREATE TABLE tt5 (

c varchar(1) BINARY

) CHARACTER SET latin1 ;

insert into tt5 SET c = 'a';

select HEX(c) ,c,c > 'A' from tt5 ;

show full columns from tt5;

HEX（）为16进制输出，16进制的61，对应10进制的97，16进制的41，对应10进制的65

不用计算器也可以这样算：16*4 + 1 = 65

注：ASCII是latin1字符集的快捷方式，8.0.28版本后废弃。

6 总结

1 建表时，char/varchar/text字段后的只有 character set binary 时，在MySQL中是特殊的字符串，不是标准SQL的字符集，字段类型变成了bianry/varbianry/blob类型，字符集collocation信息为NULL或binary时，即无具体的字符集信息了。

字符型字段（CHAR/VARCHA/TEXT）后加上CHARACTER SET+具体的字符集+ BINARY这种语法，仅仅用来指定字段的collation为具体的字符集_bin这种collation，不改变字段类型（CHAR/VARCHA/TEXT）。不改变字段的字符集类型。
字符型字段（CHAR/VARCHA/TEXT）后直接加上BINARY这种语法，仅仅用来指定字段的collation为具体的字符集_bin这种collation，不改变字段类型。不改变字段的字符集类型。