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