How to modify the character encoding in mysql

MySQL is a popular relational database management system. When creating databases and tables, we usually define character sets and character encodings for them. If the character encoding of the database and table is inconsistent, some or all of the data may be lost. Therefore, it is very important to set the character encoding correctly. In this article, I will explain how to change the character encoding of an existing database or table in MySQL.

View character encoding in MySQL

Before modifying the character encoding, we need to first understand the character encoding used by the current database or table. It can be viewed through the following SQL statement:

SHOW CREATE DATABASE database_name;

SHOW CREATE TABLE table_name;

where database_name and table_name represent the database and table names to be viewed respectively. After executing the above command, you can see the character encoding information in the results.

Modify the database character encoding

Under normal circumstances, we do not need to modify the character encoding of the database. If you really need to modify it, you can follow the steps below:

1. Stop the MySQL service.

sudo systemctl stop mysql

2. Back up the original data and database definition files.

mysqldump -u root -p --opt --default-character-set=utf8mb4 dbname > dbname_backup.sql
cp -a /var/lib/mysql /var/lib/mysql_backup

Among them, dbname represents the name of the database to be backed up.

3. Modify the MySQL configuration file /etc/mysql/my.cnf.

Add the following content under the [mysqld] block:

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

If the corresponding configuration item already exists, just modify its corresponding value directly. . Characters from more languages ​​can be supported using the utf8mb4 character set and the utf8mb4_unicode_ci collation.

4. Start the MySQL service and modify the database character encoding.

sudo systemctl start mysql
mysql -u root -p
ALTER DATABASE dbname CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Among them, dbname represents the database name whose character encoding is to be modified.

Modify table character encoding

If you want to modify the character encoding and sorting rules of the table, you can follow the following steps:

1. Back up the original data and table definition document.

mysqldump -u root -p --opt --default-character-set=utf8mb4 dbname tablename > tablename_backup.sql

Among them, dbname and tablename represent the database and table names to be backed up respectively.

2. Modify table character encoding and sorting rules.

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Among them, table_name represents the table name whose character encoding is to be modified.

After executing the above command, MySQL will change the encoding and sorting rules of all character type fields in the table to utf8mb4 and utf8mb4_unicode_ci.

Modify field character encoding

If you only want to modify the character encoding of a field in the table, you can follow the steps below:

1. Back up the data.

mysqldump -u root -p --opt --default-character-set=utf8mb4 dbname tablename > tablename_backup.sql

Among them, dbname and tablename represent the database and table names to be backed up respectively.

2. Modify the field character encoding.

ALTER TABLE tablename MODIFY COLUMN column_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Among them, tablename and column_name respectively represent the table and field names whose character encoding is to be modified.

After executing the above command, MySQL will change the character encoding and collation of this field to utf8mb4 and utf8mb4_unicode_ci.

Summary

Correctly setting character encoding is very important for data saving and querying. In MySQL, we can modify the character encoding and collation rules of existing databases, tables and fields through the above methods. It should be noted that before performing the above operations, be sure to back up the original data or database definition file to prevent data loss.

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