MySQL 字符集utf8、utf8mb3、utf8mb4

本文主要是介绍MySQL 字符集utf8、utf8mb3、utf8mb4，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

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

一、字符集设置

40 rows in set (0.00 sec)

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语句强制服务器执行字符集转换。客户端程序可以在接收到来自服务器的结果后执行转换。客户端执行转换更为有效，但此选项并不总是适用于所有客户端。

SET NAMES 'utf8';

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

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

设置了_ci，顾名思义显式不区分大小写，隐式重音不敏感。

设置了_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

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]

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.

查看你数据库这两个参数设置：
USE db_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:
CREATE TABLE tbl_name (column_list) [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]
ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_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) [CHARACTER SET charset_name] [COLLATE collation_name]
col_name {ENUM | SET} (val_list) [CHARACTER SET charset_name] [COLLATE collation_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' [COLLATE collation_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) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)

As are these:
VARCHAR(10) CHARACTER SET utf8
NATIONAL VARCHAR(10)
NVARCHAR(10)
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(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 [COLLATE collation_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 0x4D7953514C COLLATE utf8_danish_ci;

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

八、Unicode Support

BMP characters have these characteristics:

Their code point values are between 0 and 65535 (or U+0000 and U+FFFF).
They can be encoded in a variable-length encoding using 8, 16, or 24 bits (1 to 3 bytes).
They can be encoded in a fixed-length encoding using 16 bits (2 bytes).
They are sufficient for almost all characters in major languages.

Supplementary characters lie outside the BMP:

Their code point values are between U+10000 and U+10FFFF).
Unicode support for supplementary characters requires character sets that have a range outside BMP characters and therefore take more space than BMP characters (up to 4 bytes per character).

The UTF-8 (Unicode Transformation Format with 8-bit units) method for encoding Unicode data is implemented according to RFC 3629, which describes encoding sequences that take from one to four bytes. The idea of UTF-8 is that various Unicode characters are encoded using byte sequences of different lengths:

Basic Latin letters, digits, and punctuation signs use one byte.
Most European and Middle East script letters fit into a 2-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others.
Korean, Chinese, and Japanese ideographs use 3-byte or 4-byte sequences.

MySQL supports these Unicode character sets:

utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.
utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character.
utf8: An alias for utf8mb3.
ucs2: The UCS-2 encoding of the Unicode character set using two bytes per character.
utf16: The UTF-16 encoding for the Unicode character set using two or four bytes per character. Like ucs2 but with an extension for supplementary characters.
utf16le: The UTF-16LE encoding for the Unicode character set. Like utf16 but little-endian rather than big-endian.
utf32: The UTF-32 encoding for the Unicode character set using four bytes per character.

下面这张表统计字符集字节数：

Character Set	Supported Characters	Required Storage Per Character
`utf8mb3`, `utf8`	BMP only	1, 2, or 3 bytes
`ucs2`	BMP only	2 bytes
`utf8mb4`	BMP and supplementary	1, 2, 3, or 4 bytes
`utf16`	BMP and supplementary	2 or 4 bytes
`utf16le`	BMP and supplementary	2 or 4 bytes
`utf32`	BMP and supplementary	4 bytes

九、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.

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的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！