MySQL字符集字符序
在MySQL的使用过程中,了解字符集、字符序的概念,以及不同设置对数据存储、比较的影响非常重要。
不少同学在日常工作中遇到的“乱码”问题,很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。
下面介绍下如下内容:
- 字符集、字符序的基本概念及联系
- MySQL支持的字符集、字符序设置级,各设置级别之间的联系
- server、database、table、column级字符集、字符序的查看及设置
- 应该何时设置字符集、字符序
字符集、字符序的概念与联系
在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。
MySQL提供了不同级别的设置,包括server级、database级、table级、column级,可以提供非常精准的设置。
什么是字符集、字符序?简单的来说:
- 字符集(character set):定义了字符以及字符的编码。
- 字符序(collation):定义了字符的比较规则。
举个例子:
有四个字符:A、B、a、b,这四个字符的编码分别是A = 0, B = 1, a = 2, b = 3。这里的字符 + 编码就构成了字符集(character set)。
如果我们想比较两个字符的大小呢?比如A、B,或者a、b,最直观的比较方式是采用它们的编码,比如因为0 < 1,所以 A < B。
另外,对于A、a,虽然它们编码不同,但我们觉得大小写字符应该是相等的,也就是说 A == a。
这上面定义了两条比较规则,这些比较规则的集合就是collation。
- 同样是大写字符、小写字符,则比较他们的编码大小;
- 如果两个字符为大小写关系,则它们相等。
MySQL支持的字符集、字符序
MySQL支持多种字符集 与 字符序。
- 一个字符集对应至少一种字符序(一般是1对多)。
- 两个不同的字符集不能有相同的字符序。
- 每个字符集都有默认的字符序。
修改字符集
# 修改/etc/my.cnf文件
# 在[client]下添加下述内容
default-character-set=utf8
# 在[mysqld]下添加下述内容
default-character-set=utf8
保存后重启MySQL服务即可
字符集
查看MYSQL所支持的字符集
# 方式一和二可以加上 WHERE或 LIKE限定条件
# 方式一
show charset;
show charset LIKE "utf8%";
# 方式二
SHOW CHARACTER SET;
SHOW CHARACTER SET WHERE Charset="utf8";
# 方式三
use information_schema;
select * from CHARACTER_SETS;
查看字符集
show variables like '%character%';
查看库的字符集
mysql> show create database testdb\G
查看表的字符集
show table status from 库名 like 表名;
查看表中所有列的字符集
show full columns from 表名;
创建库时指定字符集
create database 库名 default character set=字符集;
创建表时指定字符集
create table 表名 (属性) default character set = 字符集;
创建字段时指定字符集
CREATE TABLE table_name (id int,name char(10) CHARACTER SET utf8 COLLATE utf8_general_ci);
修改全局字符集
- 建立连接使用的编码
set character_set_connection=utf8;
- 数据库的编码
set character_set_database=utf8;
- 结果集的编码
set character_set_results=utf8;
- 数据库服务器的编码
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
修改库的字符集
alter database 库名 default character set 字符集;
修改表的字符集
alter table 表名 convert to character set 字符集;
修改字段的字符集
alter table 表名 modify 字段名 字段属性 character set gbk;
字符序
查看支持的字符序
# 方式一
SHOW COLLATION WHERE Charset = 'utf8';
# 方式二
USE information_schema;
SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";
查看字符序
show variables like 'collation%';
字符序的命名规范
字符序的命名,以其对应的字符集作为前缀,如下所示。比如字符序 utf8_general_ci,标明它是字符集 utf8的字符序,更多规则可以参考 官方文档。
mysql> use information_schema;
Database changed
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8mb4" limit 3;
+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+--------------------+
| utf8mb4 | utf8mb4_general_ci |
| utf8mb4 | utf8mb4_bin |
| utf8mb4 | utf8mb4_unicode_ci |
+--------------------+--------------------+
3 rows in set (0.00 sec)
server的字符集、字符序
当你创建数据库,且没有指定字符集、字符序时,server字符集、server字符序就会作为该数据库的默认字符集、排序规则。
MySQL服务启动时,可通过命令行参数指定。也可以通过配置文件的变量指定。
server默认字符集、字符序:在MySQL编译的时候,通过编译参数指定。
character_set_server、 collation_server分别对应server字符集、server字符序。
- 在MySQL服务启动时,指定server字符集、字符序。如不指定,默认的字符序分别为 latin1、 latin1_swedish_ci
mysqld --character-set-server=latin1 \
--collation-server=latin1_swedish_ci
- 单独指定server字符集,此时,server字符序为 latin1的默认字符序 latin1_swedish_ci。
mysqld --character-set-server=latin1
- 配置文件指定
[client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8
- 运行时修改
# 运行时修改(重启后会失效,如果想要重启后保持不变,需要写进配置文件里)
mysql> SET character_set_server = utf8 ;
- 编译时指定默认字符集、字符序
# character_set_server、 collation_server的默认值,可以在MySQL编译时,通过编译选项指定:
cmake . -DDEFAULT_CHARSET=latin1 \
-DDEFAULT_COLLATION=latin1_german1_ci
database的字符集、字符序
指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库,可以分别指定不同的字符集/字符序。
- 可以在创建、修改数据库的时候,通过 CHARACTER SET、 COLLATE指定数据库的字符集、排序规则。
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
- 修改数据库
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
例子:创建数据库 test_schema,字符集设置为 utf8,此时默认的排序规则为 utf8_general_ci。
CREATE DATABASE test_schema
DEFAULT CHARACTER SET utf8;
- 查看数据库的字符集/字符序,有以下三种方式
- 查看moia5数据库的字符集、排序规则。(需要切换数据库)
mysql> use moia5;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb3 | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)
- 也可以通过下面命令查看 test_schema的字符集、数据库(不需要切换默认数据库)
mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name="moia5";
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| moia5 | utf8 | utf8_general_ci |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)
- 也可以通过查看创建数据库的语句,来查看字符集
mysql> SHOW CREATE DATABASE moia5;
+----------+---------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------------------+
| moia5 | CREATE DATABASE `moia5` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
database字符集、字符序是怎么确定的
- 创建数据库时,指定了 CHARACTER SET或 COLLATE,则以对应的字符集、排序规则为准。
- 创建数据库时,如果没有指定字符集、排序规则,则以 character_set_server、 collation_server为准。