第08期：有关 MySQL 字符集的注意事项

SQL调优头图.png

本文关键字：字符集、建库建表

一、数据库和字符集

1、建库时指定

创建数据库时，显式指定字符集和排序规则，同时，当切换到当前数据库后，参数 character_set_database，collation_database 分别被覆盖为当前显式指定的字符集和排序规则。举个简单例子，创建数据库 ytt_new2，显式指定字符集为 latin1，同时排序规则为 latin1_bin。之后在切换到数据库 ytt_new2 后，对应的系统参数也被修改。

mysql> create database ytt_new2 default character set latin1 collate latin1_bin;
Query OK, 1 row affected (0.03 sec)

mysql> use ytt_new2
Database changed

mysql> select @@character_set_database,  @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_bin           |
+--------------------------+----------------------+
1 row in set (0.00 sec)

2、改库时指定

改库类似建库，效果一样。但需要注意的一点是，修改库字符集与排序规则后，之前基于这个库创建的各种对象，还是沿用老的字符集与排序规则。举个例子，对存储过程的影响：

-- 简单写个存储过程
DELIMITER $$

USE `ytt_new2`$$

DROP PROCEDURE IF EXISTS `sp_demo`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_demo`(
    IN f1 VARCHAR(10),
    IN f2 VARCHAR(10)
    )
BEGIN
    DECLARE v1 VARCHAR(20);
    SET v1 = CONCAT(f1,f2);
    SELECT v1 AS result;
    END$$

DELIMITER ;

查看这个存储过程的字符集，这里看到排序规则是 latin1_bin，对应的字符集是 latin1，和数据库 ytt_new2 一致。

mysql> show create procedure sp_demo\G
*************************** 1. row ***************************
           Procedure: sp_demo
...
  Database Collation: latin1_bin
1 row in set (0.00 sec)

-- 那接下来改掉数据库的字符集为 UTF8
mysql> alter database ytt_new2 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected, 2 warning (0.02 sec)

mysql> select @@character_set_database,  @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.00 sec)

-- 再查看存储过程 sp_demo 的字符集，还是之前的。
mysql> show create procedure sp_demo\G
*************************** 1. row ***************************
           Procedure: sp_demo
...
  Database Collation: latin1_bin
1 row in set (0.00 sec)

-- 此时，调用存储过程，字符集不对，报编码错误。

mysql> call sp_demo('我','你');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x88\x91' for column 'f1' at row 1

-- 改变存储过程字符集只能删除重建，重新执行下之前的存储过程代码，再次调用，结果就正常了。

mysql> call sp_demo('我','你');
+--------+
| result |
+--------+
| 我你   |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

3、参数的指定

参数 character_set_database 和 collation_database 如果没有指定，默认继承服务器端对应参数 character_set_server 和 collation_server。

mysql> select @@character_set_server charset,  @@collation_server collation 
    -> union all
    -> select @@character_set_database,  @@collation_database;
+---------+--------------------+
| charset | collation          |
+---------+--------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
| utf8mb4 | utf8mb4_0900_ai_ci |
+---------+--------------------+
2 rows in set (0.00 sec)

那这种情况下，建库或者改库时不指定具体的字符集和排序规则，默认继承这两个参数：

mysql> show create database ytt_new3\G
*************************** 1. row ***************************
       Database: ytt_new3
Create Database: CREATE DATABASE `ytt_new3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

二、表和字符集

1、建表时指定（显式设置）

同建库一样，显式指定字符集和排序规则，优先级最高，以指定的值为准。

-- 创建新库 ytt_new4
mysql> create database ytt_new4;
Query OK, 1 row affected (0.02 sec)

mysql> use ytt_new4;
Database changed

-- 创建新表 t1, 字符集 latin1, 排序规则 latin1_bin
mysql> create table t1(a1 int) charset latin1 collate latin1_bin;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

3、继承设置（隐式转换）

默认继承所属数据库级别的字符集和排序规则。这里需要注意的是所属数据库，不是当前数据库。

-- 当前数据库 ytt_new4.
mysql> use ytt_new4;
Database changed

-- 表 t2 属于当前数据库 ytt_new4
mysql> create table t2(a1 int);
Query OK, 0 rows affected (0.05 sec)

-- 查看表 t2 字符集和排序规则
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- 创建表 t3，不过属于数据库 ytt_new5
mysql> create database ytt_new5 character set gbk;
Query OK, 1 row affected (0.03 sec)

mysql> create table ytt_new5.t3 (id int);
Query OK, 0 rows affected (0.04 sec)

-- 查看表 t3 字符集，和数据库 ytt_new5 一致
mysql> show create table ytt_new5.t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)

3、视图

视图其实就是虚拟的表，所以视图的字符集也划在表这块简单介绍下。视图的字符集完全依赖客户端的字符集设置。比如：

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

-- 此时视图 v_t3 编码为 gbk
mysql> create view v_t3 as select * from t3;
Query OK, 0 rows affected (0.01 sec)

mysql> set names gb18030;
Query OK, 0 rows affected (0.00 sec)

-- 此时视图 v_t31 编码为 gb18030
mysql> create view v_t3_1 as select * from t3;
Query OK, 0 rows affected (0.01 sec)

-- 查看这两个视图的编码
mysql> show create view v_t3\G
*************************** 1. row ***************************
                View: v_t3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t3` AS select `t3`.`a1` AS `a1` from `t3`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.01 sec)

mysql> show create view v_t3_1\G
*************************** 1. row ***************************
                View: v_t3_1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_t3_1` AS select `t3`.`a1` AS `a1` from `t3`
character_set_client: gb18030
collation_connection: gb18030_chinese_ci
1 row in set (0.00 sec)

4、触发器

触发器基于表，所以触发器也归类到表这块。其实触发器的编码规则和视图一样。也是依赖客户端的设定。比如一个简单的触发器：

-- 客户端编码为 utf8
mysql> set names utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 触发器的编码也继承同样的客户端编码
mysql> create trigger tr_after_insert_t3 after insert on t3 for each row  insert into t4 values(new.a1);
Query OK, 0 rows affected (0.01 sec)

mysql> show create trigger tr_after_insert_t3\G
*************************** 1. row ***************************
               Trigger: tr_after_insert_t3
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `tr_after_insert_t3` AFTER INSERT ON `t3` FOR EACH ROW insert into t4 values(new.a1)
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_0900_ai_ci
               Created: 2020-03-09 11:32:23.94
1 row in set (0.00 sec)

三、列和字符集

1、显式指定

同数据库和表一样，列也可以显式指定特定的字符集和排序规则。虽说是可以这样做，但是非常不推荐，最主要原因是每个列字符集不一样，导致写入和检索都得额外的编写 SQL。

-- 创建新库 ytt_new6
mysql> create database ytt_new6 character set latin1;
Query OK, 1 row affected (0.02 sec)

mysql> use ytt_new6;
Database changed

-- 在 ytt_new6 下创建表 t1，拥有字段 a1,a2,a3 分别给定不同的字符集和排序规则

mysql> create table t1( a1 char(10) charset latin1 collate latin1_bin,
                       a2 char(10) charset gbk collate gbk_bin,
                       a3 char(10) charset utf8mb4 collate utf8mb4_bin);
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a1` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `a2` char(10) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,
  `a3` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

那接下来简单插入一条记录,需要为每列添加 introduer，否则报错。

-- 没有显式指定每列字符集，报错
mysql> insert into t1 values ('character ','字符集合','字符集合');
ERROR 1366 (HY000): Incorrect string value: '\xAC\xA6\xE9\x9B\x86\xE5...' for column 'a2' at row 1

-- 为每列显式添加 Introducer
mysql> insert into t1 values (_latin1 'character ',_gbk '字符集合',_utf8mb4 '字符集合');
Query OK, 1 row affected (0.01 sec)

查询出来最麻烦，因为每次查询出来的结果只能有一个字符集，字符集兼容的列可以一起检索；不兼容的列得分开检索。举个例子，我想简单的 SELECT * 拿出所有记录，结果发现有一个字段据显示不正常。

mysql> select * from t1;
+-----------+------------------+--------------+
| a1        | a2               | a3           |
+-----------+------------------+--------------+
| character | 瀛楃?闆嗗悎      | 字符集合     |
+-----------+------------------+--------------+
1 row in set (0.00 sec)

所以针对这种情形，该怎么检索数据呢？必须得对单个字段检索或者是对兼容的列一起检索。

-- 以字符集 GBK 输出列 a1 a2，由于 a1 是保存的是字母，所以兼容输出。
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select a1,a2 from t1;
+-----------+--------------+
| a1        | a2           |
+-----------+--------------+
| character | 字符集合     |
+-----------+--------------+
1 row in set (0.00 sec)

-- 以字符集utf8mb4和gbk不兼容，得单独输出列a3。或者单独输出a2.
mysql> set names utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> select a3 from t1;
+--------------+
| a3           |
+--------------+
| 字符集合     |
+--------------+
1 row in set (0.00 sec)

2、隐式转换

这种方式，是最推荐的，也是最长的方式，所有列继承表的字符集，不单独指定。

-- 建表 t2，指定字符集为 gbk.
mysql> create table t2(a1 varchar(10),a2 varchar(10)) charset gbk;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.01 sec)

但是这里有一个需要注意的点，如果此时对表进行字符集变更，那表的列依然保留原来的字符集。例如：

mysql> alter table t2 charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` varchar(10) CHARACTER SET gbk DEFAULT NULL,
  `a2` varchar(10) CHARACTER SET gbk DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

如果想把表里字段的字符集也改了，应该用以下语句更改表字符集。

mysql> alter table t2 convert to character set utf8;
![杨涛涛自媒体.png](https://upload-images.jianshu.io/upload_images/6561209-16ba37690f38c069.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a1` varchar(10) DEFAULT NULL,
  `a2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

那到这儿，我们已经了解了字符集对数据库，表以及列的使用以及相关影响。大致总结下，这篇我详细介绍了字符集在 MySQL 数据库，表以及列相关对象处理时的注意事项，并且举例说明。希望对大家有帮助。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
杨涛涛自媒体.png