Modify the character encoding of mysql

MySQL is a widely used relational database management system that supports multiple character encodings, including UTF-8, GBK, etc. In practical applications, we often need to modify the character encoding of MySQL to meet actual needs. This article will introduce how to modify the character encoding of MySQL.

1. Determine the current character encoding

Before modifying the character encoding of MySQL, we need to determine the current character encoding. You can query the current character encoding through the following command:

show variables like 'character_set_database';

If the output result is UTF-8, the current character encoding is UTF-8. If the output result is GBK, the current character encoding is GBK.

2. Stop the MySQL service

Before modifying the character encoding of MySQL, you need to stop the MySQL service first. You can stop the MySQL service through the following command:

sudo service mysql stop

3. Modify the MySQL configuration file

The MySQL configuration file is located at /etc/mysql/mysql.conf.d/mysqld. cnf. Open the file with an editor and find the following two lines:

collation-server = utf8_general_ci 
character-set-server = utf8

Modify them to:

collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

What is modified here is that the encoding is UTF-8. Note that different situations may require modification. coding.

4. Restart the MySQL service

Restart the MySQL service, which can be achieved through the following command:

sudo service mysql start

5. Modify the created database and table Character set

After the MySQL character set is modified, the character sets of previously created databases and tables are not automatically modified. We need to manually modify the character set of the created database and tables.

Use the following command to modify the character set of the database:

ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Among them, dbname represents the database name to be modified, utf8mb4 represents the character set, and utf8mb4_unicode_ci represents the collation rule.

Use the following command to modify the character set of the table:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Among them, tablename represents the table name to be modified, utf8mb4 represents the character set, and utf8mb4_unicode_ci represents the collation rule.

It should be noted that relevant data needs to be backed up before executing the above command to avoid data loss.

6. Modify the character set during connection

When connecting to MySQL, you need to specify the character set used when connecting. You can specify the character set when connecting through the following command:

mysql -u username -p --default-character-set=utf8mb4 dbname

where username represents the user name, dbname represents the name of the database to be connected, and utf8mb4 represents the character set.

7. Verify character set

Finally, we can verify whether the character set modification is successful through the following command:

show variables like 'character_set_database';

If the output result is consistent with the modified character If the codes are consistent, the modification is successful.

Summary

There are many things that need to be paid attention to when modifying the character encoding of MySQL. You need to pay attention to backing up relevant data to avoid data loss. Be careful when operating, especially when modifying created databases and tables. It is recommended to try it in a test environment first when making modifications to ensure that it will not affect the official environment.

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