MySQL 字符集utf8、utf8mb3、utf8mb4

首先想要了解MySQL的字符集，就需要去官方文檔看看字符集是如何介紹的。英語不錯的話，看官方文檔應該是沒問題。在搜索框里搜一下就可以找到相關的解釋。我就在這里整理一下，以便后期查看。字符集在官方文檔下面這一章節：
Chapter 10 Character Sets, Collations, Unicode

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

一、字符集設置

MySQL數據庫可以做到：
1、使用多種字符集存儲字符串。
2、使用多種排序規則比較字符串。
3、在同一服務器、同一數據庫、甚至同一表中混合具有不同字符集或排序規則的字符串。
4、在任何級別啟用字符集和排序規則的規范。

MySQL可以設置如下40種字符：
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 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)

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

這里提到：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:

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個字符。在轉換后不能滿足那么就需要換一個列來索引。以下是通過壓縮方式使索引更多的字節。

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也需要相應的改變。