Modify mysql database encoding

MySQL is an open source database management system that is widely used in various websites, data warehouses and enterprise applications. In MySQL, encoding format refers to the internal binary storage method of text content stored in the database.

If your database stores special characters, such as Chinese or other non-English characters, you need to set the correct encoding format to ensure that these characters can be stored and retrieved correctly. This article will introduce how to modify the encoding format of the MySQL database.

Step One: Backup the Database

Before performing any database changes, be sure to back up the database to prevent data loss or irrecoverable damage. You can use the mysqldump tool provided by MySQL to back up the database. The command is as follows:

mysqldump -u [username] -p [database_name] > backup.sql

where [username] is the user name of the database, [database_name] is the name of the database to be backed up, > means redirecting the backup content to backup.sql file.

Step 2: View the database encoding

To view the default encoding in the current MySQL database, you can run the following command:

SELECT @@character_set_database;

This command will display the current default encoding of the database Format.

Step 3: Modify the database encoding

To modify the database encoding, you need to perform the following steps:

1. Find the MySQL configuration file (usually /etc/my. cnf) and open it. If you can't find the file, you can run the following command in the terminal to find it:

find / -name my.cnf

2. Add the following lines to the configuration file:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

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

These settings Ensure that all MySQL clients and servers use the correct encoding format.

3. Save and close the configuration file.
4. Restart the MySQL server for the changes to take effect:

sudo service mysql restart

5. Run the following command to check if the settings were successfully applied:

SELECT @@character_set_database;

If correct applied, the output should read "utf8mb4".

Step 4: Convert existing tables to the new encoding

If tables already exist in the database, they need to be converted to the new encoding format in order to store the data correctly.

1. Run the following command to generate a SQL statement for all tables that need to be upgraded:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS execute_sql
FROM information_schema.tables
WHERE table_schema = '[database_name]' AND table_type = 'BASE TABLE'
ORDER BY table_name DESC;

Where [database_name] is the name of the database that needs to be converted.

2. Copy the results into a terminal and run to convert all tables to the new encoding. As shown below:

ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
...

Step 5: Test the changes

After converting all tables, you need to test whether the changes were successful. Run the following command to ensure that the encoding format of all tables has been updated:

SHOW FULL COLUMNS FROM [table_name];

Where [table_name] is the name of the table to test. This command will display the table's columns and metadata, as well as see if any data in the old encoding format exists in the table. If the results appear correct, the change was successful.

Conclusion

Modifying the database encoding format in MySQL can ensure that special characters can be stored and retrieved correctly, providing perfect support for multi-language websites and applications. By following the steps provided in this article, you will be able to easily modify the encoding format of your MySQL database and avoid data loss or corruption.

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