MySQL 字符集utf8、utf8mb3、utf8mb4

发布时间：2020-08-07 04:32:08 作者：perfeader
来源：ITPUB博客阅读：808

首先想要了解MySQL的字符集，就需要去官方文档看看字符集是如何介绍的。英语不错的话，看官方文档应该是没问题。在搜索框里搜一下就可以找到相关的解释。我就在这里整理一下，以便后期查看。字符集在官方文档下面这一章节：
Chapter 10 Character Sets, Collations, Unicode

https://dev.mysql.com/doc/refman/5.6/en/charset.html

一、字符集设置

String expressions have a repertoire attribute, which can have two values:

ASCII: The expression can contain only characters in the Unicode range U+0000 to U+007F.
UNICODE: The expression can contain characters in the Unicode range U+0000 to U+10FFFF. This includes characters in the Basic Multilingual Plane (BMP) range (U+0000 to U+FFFF) and supplementary characters outside the BMP range (U+10000 to U+10FFFF).

这里提到：Basic Multilingual Plane (BMP) 和 supplementary characters
Basic Multilingual Plane (BMP)：基本多文种平面
Supplementary Multilingual Plane(SMP):多文种补充平面
BMP就已经包含常用字符，而SMP只是一些不常用的字符，代码点（字符）。如Emoji头像的符号，扑克牌的符号等等。
关于BMP与SMP详细可以查看wiki上的解释：https://en.wikipedia.org/wiki/Plane_(Unicode)

系统默认设置元数据表的字符集为utf8，是通过参数character_set_system设置。character_set_results这个参数默认是utf8，当查询表数据返回给客户端，这个参数是控制返回的结构数据的字符集。如果希望服务器将元数据结果传递回不同的字符集，请使用SET NAMES语句强制服务器执行字符集转换。客户端程序可以在接收到来自服务器的结果后执行转换。客户端执行转换更为有效，但此选项并不总是适用于所有客户端。

SETNAMES'utf8';

There are default settings for character sets and collations at four levels: server, database, table, and column.

Suffix	Meaning
_ai	Accent insensitive 重音不敏感
_as	Accent sensitive 重音敏感
_ci	Case insensitive 不区分大小写
_cs	case-sensitive 区分大小写
_bin	Binary 二进制

设置了_ci，顾名思义_ai也是包含的，显式不区分大小写，隐式重音不敏感。
设置了_cs，顾名思义_as也是包含的，显式区分大小写，隐式重音敏感。

设置MySQL server character：

character-set-server

方法一：
mysqld
   mysqld --character-set-server=latin1
mysqld --character-set-server=latin1 \
  --collation-server=latin1_swedish_ci

方法二：
cmake .-DDEFAULT_CHARSET=latin1
或cmake .-DDEFAULT_CHARSET=latin1 \
  -DDEFAULT_COLLATION=latin1_german1_ci

The current server character set and collation can be determined from the values of the character_set_server and collation_server system variables. These variables can be changed at runtime.

Database Character Set and Collation

CREATEDATABASEdb_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name]
ALTERDATABASEdb_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name]

The keyword SCHEMA can be used instead of DATABASE.

All database options are stored in a text file named db.opt that can be found in the database directory.

The CHARACTER SET and COLLATE clauses make it possible to create databases with different character sets and collations on the same MySQL server.

查看你数据库这两个参数设置：
USEdb_name;
SELECT@@character_set_database,@@collation_database;

Table Character Set and Collation

The CREATE TABLE and ALTER TABLE statements have optional clauses for specifying the table character set and collation:
CREATETABLEtbl_name(column_list)[[DEFAULT]CHARACTERSETcharset_name][COLLATEcollation_name]]
ALTERTABLEtbl_name[[DEFAULT]CHARACTERSETcharset_name][COLLATEcollation_name]

Column Character Set and Collation

Every “character” column (that is, a column of type CHAR, VARCHAR, or TEXT) has a column character set and a column collation. Column definition syntax for CREATE TABLE and ALTER TABLE has optional clauses for specifying the column character set and collation:

col_name {CHAR|VARCHAR|TEXT} (col_length)[CHARACTERSETcharset_name][COLLATEcollation_name]

col_name {ENUM|SET} (val_list)[CHARACTERSETcharset_name][COLLATEcollation_name]

Character String Literal Character Set and Collation

For the simple statement SELECT 'string', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables.

A character string literal may have an optional character set introducer and COLLATE clause, to designate it as a string that uses a particular character set and collation:
[_charset_name]'string'[COLLATEcollation_name]

Examples:
SELECT'abc';
SELECT _latin1'abc';
SELECT _binary'abc';
SELECT _utf8'abc'COLLATE utf8_danish_ci;

The National Character Set

Standard SQL defines NCHAR or NATIONAL CHAR as a way to indicate that a CHAR column should use some predefined character set. MySQL usesutf8 as this predefined character set. For example, these data type declarations are equivalent:

CHAR(10)CHARACTERSET utf8
NATIONALCHARACTER(10)
NCHAR(10)

As are these:
VARCHAR(10)CHARACTERSET utf8
NATIONALVARCHAR(10)
NVARCHAR(10)
NCHARVARCHAR(10)
NATIONALCHARACTERVARYING(10)
NATIONALCHARVARYING(10)

Character Set Introducers

A character string literal, hexadecimal literal, or bit-value literal may have an optional character set introducer and COLLATE clause, to designate it as a string that uses a particular character set and collation:

[_charset_name]literal[COLLATEcollation_name]

Character set introducers and the COLLATE clause are implemented according to standard SQL specifications.

Examples:
SELECT'abc';
SELECT _latin1'abc';
SELECT _binary'abc';
SELECT _utf8'abc'COLLATE utf8_danish_ci;

SELECT _latin1 X'4D7953514C'; --16进制
SELECT _utf8 0x4D7953514CCOLLATE utf8_danish_ci;

SELECT _latin1 b'1000001'; --2进制
SELECT _utf8 0b1000001COLLATE utf8_danish_ci;

二、utf8(utf8mb3)与utf8mb4的转换

10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets

The utf8mb3 and utf8mb4 character sets differ as follows:

utf8mb3 supports only characters in the Basic Multilingual Plane (BMP). utf8mb4 additionally supports supplementary characters that lie outside the BMP.
utf8mb3 uses a maximum of three bytes per character. utf8mb4 uses a maximum of four bytes per character.

Note

This discussion refers to the utf8mb3 and utf8mb4 character set names to be explicit about referring to 3-byte and 4-byte UTF-8 character set data. The exception is that in table definitions, utf8 is used because MySQL converts instances of utf8mb3specified in such definitions to utf8, which is an alias for utf8mb3.

utf8mb4与utf8(utf8mb3)转换也是特别好转换的：

1.utf8(utf8mb3)转成utf8mb4可以存储supplementary characters；
2.utf8(utf8mb3)转成utf8mb4可能会增加数据存储空间；
3.对于BMP character字符，utf8(utf8mb3)转成utf8mb4相同的代码值、相同的编码、相同的长度，不会有变化。
4.对于supplementary character字符，utf8mb4会以4字节存储，由于utf8mb3无法存储supplementary character字符，因而在字符集转换过程中，不用担心字符无法转换的问题。
5.表结构在转换过程中需要调整：utf8(utf8mb3)字符集可变长度字符数据类型（VARCHAR和text类型）设定的表中列的字段长度，utf8mb4中将会存储更少的字符。对于所有字符数据类型（CHAR、VARCHAR和文本类型），UTF8Mb4列最多可被索引的字符数比UTF8Mb3列要少。因此在转换之前，要检查字段类型。防止转换后表，索引存储的数据超出该字段定义长度，字段类型长度可以存储的最大字节数。innodb索引列：最大索引列长度767 bytes，对于utf8mb3就是可以索引255个字符，对于utf8mb4就是可以索引191个字符。在转换后不能满足那么就需要换一个列来索引。以下是通过压缩方式使索引更多的字节。

Note

For InnoDB tables that use COMPRESSED or DYNAMIC row format, you can enable the innodb_large_prefix option to permit index key prefixes longer than 767 bytes (up to 3072 bytes). Creating such tables also requires the option valuesinnodb_file_format=barracuda and innodb_file_per_table=true.) In this case, enabling the innodb_large_prefixoption enables you to index a maximum of 1024 or 768 characters for utf8mb3 or utf8mb4 columns, respectively. For related information, see Section 14.8.1.7, “Limits on InnoDB Tables”.

The preceding types of changes are most likely to be required only if you have very long columns or indexes. Otherwise, you should be able to convert your tables from utf8mb3 to utf8mb4 without problems, using ALTER TABLE as described previously.

6.应用于MySQL server 字符集也需要一一对应。
7.master 实例改变字符集，那么slave也需要相应的改变。

MySQL 字符集utf8、utf8mb3、utf8mb4

一、字符集设置

Database Character Set and Collation

Table Character Set and Collation

Column Character Set and Collation

Character String Literal Character Set and Collation

The National Character Set

Character Set Introducers

二、utf8(utf8mb3)与utf8mb4的转换

10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets

相关阅读