Corresponding Ways of Garbled Code Problem in Mysql Database

MySQL database garbled 1 character set can be set, but garbled can appear in various stages, so this article sorts out the various stages of garbled 1 and the corresponding methods.

Add charset settings when creating database/table

Database building

CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Table building

create table 表名(字段构成详细列表信息) default charset=utf8;

Client display

locale

Confirm whether locale is set in UTF8 mode. There is no problem on the server side, but only on the client side. Or a part of the client has a problem, which is often a problem with the client display settings.

确认命令：locale

mysql setting

Confirm with show variables like '% character%'. Modify character_set_database and character_set_server to modify settings only at the current session

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

确认命令（mysql）：show variables like '%character%'

Local setting

It only works in the current session, and the setting mode is:

set character_set_database=utf8

Global setting

The setting mode of multiple session is as follows:

set global character_set_database=utf8

Of course, the session mode will fail after the database is restarted, and it needs to be persisted. The same setting should be set in the configuration file my. cnf of mysql.

This defaults to the specified utf8 when creating a database or creating a database table

If it is purely a display problem, it may also be displayed due to character_set_results settings in many cases.

Confirm with show variables like '% character%'. If the setting is incorrect, you can use the following methods to solve it

set character_set_results='utf8';

Database data export

1 database export using mysqldump uses the following command

mysqldump -u用户名 -p用户密码 数据库名称 >mysqlbackup.sql

If there is garbled code, you can add the following Option

mysqldump -u用户名 -p用户密码 --default-character-set=utf8 数据库名称 >mysqlbackup.sql

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

mysqldump -u用户名 -p用户密码 --hex-blob 数据库名称 >mysqlbackup.sql

Database data import

If there is a problem when importing, you can also consider adding character-level settings

create table 表名(字段构成详细列表信息) default charset=utf8;0

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

set names utf8;

Summarize