mysql字符集的迷惑_mysql中的character set utf16对应tidb

1. 查看mysq支持的字符集

show character set;

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
......
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

---

2. 查看字符集的校对规则

show collation;

show collation like 'utf8%';

select * 
from information_schema.collations 
where collation_name like 'utf8%';
1
2
3
4
5
6
7

---

3. 查看当前数据库的字符集

show variables like '%character%';

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| 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       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

参数解释：

character_set_client：客户端请求数据的字符集。
character_set_connection：从客户端接收到数据，然后传输的字符集。
character_set_database： 默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
character_set_server： 数据库服务器的默认字符集。
character_set_filesystem： 把os上文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的。
character_set_results： 结果集的字符集。
character_set_system： 这个值总是utf8，不需要设置，是为存储系统元数据的字符集。

4. 查看当前数据库的校对规则

show variables like '%collation%';

mysql> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

参数解释：

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

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

1）_ci：case insensitive 不区分大小写的排序方式。
2）_cs：case sensitive 区分大小写的排序方式。
3）_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言。

utf8_general_ci：不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs：区分大小写，如果用户名和邮箱用这个 就会照成不良后果
utf8_bin：字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容

---

5. MySQL字符集的设置

5.1 创建对象的默认值

字符集合校对规则有4个级别的默认设置：
1）服务器级别；
2）数据库级别；
3）表级别、列级别；
4）连接级别。

低级别的设置会集成高级别的设置。

通用的规则：先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某个列选择自己的字符集。

5.2 控制server和client端交互通信的配置

客户和服务器之间的字符集转换工作由以下几个MySQL系统变量控制：

1）character_set_server：mysql server默认字符集。
2）character_set_database：数据库默认字符集。
3）character_set_client：MySQL server假定客户端发送的查询使用的字符集。
4）character_set_connection：MySQL Server接收客户端发布的查询请求后，将其转换为character_set_connection变量指定的字符集。
5）character_set_results：mysql server把结果集和错误信息转换为character_set_results指定的字符集，并发送给客户端。
6）character_set_system：系统元数据(字段名等)字符集

还有以collation_开头的同上面对应的变量，用来描述字符序。

注意事项：
• my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集，不会对使用libmysqlclient库的应用程序产生任何作用！

• 对字段进行的SQL函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响。

• SQL语句中的裸字符串会受到连接字符集或introducer设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！

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

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

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

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

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

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

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

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

---

6 实例操作

6.1 为列指定字符集

6.2 为表指定字符集

6.3 为数据库指定字符集

6.4 为列指定校对规则

6.5 为表指定校对规则

6.6 为数据库指定校对规则

6.7 字符直接量字符集

6.8 配置MySQL服务器级别字符集

6.8.1 编译时指定

编译时可指定默认字符集和默认校对规则，要想同时更改默认字符集和校对规则，要同时使用-- with-charset=CHARSET和--with-collation=COLLATION选项。校对规则必须是字符集的合法校对规则。

./configure -- with-charset=CHARSET --with-collation=COLLATION
1

通过configure选项--with-extra-charsets=LIST，可以定义在服务器中再定义增加字符集。LIST 指下面任何一项：

1. 空格间隔的一系列字符集名。
2. complex -，以包括不能动态装载的所有字符集。
3. all -，以将所有字符集包括进二进制。

./configure -- with-charset=CHARSET --with-collation=COLLATION --with-extra-charsets=all
1

6.8.2 在配置文件my.cnf中指定

[mysqld]
character_set_server=utf8

--影响参数：character_set_server 和 character_set_database
--注意：修改后要重启数据库才能生效。
1
2

[client]
default-character-set=utf8

--影响参数：character_set_client, character_set_connection, character_set_results。
--注意：修改后无需重启数据库。
1
2

6.8.3 使用启动参数--character-set-server=CHARSET指定

./mysqld --character-set-server=utf8 >/dev/null 2>&1 &
1

--影响参数：character_set_server 和 character_set_database
1

6.8.4 客户端登录时通过–default-character-set=CHARSET指定

mysql -uroot -pmysql --default-character-set=utf8
1

--影响参数：set character_set_client，set character_set_connection，set character_set_results。
1

6.8.5 临时指定

a）分别指定

mysql> set character_set_client = utf8;
mysql> set character_set_connection = utf8;
mysql> set character_set_database = utf8;
mysql> set character_set_results = utf8;
mysql> set character_set_server = utf8;
1
2
3
4
5

b）mysql客户端使用：

set names utf8;
 <<等同于>>
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;
1
2
3
4
5

c）

set character set utf8;
 <<等同于>>
set character_set_client=utf8;
set character_set_results=utf8;
set collation_connection=@@collation_database;
1
2
3
4
5

6.8.6 总结:介绍几个MYSQL命令

6.8.6.1 查看数据库支持的所有字符集

show character set;
或
show char set;
1
2
3

6.8.6.2 查看当前状态:包含当前的字符集设置

status
或
\s
1
2
3

6.8.6.3 查看系统字符集设置:包括所有的字符集设置

show variables like 'char%';
1

6.8.6.4 查看库mydb002表my_table1的字符集设置

mysql> show table status from mydb002 like 'my_table1' \G
*************************** 1. row ***************************
           Name: my_table1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-06-28 00:15:40
    Update_time: 2020-06-28 00:15:55
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

6.8.6.5 查看表列的字符集设置，同一个表中，每列可设置成不同的字符集

mysql> show full columns from my_table1;
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)      | NULL               | NO   |     | NULL    |       | select,insert,update,references |         |
| name  | varchar(100) | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

6.8.6.6 设置字符集

1. 修改服务器级
   1.1. 临时更改：
   SET GLOBAL character_set_server=utf8;
   1.2. 永久更改：修改my.cnf文件
   [mysqld]
character-set-server=utf8

2. 修改数据库级
   2.1. 临时更改：
   SET GLOBAL character_set_database=utf8;
   2.2 永久更改：
   同1.2操作，改了服务器级别的此项跟随修改。

3. 修改表级，更改后永久生效
   ALTER TABLE table_name DEFAULT CHARSET utf8;

4. 修改列级，更改后永久生效；例：

alter table `table_test_01` 
change `field_test_01` varchar( 20 )
character set utf8 
collate utf8_general_ci 
default null;
1
2
3
4
5

5. 更改连接字符集
   5.1. 临时更改：
   set names utf8;
   5.2. 永久更改，修改my.cnf文件：
   [client]
default-character-set=utf8

6. 执行SQL语句时信息的路径是这样的
   信息输入路径：client→connection→server；
   信息输出路径：server→connection→results；

---

[client]
default-character-set=utf8

[mysql]

[mysqld]
character-set-server=utf8
1
2
3
4
5
6
7

字符集的选择

字符集“utf8mb4” 对应了我们常使用的UTF-8。

#查询utf8和utf8mb4长度信息
SHOW CHARSET LIKE 'utf8%';
# 修改表的字符集
ALTER TABLE xxxxxx CONVERT TO CHARACTER SET utf8mb4;
1
2
3
4

排序规则

排序规则按照字符集“utf8mb4”的继续说明，我们要说的字符集主要就三个：

utf8mb4_bin
utf8mb4_general_ci
utf8mb4_unicode_ci

1. utf8mb4_bin
   bin的意思就是二进制的意思，也就是说排序查找的规则是按照二进制的方式进行查找的，这里就会涉及到我们常常说到的数据库里面的大小写区分。比如说：

a的二进制编码是： 01100001
A的二进制编码是： 01000001
那么执行这两条sql语句的结果是不一样的，因为a和A的二进制编码是不一样的。
SELECT * FROM dm_user WHERE username='a';
SELECT * FROM dm_user WHERE username='A';
1
2
3
4
5

2. utf8mb4_unicode_ci 和 utf8mb4_general_ci
   这两种排序规则都是不区分大小写的，也就是说在这两种排序规则下，a和A是一样的。
   utf8mb4_unicode_ci 是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序。
   utf8mb4_general_ci 没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。
   utf8mb4_general_ci 在比较和排序的时候更快
   utf8mb4_unicode_ci 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

排序规则： 是指对指定字符集下不同字符的比较规则。其特征有以下几点

两个不同的字符集不能有相同的排序规则
两个字符集有一个默认的排序规则
有一些常用的命名规则。如_ci结尾表示大小写不敏感（caseinsensitive）,_cs表示大小写敏感（case sensitive）,_bin表示二进制的比较（binary）.