mysql set character set

As a powerful open source database, MySQL is widely used in most applications in all walks of life. Character set setting is a very important issue when using MySQL. This article will introduce how to set the MySQL character set to help developers use the MySQL database correctly.

1. The concept of character set

Character set is a specification about the mapping relationship between codes and characters. It specifies the encoding method of computer characters. Character sets may have different settings in different database systems, and different character sets have different characteristics and advantages. For example, when processing Chinese characters, GB2312/GBK/GB18030 has advantages over Latin1/Latin7.

In MySQL, character set refers to the encoding of character data in databases, tables, and columns. In layman's terms, a character set refers to the way used to store and process characters and strings.

The character sets supported in MySQL mainly include the following: ASCII, GBK, GB2312, UTF-8, UTF-16 and ISO-8859, etc. Among them, UTF-8 is the most commonly used character set in MySQL because It supports multiple languages, has flexible encoding formats, saves space and other benefits.

2. How to set the MySQL character set

1. Query the character set supported by MySQL

In the MySQL database, you can query the character set supported by the database through commands Character set, the method is as follows:

SHOW CHARACTER SET;

This command can list the various character sets available for MySQL, for example:

##dec8DEC West Europeandec8_swedish_ci1##cp850# #hp8HP West Europeanhp8_english_ci1koi8rKOI8-R Relcom Russiankoi8r_general_ci1latin1iso-8859-1 West Europeanlatin1_swedish_ci1iso-8859-2 Central European7bit Swedish##asciiUS ASCIIascii_general_ci1ujis_japanese_cisjis_japanese_cihebrew_general_ci##tis620TIS620 Thaitis620_thai_ci121##gb2312GB2312 Simplified Chinesegb2312_chinese_ci2##keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1macceMac Central Europeanmacce_general_ci1macromanMac West Europeanmacroman_general_ci1cp852DOS Central Europeancp852_general_ci1latin7iso-8859-13 Balticlatin7_general_ci1##utf8mb4cp1251##utf16UTF-16 Unicodeutf16_general_ci4UTF-16LE UnicodeWindows ArabicWindows BalticUTF-32 UnicodeBinary pseudo charsetGEOSTD8 Georgian##cp932SJIS for Windows Japanesecp932_japanese_ci2eucjpms_japanese_ci

Charset	Description	Default collation	Maxlen
big5	Big5 Traditional Chinese	big5_chinese_ci	2
DOS West European	cp850_general_ci	1
			##latin2
latin2_general_ci	1		swe7
swe7_swedish_ci	1
		##ujis	EUC-JP Japanese
3		sjis	Shift-JIS Japanese
2		hebrew	iso-8859-8 Hebrew
1
	##euckr	EUC-KR Korean	euckr_korean_ci
	koi8u	KOI8-U Ukrainian	koi8u_general_ci
##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
UTF-8 Unicode	utf8mb4_general_ci	4
Windows Cyrillic	cp1251_general_ci	1
			##utf16le
utf16le_general_ci	4		cp1256
cp1256_general_ci	1		cp1257
cp1257_general_ci	1		utf32
utf32_general_ci	4		binary
binary	1		geostd8
geostd8_general_ci	1
		##eucjpms	UJIS for Windows Japanese
3		Set the character set of the database When we create a new MySQL database, we can set the default character set of the database, so that the character set of all tables in the database will be used the character set. The specific steps are as follows: 2.1 First check the character sets supported by MySQL mysql> SHOW CHARACTER SET; 2.2 When creating a new database, add a character set setting Define CREATE DATABASE new_db CHARACTER SET utf8; or CREATE DATABASE new_db DEFAULT CHARACTER SET utf8; where utf8 is one of the commonly used character sets. Set the character set of the MySQL table In order for the fields of the table to store and display data correctly, we need to set the character set for the table. In MySQL, the character set of a table can be set when creating the table. If the table has been created, you can also modify the table through the Alter command. 3.1 Set the character set when creating the table CREATE TABLE new_table ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Among them, ENGINE=InnoDB is used to set the storage engine of the table, and CHARSET=utf8 is used to set the default character set of the table. . 3.2 Modify the character set of the table ALTER TABLE old_table CONVERT TO CHARACTER SET utf8; or ALTER TABLE old_table MODIFY COLUMN name VARCHAR(50) CHARACTER SET utf8; Among them, the CONVERT TO command is used to modify the default character set of the table, and the MODIFY COLUMN command is used to modify the character set of a column in the table. Set the character set for MySQL connection When connecting to the MySQL server, you can also set the character set for the operation. This character set setting is often called the "client character set" and refers to the character set of data transmitted in the client. 4.1 View the character set of the current connection mysql> SELECT @@character_set_connection; 4.2 Modify the character set of the connection SET character_set_connection = utf8; or mysql --default-character-set=utf8 -u root -p Among them, the SET command can modify the default character set of the connection, --default-character The -set command can specify the client character set. Other character set settings In some cases, you may need to open other character set settings of MySQL to handle some rare data storage and conversion Scenes. At this time, you may need to modify the MySQL configuration file - my.cnf file. Modifying this file may require administrator rights. The modification method is as follows: 5.1 Find the my.cnf file In Linux, the my.cnf file is usually stored in the /etc/my.cnf or /etc/mysql/my.cnf directory . In Windows, the my.cnf file is usually stored in the installation directory of the MySQL database. 5.2 Modify the my.cnf file Add the following statement in the my.cnf file: [mysqld] character_set_server=utf8 init_connect='SET NAMES utf8' Among them, the character_set_server command is used to set the listed character set, and the init_connect command is used to automatically set the character set when creating a connection. Summary MySQL is a very popular open source database, and its character set setting plays a very important role. Correctly setting the character set of MySQL ensures normal reading, writing and storage of data. In this article, we introduce the basic concepts of MySQL character sets, how to set character sets when creating databases, tables, and connections, and how to modify the my.cnf file to enable more character set settings. This knowledge can help developers better use MySQL.

The above is the detailed content of mysql set character set. For more information, please follow other related articles on the PHP Chinese website!