How to deal with garbled characters in Mysql database

In MySQL, database garbled characters can generally be fixed by setting the character set, but garbled characters can appear at various stages, so this article summarizes the various stages of garbled characters and the corresponding methods.

Add charset settings when creating a database/table

Building a database

CREATE DATABASE 數據庫名稱DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Create a table

create table 表名(字段構成詳細列表信息) default charset=utf8;

Client display

locale

Check whether the locale setting is UTF8. Generally, there is no problem on the server side, only on the client side. Or there may be problems with some clients, which is often caused by problems with the client display settings.

確認命令：locale

mysql settings

Use show variables like '%character%' to confirm. Generally, modifying character_set_database and character_set_server can only modify the settings in the current session.

Use the mysql command to set the character set within the session

確認命令（mysql）：show variables like '%character%'

Local settings

It only works in the current session. The setting method is:

set character_set_database=utf8

Global Settings

The setting method for multiple sessions to work is:

set global character_set_database=utf8

Of course, the session mode will become invalid after the database is restarted, and needs to be persisted. The same setting can be set in the MySQL configuration file my.cnf.

In this way, when creating a database or creating a database table, utf8 is specified by default.

If it is a pure display problem, it may often be due to the character_set_results setting.

Use show variables like '%character%' to confirm. If the setting is incorrect, you can use the following method to solve it

set character_set_results='utf8';

Database data export

Generally, the following command is used to export the database using mysqldump

mysqldump -u用戶名-p用戶密碼數據庫名稱>mysqlbackup.sql

If garbled characters appear, you can add the following Option

mysqldump -u用戶名-p用戶密碼--default-character-set=utf8 數據庫名稱>mysqlbackup.sql

If it contains blob type, you need to use the hex-blob option to export mysqldump

mysqldump -u用戶名-p用戶密碼--hex-blob 數據庫名稱>mysqlbackup.sql

Database data import

If there are problems when importing, you can also consider adding character-level settings

mysql -u用戶名-p用戶密碼--default-character-set=utf8 數據庫名稱<mysqlbackup.sql

You can also use the following method and execute the following command before importing

set names utf8;

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links