Modify mysql character set

MySQL is a common relational database management system. In actual development, it may be necessary to modify the character set of MySQL. This article will introduce how to modify the character set in MySQL.

1. Check the current character set of the database

To check the current character set of the database in MySQL, you can use the following command:

SHOW VARIABLES LIKE '%character%';

This command will return some system parameters. value, which contains the currently used character set. For example:

Variable_name       | Value
---------------------|---------
character_set_client | utf8mb4
character_set_results| utf8mb4
character_set_system | utf8
character_sets_dir   | /usr/share/mysql/charsets/

Among them, character_set_client represents the character set used by the client, character_set_results represents the character set of the query results, character_set_system represents MySQL The character set used in the system.

2. Modify the character set of the database

We can modify the character set of MySQL in the MySQL startup parameters or configuration file. First, you need to edit the my.cnf file, which is usually located in the /etc/mysql or /etc/mysql/mysql.conf.d directory.

After opening the file, find the following line:

[mysqld]
...

Add the following code under this line:

collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

These options will respectively set the character set and combination used by MySQL Rules and default connection character sets.

After saving the changes, we need to restart the MySQL service for the changes to take effect:

sudo service mysql restart

Next use the previously mentioned command, you can see that MySQL has successfully used the new settings we set character set.

3. Modify the character set of the database table

When you need to modify the character set of an existing database table, you can use the following method:

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Among them, table_name is the name of the table that needs to be operated, utf8mb4 is the new character set, utf8mb4_unicode_ci is the new combination rule.

4. Modify the character set of the database column

If you need to modify the character set of an existing column, you can use the following statement:

ALTER TABLE `table_name` MODIFY `column_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Among them, table_name is the name of the target table, column_name is the name of the target column, utf8mb4 is the new character set, utf8mb4_unicode_ci is the new combination rule.

Summary

Modifying the character set in MySQL requires editing the configuration file and using SQL statements. After the modification is completed, the MySQL service needs to be restarted for the changes to take effect. If you need to modify the character set of an existing table or column, you can use the ALTER command to do so. It should be noted that modifying the character set may affect existing data, and data should be backed up in advance to avoid data loss.

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