Shulou(Shulou.com)06/01 Report--

Database garbled in MySQL can generally be set by character set, but garbled can appear in all stages, so this article sorts out the various stages of garbled and the corresponding methods.

Add charset settings when creating a database / table

Build a database

CREATE DATABASE database name DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Build a table

Create table table name (fields make up detailed list information) default charset=utf8

Client display

Locale

Confirm whether locale is set to UTF8 mode. Generally, there is no problem on the server side, only on the client side. Or part of the client has a problem, often there is a problem with the client display setting.

Confirm command: locale

Mysql Settin

Use show variables like'% character%' for confirmation. Generally, you can modify character_set_database and character_set_server settings only in the current session.

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

Confirm command (mysql): show variables like'% character%'

Local setting

Only works on the current session. The setting method is as follows:

Set character_set_database=utf8

Global settin

Multiple session can be set to work as follows:

Set global character_set_database=utf8

Of course, the session mode will fail after the database restart and need to be persisted. The same settings should be set to the mysql configuration file my.cnf.

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

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

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

Set character_set_results='utf8'

Database data export

Generally, using mysqldump for database export will use the following command

Mysqldump-u user name-p user password database name > mysqlbackup.sql

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

Mysqldump-u user name-p user password-default-character-set=utf8 database name > mysqlbackup.sql

If there is a blob type, you need to use the hex-blob option for mysqldump export

Mysqldump-u user name-p user password-hex-blob database name > mysqlbackup.sql

Database data import

If there is a problem during import, you can also consider adding character-level settings.

Mysql-u user name-p user password-default-character-set=utf8 database name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.