MySQL character encoding system (1)-Data storage encoding _ MySQL

MySQL character encoding system (I)-The data storage encoding has been installed for MySQL many times, and every time it will be entangled in the character encoding configuration of the database, so I decided to thoroughly clarify it this time.

The character encoding structure of MySQL is relatively small. it consists of two parts: Data storage encoding and data transmission encoding. This article discusses data storage encoding, which is discussed in the next MySQL character encoding system (II)-Data transmission encoding.

Encoding level

The character encoding configuration of data storage specifies the default character encoding used for data stored in the database. The default character encoding settings are divided into four levels: Server level, database level, data table level, and Column level. That is to say, you can set a default character encoding for the server, set different default encoding for each database on the server, and set different default encoding for each data table in the same database, set different default codes for each column in the same data table.

Logical structure of the MySQL database server

Then how does the encoding settings of these four layers work? If no character encoding is specified during database creation, the server encoding is set by default. If no encoding is specified during data creation, the database encoding is set by default; if you do not specify the encoding of certain columns when adding new columns to a data table or creating a data table, these columns are set to the data table encoding by default. Note that the four levels of encoding exist as the "default". the encoding directly specified when a user creates a database, table, or adds a column is the highest priority.

On the other hand, directly changing the four levels of encoding does not change the current encoding of all their lower-level objects. For example, if the modification is only at the Server level, the default encoding of all existing databases remains the same, and the character encoding of data tables, columns, and each row of existing data records remains unchanged, however, if you create a new database without specifying its default encoding, its default encoding will be set to the Server's default encoding. even if you modify all four levels of encoding, however, the character fields of each existing record in the data table are still stored according to the original encoding. However, if a new record is inserted into the data table, the database stores the character fields of the record based on the default encoding of the current columns in the data table.

Setting method

Modify the following SQL statements for encoding at the following levels on the Server:

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_nameALTER TABLE dbl_name [DEFAULT] CHARACTER SET [=] charset_nameALTER TABLE dbl_name MODIFY [COLUMN] col_name {CHAR[(length)] | TEXT} CHARACTER SET charset_name

Note that the third modified column character encoding is actually implemented by completely redefining column attributes. The syntax is the same as specifying column field attributes when creating a new data table. Therefore, if you only want to modify the column character encoding, you must complete all the definition modifications used when creating the column.

You can directly modify the character_set_server variable during the runtime to modify the default Server encoding. However, this is temporary and will be automatically restored after the client is shut down and restarted. To change the default Server encoding permanently, set the variable value in the [mysqld] area of my. ini or my. cnf configuration file, and then restart the Server:

[mysqld]character_set_server=charset_name