MySQL - SET NAMES 語句

---

---

MySQL 的字元集是一組字串中允許使用的合法字元。MySQL 提供了多個字元集，您可以使用 **SHOW CHARACTER SET** 語句檢視所有可用字元集的列表。

SHOW CHARACTER SET\G;

輸出

以下是上述查詢的輸出 -

************* 1. row *************
          Charset: armscii8
      Description: ARMSCII-8 Armenian
Default collation: armscii8_general_ci
           Maxlen: 1
************* 2. row *************
          Charset: ascii
      Description: US ASCII
Default collation: ascii_general_ci
           Maxlen: 1
************* 3. row *************
          Charset: big5
      Description: Big5 Traditional Chinese
Default collation: big5_chinese_ci
           Maxlen: 2
************* 4. row *************
          Charset: binary
      Description: Binary pseudo charset
Default collation: binary
           Maxlen: 1
************* 5. row *************
          Charset: cp1250
      Description: Windows Central European
Default collation: cp1250_general_ci
           Maxlen: 1
************* 6. row *************
          Charset: cp1251
      Description: Windows Cyrillic
Default collation: cp1251_general_ci
           Maxlen: 1
************* 7. row *************
          Charset: cp1256
      Description: Windows Arabic
Default collation: cp1256_general_ci
           Maxlen: 1
************* 8. row *************
          Charset: cp1257
      Description: Windows Baltic
Default collation: cp1257_general_ci
           Maxlen: 1
************* 9. row *************
          Charset: cp850
      Description: DOS West European
Default collation: cp850_general_ci
           Maxlen: 1
************* 10. row *************
          Charset: cp852
      Description: DOS Central European
Default collation: cp852_general_ci
           Maxlen: 1
************* 11. row *************
          Charset: cp866
      Description: DOS Russian
Default collation: cp866_general_ci
           Maxlen: 1
************* 12. row *************
          Charset: cp932
      Description: SJIS for Windows Japanese
Default collation: cp932_japanese_ci
           Maxlen: 2
************* 13. row *************
          Charset: dec8
      Description: DEC West European
Default collation: dec8_swedish_ci
           Maxlen: 1
************* 14. row *************
          Charset: eucjpms
      Description: UJIS for Windows Japanese
Default collation: eucjpms_japanese_ci
           Maxlen: 3
************* 15. row *************
          Charset: euckr
      Description: EUC-KR Korean
Default collation: euckr_korean_ci
           Maxlen: 2
************* 16. row *************
          Charset: gb18030
      Description: China National Standard GB18030
Default collation: gb18030_chinese_ci
           Maxlen: 4
************* 17. row *************
          Charset: gb2312
      Description: GB2312 Simplified Chinese
Default collation: gb2312_chinese_ci
           Maxlen: 2
************* 18. row *************
          Charset: gbk
      Description: GBK Simplified Chinese
Default collation: gbk_chinese_ci
           Maxlen: 2
************* 19. row *************
          Charset: geostd8
      Description: GEOSTD8 Georgian
Default collation: geostd8_general_ci
           Maxlen: 1
************* 20. row *************
          Charset: greek
      Description: ISO 8859-7 Greek
Default collation: greek_general_ci
           Maxlen: 1
************* 21. row *************
          Charset: hebrew
      Description: ISO 8859-8 Hebrew
Default collation: hebrew_general_ci
           Maxlen: 1
************* 22. row *************
          Charset: hp8
      Description: HP West European
Default collation: hp8_english_ci
           Maxlen: 1
************* 23. row *************
          Charset: keybcs2
      Description: DOS Kamenicky Czech-Slovak
Default collation: keybcs2_general_ci
           Maxlen: 1
************* 24. row *************
          Charset: koi8r
      Description: KOI8-R Relcom Russian
Default collation: koi8r_general_ci
           Maxlen: 1
************* 25. row *************
          Charset: koi8u
      Description: KOI8-U Ukrainian
Default collation: koi8u_general_ci
           Maxlen: 1
************* 26. row *************
          Charset: latin1
      Description: cp1252 West European
Default collation: latin1_swedish_ci
           Maxlen: 1
************* 27. row *************
          Charset: latin2
      Description: ISO 8859-2 Central European
Default collation: latin2_general_ci
           Maxlen: 1
************* 28. row *************
          Charset: latin5
      Description: ISO 8859-9 Turkish
Default collation: latin5_turkish_ci
           Maxlen: 1
************* 29. row *************
          Charset: latin7
      Description: ISO 8859-13 Baltic
Default collation: latin7_general_ci
           Maxlen: 1
************* 30. row *************
          Charset: macce
      Description: Mac Central European
Default collation: macce_general_ci
           Maxlen: 1
************* 31. row *************
          Charset: macroman
      Description: Mac West European
Default collation: macroman_general_ci
           Maxlen: 1
************* 32. row *************
          Charset: sjis
      Description: Shift-JIS Japanese
Default collation: sjis_japanese_ci
           Maxlen: 2
************* 33. row *************
          Charset: swe7
      Description: 7bit Swedish
Default collation: swe7_swedish_ci
           Maxlen: 1
************* 34. row *************
          Charset: tis620
      Description: TIS620 Thai
Default collation: tis620_thai_ci
           Maxlen: 1
************* 35. row *************
          Charset: ucs2
      Description: UCS-2 Unicode
Default collation: ucs2_general_ci
           Maxlen: 2
************* 36. row *************
          Charset: ujis
      Description: EUC-JP Japanese
Default collation: ujis_japanese_ci
           Maxlen: 3
************* 37. row *************
          Charset: utf16
      Description: UTF-16 Unicode
Default collation: utf16_general_ci
           Maxlen: 4
************* 38. row *************
          Charset: utf16le
      Description: UTF-16LE Unicode
Default collation: utf16le_general_ci
           Maxlen: 4
************* 39. row *************
          Charset: utf32
      Description: UTF-32 Unicode
Default collation: utf32_general_ci
           Maxlen: 4
************* 40. row *************
          Charset: utf8mb3
      Description: UTF-8 Unicode
Default collation: utf8mb3_general_ci
           Maxlen: 3
************* 41. row *************
          Charset: utf8mb4
      Description: UTF-8 Unicode
Default collation: utf8mb4_0900_ai_ci
           Maxlen: 4

您可以使用以下查詢獲取預設字元集的名稱 -

SELECT default_character_set_name FROM information_schema.SCHEMATA S 
WHERE schema_name = "test";

輸出

上述查詢產生以下輸出 -

DEFAULT_CHARACTER_SET_NAME
utf8mb4

SET NAMES 語句

MySQL SET NAMES 語句用於為 character_set_client、character_set_connection 和 character_set_results 會話系統變數設定值。

語法

以下是 MySQL SET NAMES 語句的語法 -

SET NAMES {'charset_name'
[COLLATE 'collation_name'] | DEFAULT}

其中，'charset_name' 是字元集的名稱。

示例

以下查詢將 character_set_client、character_set_connection 和 character_set_results 變數的值設定為 'utf8'。 -

SET NAMES 'utf8';

驗證

您可以使用 show variables like 語句驗證 character_set_client 和 character_set_results 變數的當前值，如下所示 -

show variables like "character_set_client";

輸出

執行上述 mysql 查詢後，它會產生如下所示的輸出 -

Variable_name	Value
character_set_client	utf8

show variables like "character_set_results";

輸出

以下是上述查詢的輸出 -

Variable_name	Value
character_set_results	utf8

show variables like "character_set_connection";

輸出

上述 mysql 查詢產生以下輸出 -

Variable_name	Value
character_set_connection	utf8

collate 子句

MySQL 校對是一組用於驗證/比較字元集的每個字元的規則。每個字元集至少有一個校對（它可以有多個）和一個預設校對。沒有兩個字元集可以具有相同的校對。**SHOW CHARACTER SET 語句**。為每個字元集提供預設校對。

您可以使用 COLLATE 子句顯式地為字元集指定校對。

示例

以下查詢將字元集的校對設定為 ascii_bin -

SET NAMES ascii COLLATE ascii_bin;

您可以使用 SHOW COLLATION 語句獲取字元集的所有允許的校對，如下所示 -

SHOW COLLATION WHERE Charset = 'ascii';

輸出

執行上述查詢後，它將產生以下輸出 -

Collation	Charset	Id	Default	Compiled	Sortlen	Pad_attribute
ascii_bin	ascii	65		Yes	1	PAD SPACE
ascii_general_ci	ascii	11	Yes	Yes	1	PAD SPACE

DEFAULT 子句

您可以使用 DEFAULT 子句將校對和對映設定為預設值。

SET NAMES DEFAULT;