Logo
活死人の行知路

MySQL字符集

📅 | 📝 672 字
#MySQL

字符集规定了字符在MySQL中的格式，不同的字符集有不同的编码规则和排序规则。MySQL选择合适的字符集非常重要，如果选择不切当，不仅影响数据的存储，还会影响客户端和服务端的通信。

alt text

常见的字符集

根据您提供的第四张图片，关于 MySQL 常用字符集的 Markdown 表格如下：

字符集长度 (Max Bytes)说明
GBK$2$支持中文，但是不是国际通用字符集
latin1$1$MySQL 8.0 之前默认的字符集
utf8$3$支持中英文混合场景，是国际通用字符集
utf8mb4$4$完全兼容 utf8，用四个字节存储更多的字符，MySQL 8.0 默认的字符集

关键点解析：

  1. utf8 与 utf8mb4 的区别：
    • MySQL 中的 utf8 实际上是 utf8mb3，它最多只能存储 $3$ 个字节的字符。
    • utf8mb4 是真正的 UTF-8 编码，支持 $4$ 字节存储。它不仅兼容 utf8，还能存储表情符号（Emoji）、特殊的汉字（如生僻字）和一些特殊符号。
    • 建议：在现代开发中，始终优先选择 utf8mb4。
  2. GBK：
    • 主要用于中文环境，存储汉字占 $2$ 个字节，存储英文字母占 $1$ 个字节。虽然比 utf8 存中文更省空间，但缺乏跨语言的通用性。
  3. latin1：
    • 也称为 ISO-8859-1，仅支持西欧语言，不支持中文，由于编码简单且节省空间，在老旧版本的默认设置中较常见。

字符集对MYSQL的影响

  • 如果设置不合理，会产生乱码的可能

实现

set names utf8mb4;
create table charset_test1(a varchar(10)) charset=utf8mb4;
set names gbk;
mysql> insert into charset_test1(a) values ('中文');
ERROR 1366 (HY000): Incorrect string value: '\xAD\xE6\x96\x87' for column 'a' at row 1

如果出现上面的错误，这个和sql_mode有关系，可以查看下sql_mode:

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

将 STRICT_TRANS_TABLES 严格模式去掉：

set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

重新执行之前的SQL就可以写入进去嘞:

mysql> insert into charset_test1(a) values ('中文');
Query OK, 1 row affected, 1 warning (0.00 sec)

但是提示有一条warning，可以查看下warning:

mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD\xE6\x96\x87' for column 'a' at row 1 |
+---------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

虽然是warning，但是表示已经把数据写入进去嘞。

查看下全表数据：

mysql> select * from charset_test1;
+-------+
| a     |
+-------+
| �?��     |
+-------+
1 row in set (0.01 sec)

可以看到写入的数据是乱码，这说明字符集设置不合理可能会出现乱码。

  • 在关联查询中，如果关联字段的字符集不一致，则会出现隐式转换，从而无法使用关联字段的索引。

设置字符集

先查看当前数据库可用的字符集

show character set;

显式的就是当前实例支持的所有字符集。一个字符集至少有一个排序规则，查看某个字符集的排序规则：

show collation where charset='utf8mb4';

MySQL的字符集和排序规则有4个级别的设置，包括服务器级别、数据库级别、表级别，字段级别。

服务器级别的设置

查看当前MySQL服务的字符集

mysql> show variables like 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.07 sec)

查看当前MySQL服务的规则

show variables like 'collation_server';
mysql> show variables like 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_0900_ai_ci |
+------------------+--------------------+
1 row in set (0.05 sec)

调整服务器级别的字符集和交流规则 一种方式是在启动的时候配置：

mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci;

另一种方式在配置文件中添加对应的配置： vim /data/mysql/conf/my.cnf 搜索character-set-server，并配置:

character-set-server = utf8
collation_server=utf8mb4_0900_ai_ci

还有一种动态修改的方式： set global character_set_server=utf8mb4; set global collation_server=utf8mb4_0900_ai_ci;

数据库级别

每个数据库都可以设置单独的字符集和排序规则。

create database test6 character set latin1 collate latin1_swedish_ci;

查看某个库的字符集和排序规则：

mysql> use test6;
Database changed
mysql> select @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+
1 row in set (0.00 sec)

还有一种方式查看某个库的字符集和排序规则：

show create database test6;

还可以到 information_scheme 库中查看：

mysql> select default_character_set_name, default_collation_name from information_schema.schemata where schema_name='test6';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+
1 row in set (0.00 sec)

表级别指定字符集和排序规则

mysql> creat e table test1(a varchar(10)) engine=innodb charset=utf8mb4 collate=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `a` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表级别字符集和排序规则：

alter table test1 charset=latin1 collate=latin1_danish_ci;

行级

mysql> create table column_character_test(name varchar(10) charset latin1 collate latin1_danish_ci);
Query OK, 0 rows affected (0.04 sec)

mysql> show create table column_character_test;
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                  |
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| column_character_test | CREATE TABLE `column_character_test` (
  `name` varchar(10) CHARACTER SET latin1 COLLATE latin1_danish_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表级别字符集和排序规则：

alter table column_character_test modify name varchar(10) charset utf8mb4 collate utf8mb4_0900_ai_ci;
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                     |
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| column_character_test | CREATE TABLE `column_character_test` (
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

排序规则

查看MySQL中的排序规则

show collation;

排序规则后缀

MySQL字符集使用建议

  • 显式定义字符集
  • 统一客户端和服务端编码
  • 无特殊情况，建议使用 utf8mb4

实验

  • 要求：设计一张商品表，标明为 product_info，字符集为utf8mb4: 根据您提供的第六张图片，关于商品表字段及要求的 Markdown 表格如下：
字段要求
主键自增
商品名字包括中英文，50个字符以内
商品价格不超过百万，精确到小数点后两位
商品生产日期包括年月日和时分秒
商品厂商包括中英文，50个字符以内
商品分类使用枚举类型，包括’洗漱日化’、‘生鲜水果’、‘休闲百货’、‘酒水调料’、‘其他’
记录创建时间能记录每个商品信息的录入时间
记录更新时间能记录每个商品信息的更新时间

针对这些要求的建表类型建议：

结合之前您提供的几张图片关于数据类型的知识，针对上述需求，在 MySQL 中的具体数据类型选择建议如下：

  1. 主键：建议使用 INT UNSIGNED AUTO_INCREMENT 或 BIGINT UNSIGNED AUTO_INCREMENT。
  2. 商品名字 & 商品厂商：建议使用 VARCHAR(50)，并配合 utf8mb4 字符集以完美支持中英文。
  3. 商品价格：因为涉及金额，必须追求精确，建议使用 DECIMAL(8, 2)（最高可表示 $999999.99$，满足不超过百万的要求）或者更宽裕的 DECIMAL(10, 2)。
  4. 商品生产日期：要求包含年月日时分秒，建议使用 DATETIME。
  5. 商品分类：按要求使用 ENUM 类型：ENUM('洗漱日化', '生鲜水果', '休闲百货', '酒水调料', '其他')。
  6. 记录创建时间：建议使用 DATETIME 或 TIMESTAMP，可设置默认值为 CURRENT_TIMESTAMP。
  7. 记录更新时间：建议使用 DATETIME 或 TIMESTAMP，可设置默认值及自动更新 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
create table product_info(
    id int primary key auto_increment comment '主键',
    prod_name varchar(50) not null comment '商品名称',
    prod_price decimal(10, 2) not null default 0 comment '价格',
    prod_date timestamp default null comment '商品生产日期',
    prod_manufacturer varchar(50) default null comment '商品厂商',
    prod_sort enum('洗漱日化', '生鲜水果', '休闲百货', '酒水调料', '其他') default null comment '商品分类',
    create_time timestamp not null default CURRENT_TIMESTAMP comment '创建时间',
    update_time timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间'
) engine=innodb charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment '商品信息表';