MySQL character encoding system (i)--Data storage encoding

The installation of MySQL many times, each time will be entangled in the database character encoding configuration, so I decided this time to completely clarify it.

MySQL's character encoding structure is relatively thin, it is divided into two parts: data storage encoding and transmission code. This paper discusses the data storage coding section, the transmission code is discussed in the next MySQL character encoding system (ii)-Data transmission coding.

Coding hierarchy

The character encoding configuration of the data store is what character encoding is used by default for data stored in the specified database. The default character encoding settings are divided into four levels: server-level, database-level, data-table-level, and column-level. That is, you can set a default character encoding for the server, set a different default encoding for each database in the server, set a different default encoding for each data table in the same database, and set a different default encoding for each column in the same data table.

Logical structure of MySQL database server

So how does this four-level coding setup work? If you do not specify a character encoding when you create a new database, the server's encoding is set by default, and if you do not specify any encoding when you create a new data table, the database is encoded by default, and if you add new columns to the datasheet or create new data tables without specifying the encoding of some columns, they are set to the data table encoding by default Note that there are four levels of encoding as "default", and the encoding that is directly specified when the user creates the database, table, or add column is the highest priority.

On the other hand, changing these four-level encodings directly does not change the current encoding of all of their underlying objects. For example, if you modify only the server level, then the default encoding for all existing databases will be the same, the data table, table columns, and the character encoding of each row of data records is the same, but if you create a new database and do not specify its default encoding, its default encoding is set to the default encoding of the server , and even if all four levels of encoding are modified, the character field of each existing record in the datasheet is still stored in the original encoding, but if a new record is inserted into the datasheet, the database stores the individual character fields of the record according to the default encoding of the current columns of the data table.

Setup method

Modify the SQL statements for the following levels of server encoding:

ALTER {DATABASE | SCHEMA} [db_name] [default] CHARACTER Set [=] Charset_namealter TABLE dbl_name [DEFAULT] CHARACTER SET [=] Charset_namealt ER 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 the column properties, which is the same as specifying column field properties when creating a new data table. So if you just want to modify the column character encoding, you must write all the definition adornments that were used to create the column.

Modifying the server default encoding can be implemented directly by modifying the variable character_set_server at run time, but it is temporary and will be restored automatically when the client shuts down and restarts. To permanently change the server default encoding you need to set the value of the variable in the "[Mysqld]" area of the My.ini or my.cnf configuration file, and then restart the server:

[Mysqld]character_set_server=charset_name