Research on MySQL coding problem

Occupy a pit.

The error was made when inserting Chinese into the MySQL database that was built on this machine today.

Use show variables like ' char% '; And show variables like ' collation% '; Many items were found to be Latin. Aware of the problem with the configuration file.

To find the configuration file, unexpectedly did not find the configuration file (remember the previously configured, is it deleted?) ）。 Then see mysql.server This script, found that the MY.CNF configuration file can be placed in addition to/etc can also be placed in the user's home directory, but also can be placed in the location of the installation/usr/local/mysql. For the catalog to be structured, it was decided to place it in the installation location.

Add the configuration file to the following:

 [ client  ]  character-set-client  =utf8mb4  [ mysqld  Span style= "color: #800000; Font-weight:bold; " >]  character-set-server  = Utf8mb4collation-server  =utf8mb4_unicode_ci  [ mysql   " Span style= "color: #000000;" >default-character-set  =utf8mb4 

This content was originally configured when the time from the StackOverflow found, slightly modified a bit. The location of the problem in StackOverflow (https://stackoverflow.com/questions/3513773/ CHANGE-MYSQL-DEFAULT-CHARACTER-SET-TO-UTF-8-IN-MY-CNF), is the answer of a person who joins the emoji in a configuration file.

For the time being, don't go through the MySQL documentation to see what these configuration items represent.

Here's a weird behavior:

After I used the MySQL command to connect to the database, using the two show statements mentioned above, I found that the configuration was in effect. But when I try to insert Chinese, I still get an error. Looking back at the configuration using the show command, it turns out that character_set_database and collation_database have become Latin. The same situation has been tried several times.

It suddenly occurred to me that the database was also coded, using MySQL workbench to view the corresponding table encoding, sure enough is Latin, and then view the corresponding library encoding, is also Latin.

Now that the configuration file has been modified, the encoding is already utf8mb4 when recreating a new database.

Now the problem is how to modify the default character set for an existing database.

By looking at the MySQL manual (https://dev.mysql.com/doc/refman/5.7/en/alter-database.html), you can:

ALTER DATABASE <db_name> Character Set UTF8MB4

ALTER DATABASE <db_name> COLLATE UTF8MB4_UNICODE_CI

After changing the default encoding of the database, the code of the table originally created in the database has not been modified, and the modified table is encoded in a similar way to the database, nothing more than replacing it with a table, replacing <db_name> with <table_name >

After the table has been changed, the character set of the existing field in the table is still Latin, and the modified field is not the same as the first two, using the following statement:

ALTER TABLE <table_name> change <column_name> <column_name> <column_type> Character Set utf8mb4 ;

After this series of changes, we can finally insert the Chinese.

In order to avoid the problem of modification, it is best to configure the character encoding when the database is initially built.

There are a number of questions left that need to be clarified, such as what does the collation in the configuration file represent? What is the best way to write a configuration file? How does the configuration of a character set inherit between a database/table/field?

Research on MySQL coding problem