字符集
1、字符集概述
简单的说字符集就是一套文字符号及其编码、比较规则的集合。
2、常见的字符集
| 字符集 | 是否定长 | 编码方式 | 其他说明 |
|---|---|---|---|
| ASCII | 是 | 单字节 7 位编码 | 最早的奠基性字符集 |
| ISO-8859-1/latin1 | 是 | 单字节 8 位编码 | 西欧字符集 |
| GB 2312-80 | 是 | 双字节编码 | 早期标准 |
| GBK | 是 | 双字节编码 | 虽然不是国标，但支持的系统不少 |
| GB 18030 | 否 | 2 字节或 4 字节编码 | 开始有一些支持，但数据库支持的还很少 |
| UTF-32 | 是 | 4 字节编码 | USC-4 原始编码，现在很少用 |
| UCS-2 | 是 | 2 字节编码 | - |
| UTF-16 | 否 | 2 字节或 4 字节 | Java 和 Windows XP/NT 使用 |
| UTF-8 | 否 | 1~4 字节 | 广泛支持的 Unicode 字符集 |
如何选择合适的字符集：
- 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode 字符集。
- 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是 GBK 文字，如果选择 GB 2312-80 为数据库字符集，就很有可能出现某些文字无法导入的情况。
- 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。因为，相对于 UTF-8 而言，GBK 比较 “小”，每个汉字只占 2 个字节，而 UTF-8 汉字编码需要 3 个字节。
- 如果数据库需要做大量的字符运算，如排序、比较等，那么优先选择定长字符集可能会更好；
- 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。
3、MySQL 支持的字符集简介
查看所有可用的字符集命令是 show character set：
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
...
或者查看 information_schema.character_sets，可以显示所有的字符集和该字符集默认的校对规则：
mysql> DESC information_schema.CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(32) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MySQL 的字符集包括字符集（CHARACTER）和排序规则（COLLATION）两个概念。其中字符集用来定义 MySQL 存储字符串的方式，排序规则用来定义比较字符串的方式。字符集和排序规则是一对多的关系，MySQL 支持 30 多种字符集和 70 多种排序规则。
每个字符集至少对应一个排序规则。可以用 “SHOW COLLATION LIKE ‘***’;” 命令或者通过系统表 information_schema.COLLATION 来查看相关字符集的排序规则：
mysql> SHOW COLLATION LIKE 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
...
排序命令规则约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以 _ci（大小写不敏感）、**_cs（大小写敏感）**或 **_bi（二元，即比较是基于字符编码的值而与 language 无关）**结束。
4、MySQL 字符集的设置
MySQL 的字符集和校对规则有 4 个级别的默认设置：服务器级、数据库级、表级和字段级。它们分别在不同的地方设置，作用也不同。
4.1、服务器字符集和校对规则
服务器字符集和校对规则，可以在 MySQL 服务启动的时候确定。
-
可以在 my.cnf 中设置：
[root@mysql ~]# vim /etc/my.cnf [mysqld] ... character-set-server = utf8 -
或者在编译中指定：
shell>> cmake . -DDEFAULT_CHARSET=utf8
可以使用 show variables like ‘character_set_server’ 来查看当前服务器的字符集和校对规则：
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | utf8 |
+----------------------+-------+
1 row in set (0.00 sec)
4.2、数据库字符集和校对规则
数据库字符集和排序规则既可以在创建数据库的时候指定，也可以在创建完成后通过 “alter database” 命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。
设置数据库字符集的规则如下：
- 如果指定了字符集和排序规则，则使用指定的字符集和排序规则；
- 如果指定了字符集没有指定排序规则，则使用指定字符集的默认排序规则；
- 如果指定了排序规则但未指定字符集，则字符集使用与该排序规则关联的字符集；
- 如果没有指定字符集和排序规则，则使用服务器字符集和排序规则作为数据库的字符集和排序规则。
推荐在创建数据库时明确指定字符集和排序规则，避免受到默认值的影响。要显示当前数据库的字符集和排序规则，可以使用 “SHOW VARIABLES LIKE ‘character_set_database’” 和 “SHOW VARIABLES LIKE ‘collation_server’”：
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| character_set_database | utf8 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.00 sec)
4.3、表字符集和排序规则
表的字符集和排序规则在创建表的时候指定，可以通过 alter table 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。
设置表的字符集规则和上面基本类似：
- 如果指定了字符集和排序规则，使用指定的字符集和排序规则；
- 如果指定了字符集没有指定排序规则，使用指定字符集的默认排序规则；
- 如果指定了排序规则但未指定字符集，则字符集使用与该排序规则关联的字符集；
- 如果没有指定字符集和排序规则，使用数据库字符集和排序规则作为表的字符集和排序规则。
推荐在创建表的时候明确指定字符集和排序规则，以避免受到默认值的影响。要显示表的字符集和排序规则，可以使用 SHOW CREATE TABLE 命令查看：
mysql> SHOW CREATE TABLE text2 \G
*************************** 1. row ***************************
Table: text2
Create Table: CREATE TABLE `text2` (
`id` varchar(100) DEFAULT NULL,
`context` blob,
`hash_value` varchar(40) DEFAULT NULL,
KEY `idx_hash_value` (`hash_value`),
KEY `idx_blob` (`context`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
4.4、列字符集和排序规则
MySQL 可以定义列级别的字符集和排序规则，主要针对相同的表不同字段需要使用不同的字符集的情况，应该说一般遇到这种情况的概率很小。
列字符集和排序规则的定义可以在创建表时指定，或者修改表时调整。如果在创建表的时候没有特别指定字符集和排序规则，则默认使用表的字符集和排序规则。
4.5、连接字符集和排序规则
上面 4 种设置方式，确定的是数据保存的字符集和排序规则，对于实际的应用访问来说，还存在客户端和服务器之间交互的字符集和排序规则的设置。
对于客户端和服务器的交互操作，MySQL 提供了 3 个不同的参数：character_set_client、character_set_connection 和 character_set_results，分别代表客户端、连接和返回结果的字符集。通常情况下，这 3 个字符集应该是相同的，才可以确保用户写入的数据可以正确地读出，特别是对于中文字符，不同的写入字符集和返回结果字符集将导致写入的记录补不能正确读出。
通常情况下，不会单个地设置这 3 个参数，可以通过以下命令：
SET NAMES ***;
来设置连接的字符集和排序规则，这个命令可以同时修改这 3 个参数的值。使用这个方法修改连接的字符集和排序规则，需要应用每次连接数据库后都执行这个命令。
另一个简单的方法，是在 my.cnf 中设置以下语句：
[mysqld]
default-character-set=utf8
这样服务启动后，所有连接默认就是使用 utf8 字符集进行连接的，而不需要在程序中再执行 set names 命令。另外，字符串常量的字符集也是由 character_set_connection 参数来指定。
5、字符集的修改步骤
如果在应用开始阶段没有正确地设置字符集，在运行一段时间后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就需要进行字符集的修改。字符集的修改不能直接通过 “alter database character set *” 或者 “alter table tablename set ***” 命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可以生效。
一下模拟的是将 latin1 字符集的数据库修改成 utf8 字符集的数据库的过程：
-
导出表结构；
mysqldump -uroot -p --default-character-set=utf8 -d databasename > createtab.sql其中，–default-character-set=utf8 表示设置以什么字符集连接；-d 表示只导出表结构，不导出数据。
mysql> create database test2; Query OK, 1 row affected (0.00 sec) mysql> create table latin(id int, name varchar(12)); Query OK, 0 rows affected (0.00 sec) mysql> insert into latin values(1, 'sdfsdf'),(2, 'sdafasdfads'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0[root@localhost ~]# mysqldump -uroot -pOpfordream@1996 --default-character-set=utf8 -d test2 > createtab.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. -
手动修改 createtab.sql 中表结构定义中的字符集为新的字符集；
-
确保记录不再改变，导出所有记录；
mysqldump -uroot -p[password] --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql- –quick：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中；
- –extended-insert：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小，重载文件时可以加速插入；
- –no-create-info：不导出每个转储表的 CREATE TABLE 语句；
- –default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存为乱码。
[root@localhost ~]# mysqldump -uroot -pOpfordream@1996 --quick --no-create-info --extended-insert --default-character-set=latin1 test2 > data.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. -
打开 data.sql，将 SET NAMES latin1 修改为 SET NAMES utf8；
[root@localhost ~]# vim data.sql -- MySQL dump 10.13 Distrib 5.7.32, for Linux (x86_64) -- -- Host: localhost Database: test2 -- ------------------------------------------------------ -- Server version 5.7.32 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES latin1 */; <--原先的 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; ...-- MySQL dump 10.13 Distrib 5.7.32, for Linux (x86_64) -- -- Host: localhost Database: test2 -- ------------------------------------------------------ -- Server version 5.7.32 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; <--修改后 -
使用新的字符集创建新的数据库；
create database databasename default charset utf8;mysql> create database test3 default charset utf8; Query OK, 1 row affected (0.00 sec) -
创建表，执行 createtab.sql；
mysql -uroot -p[password] databasename < createtab.sql[root@localhost ~]# mysql -uroot -pOpfordream@1996 test3 < createtab.sql mysql: [Warning] Using a password on the command line interface can be insecure. -
导入数据，执行 data.sql。
mysql -uroot -p[password] databasename < data.sql[root@localhost ~]# mysql -uroot -pOpfordream@1996 test3 < data.sql mysql: [Warning] Using a password on the command line interface can be insecure.mysql> use test3; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_test3 | +-----------------+ | latin | +-----------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE latin \G *************************** 1. row *************************** Table: latin Create Table: CREATE TABLE `latin` ( `id` int(11) DEFAULT NULL, `name` varchar(12) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 <--utf8 字符集 1 row in set (0.00 sec)
282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
