Modify mysql database encoding

MySQL is a common relational database management system used to store and manage data in various websites and applications. MySQL supports multiple character sets and encoding methods, including UTF-8, GB2312, ISO-8859-1, etc. When using MySQL, you may need to modify the database encoding to ensure that text data can be stored and retrieved correctly. In this article, we will introduce how to modify the encoding of a MySQL database.

1. Query the encoding of the database

Before dealing with the encoding problem of the MySQL database, you first need to confirm the encoding method of the current database. We can query the encoding of the current database through the following SQL statement:

SHOW VARIABLES LIKE 'character_set_database';

If the encoding of the current database is UTF-8, the following results will be returned:

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | utf8   |
+------------------------+--------+

If the encoding of the current database is GBK , the following results will be returned:

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | gbk    |
+------------------------+--------+

2. Modify the encoding of the database

If you find that it is not the expected encoding when querying the database encoding, you need to modify it. The method is as follows:

1. Back up the database

Be sure to back up the database before modifying the encoding. Because during the encoding modification process, the database may be affected, resulting in data loss or data corruption.

mysqldump -u username -p databasename > backup.sql

This command will export the contents of the database to the backup.sql file, where username is the database user name and databasename is the database name.

2. Modify the database encoding

After backing up the database, we can start to modify the database encoding. The specific steps are as follows:

(1) Edit the MySQL configuration file

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

(2) Modify the configuration file

In the MySQL configuration file, you can find the following configuration:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

In this configuration file, you need to change character-set The values ​​of -server and collation-server are modified to new encoding methods, such as UTF-8. The modified configuration is as follows:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

(3) Restart the MySQL service

Finally, you need to restart the MySQL service through the following command to make the modification take effect:

sudo service mysql restart

3. Test the database encoding

After modifying the database encoding, you should test to ensure that it has been modified correctly. You can check the encoding of the current database through the following query:

SHOW VARIABLES LIKE 'character_set_database';

If the returned result shows that the current encoding is the same as the previous setting, it means the modification is successful.

In this article, we introduce how to modify the encoding of the MySQL database. Please remember to back up the database and carefully confirm every step of the modification process to avoid possible data loss or data corruption. Through the content of this article, I believe readers can easily modify and test the coding method of the MySQL database.

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