MySQL – Character Set
”;
The MySQL Character Set
The MySQL Character set is used to determine the set of permissible characters within a string. It allows for the storage of data in various character encodings. By default, MySQL uses the “utf8mb4” character set.
These character sets provides several functionalities −
- Storage Variety − MySQL allows the storage of strings in various character sets.
- String Comparison − Collations help in comparing strings based on the chosen character set.
- Mixed Character Sets − It is possible to combine strings with different character sets or collations within the same server, database, or table.
- Specifying Character Set and Collation − You can define the character set and collation at different levels of the database structure.
The MySQL Show Character-Set
You can use the MySQL SHOW CHARACTER SET statement to view the list of all the available character sets.
Syntax
Following is the syntax of the SHOW CHARACTER SET statement −
SHOW CHARACTER SET [LIKE ''pattern'' | WHERE expr]
Example
The following query retrieves a list of available character sets, along with their descriptions, default collations, and maximum lengths in a MySQL database −
SHOW CHARACTER SET;
Output
Following is the output obtained −
| Charset | Description | Default collation | Maxlen |
|---|---|---|---|
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
The MySQL Set Character-set
The MySQL SET CHARACTER SET Statement is used to assign a value to the character set attribute. It maps all the strings between the server and the current client with the specified mapping set. This statement changes values of the “character_set_client” and “character_set_results” variables.
Syntax
Following is the syntax of the MySQL SET CHARACTER SET Statement −
SET {CHARACTER SET | CHARSET} {''charset_name'' | DEFAULT}
Where, ”charset_name” is the name of the character set.
Example
The query given below sets the character set to “macroman” −
SET CHARACTER SET macroman;
Output
The output produced is as shown below −
Query OK, 0 rows affected (0.10 sec)
Verification
You can verify the character set values using the SHOW VARIABLES LIKE statement as shown below −
SHOW VARIABLES LIKE "character_set_client";
Following is the output obtained −
| Variable_name | Value |
|---|---|
| character_set_client | macroman |
Now verifying the current value of the “character_set_results” variable −
SHOW VARIABLES LIKE "character_set_results";
The result produced is as shown below −
| Variable_name | Value |
|---|---|
| character_set_results | macroman |
”;