mysql校验规则_mysql学习笔记5，mysql字符集和字符校验规则

一、什么是字符集、字符校验规则？简单的来说：

字符集(character set)：定义了字符以及字符的编码。

校验规则(collation)：定义了字符的比较规则，也可以称为排序规则、字符序。

MySQL提供了不同级别的设置，包括服务器级、连接级、数据库级、表级、列级。

二、MySQL支持的字符集、字符校验规则

MySQL支持多种字符集与 校验规则。

一个字符集对应至少一种校验规则(一般是1对多)。

两个不同的字符集不能有相同的字符校验规则。

每个字符集都有默认的字符校验规则。

(1)如何查看 mysql支持的字符集

方法1：

//查找mysql支持的所有字符集

mysql> SHOW CHARACTER SET;

//SHOW CHARACTER SET可以加where条件和like

//查找字符集名称为utf8的记录

mysql> SHOW CHARACTER SET WHERE Charset="utf8";

mysql> SHOW CHARACTER SET LIKE "utf8%";

方法2：查找information_schema数据库的CHARACTER_SETS表。

mysql> use information_schema;

mysql> select * from CHARACTER_SETS;

(2)什么是COLLATE 排序规则(校验规则、字符序)

对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。另外，mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

(3)如何查看到mysql支持的COLLATE

方式1：通过SHOW COLLATION进行查看

mysql> SHOW COLLATION;

//查找字符集为utf8对应的字符序

mysql> SHOW COLLATION WHERE Charset = 'utf8';

//查找collation名称包含utf8的所有字符序

mysql> show collation like '%utf8%';

方式2：查找information_schema数据库的COLLATIONS表。

mysql> USE information_schema;

mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";

(4)如何查看当前数据库的字符集

mysql> show variables like '%character%';

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

| Variable_name | Value |

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

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /MySQL8.0.12/share/charsets/ |

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

8 rows in set, 1 warning (0.00 sec)

名词解释：

character_set_client：客户端请求数据的字符

character_set_connection：客户机/服务器连接的字符集

character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。

character_set_filesystem：把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的

character_set_results：结果集，返回给客户端的字符集

character_set_server：数据库服务器的默认字符集

character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象(如表和列)的名字，也用于存储在目录表中的函数的名字。

(5)如何查看当前数据库的校验规则

mysql> show variables like 'collation%';

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

| Variable_name | Value |

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

| collation_connection | utf8mb4_general_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

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

3 rows in set, 1 warning (0.00 sec)

名词解释：

collation_connection 当前连接的字符集。

collation_database 当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation_server变量的值。

collation_server 服务器的默认校对。

排序方式的命名规则为：字符集名字语言后缀，其中各个典型后缀的含义如下：

_ci：不区分大小写的排序方式

_cs：区分大小写的排序方式

_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言

二、Server(服务器级)的字符集、字符序

当你创建数据库，且没有指定字符集、字符序时，server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

server默认字符集、字符序：在MySQL编译的时候，通过编译参数指定。

character_set_server、collation_server分别对应server字符集、server字符序。

(1)查看server字符集、字符序

mysql> SHOW VARIABLES LIKE "character_set_server";

mysql> SHOW VARIABLES LIKE "collation_server";

(2)可以在MySQL服务启动时，指定server字符集、字符序

mysqld --character-set-server=latin1

--collation-server=latin1_swedish_ci

(3)配置文件指定

[client]

default-character-set=utf8

--影响参数：character_set_client，character_set_connection 和character_set_results。

[mysql]

default-character-set=utf8

[mysqld]

collation-server = utf8_unicode_ci

init-connect='SET NAMES utf8'

character-set-server = utf8

(4) 运行时修改

mysql> SET character_set_server = utf8 ;

(5)编译时指定默认字符集、字符序

cmake . -DDEFAULT_CHARSET=latin1

-DDEFAULT_COLLATION=latin1_german1_c

三、连接级别

(1) 查看设置：

# 服务端使用这个编码来理解客户端发来的statements

show variables like 'character_set_client';

show variables like 'character_set_connection' ;

# 服务端使用这个编码回送结果集和错误信息

show variables like 'character_set_results';

四、database的字符集、字符序

指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库，可以分别指定不同的字符集/字符序。

(1)创建数据库：

CREATE DATABASE db_name

[[DEFAULT] CHARACTER SET charset_name]

[[DEFAULT] COLLATE collation_name]

(2)修改数据库：

ALTER DATABASE db_name

[[DEFAULT] CHARACTER SET charset_name]

[[DEFAULT] COLLATE collation_name]

(3)查看数据库的字符集/字符序

1、查看test_schema的字符集、排序规则。(需要切换默认数据库)

mysql> use test_schema;

Database changed

mysql> SELECT @@character_set_database, @@collation_database;

2、例子二：也可以通过下面命令查看test_schema的字符集、数据库(不需要切换默认数据库)

从 information_schema.SCHEMATA 获取

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name=”test_schema”;

3、例子三：也可以通过查看创建数据库的语句，来查看字符集。

mysql> SHOW CREATE DATABASE test_schema;

(4)database字符集、字符序是怎么确定的

创建数据库时，指定了CHARACTER SET或COLLATE，则以对应的字符集、排序规则为准。

创建数据库时，如果没有指定字符集、排序规则，则以character_set_server、collation_server为准，即以服务器级设定的值。

五、table的字符集、字符序

(1)创建表、修改表的语法如下，可通过CHARACTER SET、COLLATE设置字符集、字符序。

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]

//例子:

CREATE TABLE `test_schema`.`test_table` (

`id` INT NOT NULL COMMENT '',

PRIMARY KEY (`id`) COMMENT '')

DEFAULT CHARACTER SET = utf8;

(2)查看table的字符集/字符序

方式一：通过SHOW TABLE STATUS查看table状态

方式二：查看information_schema.TABLES的信息。

mysql> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";

方式三：通过SHOW CREATE TABLE确认。

六、column的字符集、排序

类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，语法如下：

col_name {CHAR | VARCHAR | TEXT} (col_length)

[CHARACTER SET charset_name]

[COLLATE collation_name]

(1)新增column并指定字符集/排序规则

例子如下：(创建table类似)

mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;

(2)查看column的字符集/字符序 information_schema.COLUMNS

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";

七、何时设置字符集、字符序

一般来说，可以在三个地方进行配置：

(1)创建数据库的时候进行配置。

(2)mysql server启动的时候进行配置。

(3)从源码编译mysql的时候，通过编译参数进行配置

八、默认情况下字符集选择规则

(1)编译MySQL 时，指定了一个默认的字符集

(2)安装MySQL 时，可以在配置文件 (my.cnf) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的;

(3)启动mysqld 时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;

(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server;

(5)当选定了一个数据库时，character_set_database被设定为这个数据库默认的字符集;

(6)在这个数据库里创建一张表时，表默认的字符集被设定为character_set_database，也就是这个数据库默认的字符集;

(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;