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;
