MySQL CHARACTER SET Binary分析

举报
jackcao 发表于 2022/10/31 10:42:18 2022/10/31
【摘要】 本文针对MySQL 的character set binary进行了详细分析。对于MySQL 与其他数据库之间进行字符集映射,是很好的参考材料。

MySQL CHARACTER SET Binary分析

Jack Cao

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

1.1      字符集和collation

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

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

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

A:0

B:1

a:2

b:3

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

假设我们要比较连个字符AB,简单方式是看他们对应的编码,0A)和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中只有一个规则,就是用来对字符进行比较和编码,这个简单的规则collationbinary collation

 

1.2      Server的字符集和collation

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

Server默认字符集:utf8mb4

Server默认collationutf8mb4_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

表的字符集和collationMySQL的扩展,标准SQL中没有。

1.5      字段的字符集和collation

每个字符型的列(例如,charvarchartext的数据类型)都有字段的字符集和字段的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;

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

| Charset  | Description                     | Default collation   | Maxlen |

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

| 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 |

| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |

| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |

| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |

| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |

| ascii    | US ASCII                        | ascii_general_ci    |      1 |

| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |

| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |

| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |

| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |

| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |

| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |

| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |

| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |

| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |

| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |

| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |

| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |

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

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

| cp866    | DOS Russian                     | cp866_general_ci    |      1 |

| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |

| macce    | Mac Central European            | macce_general_ci    |      1 |

| macroman | Mac West European               | macroman_general_ci |      1 |

| cp852    | DOS Central European            | cp852_general_ci    |      1 |

| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |

| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |

| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |

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

| utf16le  | UTF-16LE Unicode                | utf16le_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 |

| binary   | Binary pseudo charset           | binary              |      1 |

| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |

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

| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |

| 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字符集和binarycollation,在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 BINARYVARCHAR becomes VARBINARY, and TEXT becomes BLOB

数据类型为字符串类指定CHARACTER SET binary  属性时,会导致列在被创建时变成对应的二进制串数据类型:CHAR变成BINARYVARCHAR变成VARBINARYTEXT会变成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;

截图:

从上图可知,表tt1tt2的创建语法和字段类型的确不一样,如图中红框所示,通过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;

从图可以看出:

  1. 字符型字段(CHAR/VARCHA/TEXT)后加上CHARACTER SET+具体的字符集+ BINARY这种语法,仅仅用来指定字段的collation为具体的字符集_bin这种collation,不改变字段类型(CHAR/VARCHA/TEXT)。
  2. 字符型字段(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),其字符集为bianrycollation binary。二进制字符串是一系列的二进制字节,比较和排序基于对应数值。

Nonbinary strings(字段类型为CHAR VARCHA TEXT)拥有多个collation

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

例如latin1big5字符集的二进制collation分别为latin1_bin  big5_binutf8mb4字符集例外,有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 stringscollation定义了字符值得排列和比较顺序。对_bin collation来讲,顺序是基于numeric character code values数值字符编码值(具体可以参考第五章),这一点和binary string的顺序类似,除了字符编码值可能为多字节。

4.2      字符集转换

nonbinary string的字符集可以自动转换成其他字符集,即使字符串采用的是_bincollation

例如,不同字符集之间赋值:

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)的比较

MySQLcollation有填充属性,要么适用空格填充,要么不填充。

大部分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 (BINARYVARBINARYBLOB ),所有的字节,包括尾随空格,比较时都参与比较,例如:

 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字符集为例

Aascii编码为65

aascii编码为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进制的9716进制的41,对应10进制的65

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

注:ASCIIlatin1字符集的快捷方式,8.0.28版本后废弃。

6       总结

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

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

4.如果从具体的建表预计中看不出具体的字符集信息,可以最终从 show full columns [Table_name] 结果中进行确认。

Collation 列为NULLbinary,不考虑和其他种类数据库字符集映射

Collation 列为_bin类似的信息时,才考虑和其他数据库进行字符集的类型映射

7       其他:Collation后缀参考

Suffix

Meaning

_ai

Accent-insensitive

_as

Accent-sensitive

_ci

Case-insensitive

_cs

Case-sensitive

_ks

Kana-sensitive

_bin

Binary

常见的cs:大小写敏感

ci:大小写不敏感

8       参考文档

http://www.postgres.cn/docs/9.5/multibyte.html

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

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

https://blog.51cto.com/u_15023289/2561174

https://www.cnblogs.com/cchust/p/4327019.html

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

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

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

https://www.thethingsnetwork.org/docs/devices/bytes/

https://www.december.com/html/spec/codes.html

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html

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

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

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

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

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

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

 

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。