你真的搞懂MySQL的字符集了吗？

尹洪亮Kevin 2021-07-30

920

通过本文你可以学习以下内容：

搞懂到底什么是字符集，它和字符、编码有什么关系？
MySQL的数据库字符集、表字符集、字段字符集有什么关系？
MySQL的字符集转换会有什么问题？
MySQL怎样设置数据库服务端和客户端字符集？
MySQL怎样设置数据库字符集、表字符集、字段字符集？

一、基础术语

字符：汉字，英文字母，标点符号，拉丁文等等。
编码：将字符转换成计算机存储的格式，比如，A用65表示。
字符集 Character Set：等于字符+编码，包含一组字符以及对应的编码方式。

二、字符集原理

不同的字符集，规定了不同的字符的编码方式，是一组符号和编码。

例如我们熟知的ASCII字符集，包括的字符有数字、大小写字母、分号、换行之类的符号，编码方式是用一个7bit表示一个字符，例如A的编码是65，b的编码是98。

ASCII（American Standard Code for Information Interchange，美国标准信息交换代码）是基于拉丁字母的一套电脑编码系统，主要用于显示现代英语和其他西欧语言，主要编码表如下图所示。

ASCII只规定了英文字母的编码，非英文语言不能用ASCII编码表示，为此，不同的国家，都为自己的语言做了编码，比如，我们国家，就有GB2312编码。

但每个国家之间的编码不同，也存在着一些跨平台的问题，为此，一些国际化标准组织，就制定了一些国际通用的编码，最常用的就是UTF8了。ASCII只对英文符号和英文字母做了编码，GB2312对英文符号，英文字母，汉字做了编码，UTF8对世界上所有的语言文字做了编码，所以，GB1212的字符包含了ASCII字符，UTF8包含了GB2312字符。

三、MySQL字符集

MySQL目前支持多字符集，并且，支持在不同的字符集之间转换(便于移植和支持多语言)。

MySQL可以设置服务器级字符集、数据库级字符集（图A-1）、数据表级字符集（图A-2）、表列级别字符集（图A-3），实际上，最终使用字符集的地方是存储字符的列。

其中服务器级字符集、数据库级字符集、数据表级字符集都是为列的字符集做默认选项的。

图A-1 数据库级字符集

图A-2 数据表级字符集

图 A-3 数据表列级编码

四、字符集的转换问题

如果小字集转换成大字符集，不会丢失数据，但大字集，转换成小字集，可能会丢失数据。

比如，UTF8里有的字符，GB2312不一定有，所以，从UTF8转换到GB2312可能会丢失一些字符。

五、字符集设置

可以通过Navicat工具可视化的设置数据库、表、字段的字符集，不再介绍，可参考上方的图A1~A3，下面重点介绍通过文件配置和SQL语句进行字符集设置。

5.1数据库服务端和客户端字符集

1、先停止mysql服务，然后修改mysql安装目录下的my.ini或my.cnf文件。

2、在[mysqld]标签(没有则新建)下增加：character-set-server=utf8

3、在[client]标签(没有则新建)下增加：default-character-set=utf8

4、启动mysql服务即可

5、可通过show VARIABLES like '%char%';查看编码设置，如图A-4所示。

图A-4 数据库字符集设置

6、这里可以看到字符集有很多设置参数，除了character_set_filesystem=binary以外，其他的都可以一致设置为utf8或utf8mb4。如果发现编码不一致，就可以通过my.ini或my.cnf文件进行设置。

5.2数据库字符集设置

数据库可以在创建时指定字符集、或者修改字符集

-- 语法
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];


-- 示例: 创建数据库
create database if not exists dbtest character set utf8;
-- 示例：修改数据库
ALTER DATABASE dbtest CHARACTER SET 'utf8';

5.3数据表字符集设置

可以在创建表时指定默认字符集，也可以创建后修改表的字符集

-- 创建表时：DEFAULT CHARSET=utf8mb4 设置字符集
CREATE TABLE `t_employee` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `code` varchar(10) NOT NULL COMMENT '员工编码',
  `name` varchar(10) NOT NULL COMMENT '员工姓名',
  `age` int(10) unsigned DEFAULT NULL COMMENT '年龄',
  `sex` int(10) unsigned DEFAULT NULL COMMENT '性别',
  `cert_type` int(10) unsigned DEFAULT NULL COMMENT '证件类型',
  `cert_no` varchar(20) DEFAULT NULL COMMENT '证件号',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `income_date` date DEFAULT NULL COMMENT '入职日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `cert_type` (`cert_type`,`cert_no`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COMMENT='员工表';


-- 修改表的字符集
ALTER TABLE `dbtest`.`t_employee` CHARACTER SET = utf8mb4;

5.4字段字符集设置

可以在创建表时直接指定字段的字符集，也可以在创建表之后修改字段的字符集。

-- 创建表时：CHARACTER SET utf8mb4指定字段字符集
CREATE TABLE `t_employee` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `code` varchar(10) NOT NULL COMMENT '员工编码',
  `name` varchar(10) NOT NULL COMMENT '员工姓名',
  `age` int(10) unsigned DEFAULT NULL COMMENT '年龄',
  `sex` int(10) unsigned DEFAULT NULL COMMENT '性别',
  `cert_type` int(10) unsigned DEFAULT NULL COMMENT '证件类型',
  `cert_no` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '证件号',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `income_date` date DEFAULT NULL COMMENT '入职日期',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `cert_type` (`cert_type`,`cert_no`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='员工表';


-- 修改字段的字符集：CHARACTER SET utf8mb4
ALTER TABLE `dbtest`.`t_employee` 
MODIFY COLUMN `cert_no` varchar(20) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT '证件号' AFTER `cert_type`;

点“在看”变好看噢

mysql

文章转载自尹洪亮Kevin，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。