[MySQL] unified console-pma-PHP encoding! Solve Chinese garbled characters

[MySQL] unified console-pma-PHP encoding! Solve Chinese garbled characters

After a long time, I have read a lot of solutions. Either pma can display Chinese characters, the console is garbled, or PHP calls can display garbled characters, I think it is necessary to write an article to sum up.

1. Understand the encoding mechanism of MySQL

When MySQL processes connections, SQL requests sent from external connections are converted in the following order:
Character_set_client // character set used for customer connection
|
Character_set_connection // MySQL connection Character Set
|
Character_set_database // character set used by the database (Table, column)
|
Character_set_results // the character set used by the client to display

For details, see mysql encoding.

In additionDatabase Character Set, table character set, and field character set.

2. The root cause of Garbled text is

① The client does not correctly set the client Character Set, causing the original SQL statement to be converted to the character set referred to by connection. This conversion will lead to loss of information. If the client is in utf8 format, if it is converted to the gb2312 format, the information will be lost. Otherwise, the information will not be lost. Make sure that the character set of connection is greater than the client character set to ensure that no information is lost during conversion.
② The font of the database is not correctly set. If the font of the database is incorrectly set, the connection character set is converted to the database character set and the encoding is still lost because it is the same as the above.

3.Solve console-pma garbled

The method I provide here is to unify all character setsUtf8

① Unified character set of client, connection, and database.

 

set character_set_client = gbk;set character_set_connection = utf8;set character_set_database = utf8;set character_set_results = gbk;

The client = gbk is used to enter Chinese characters in the console, and results = gbk is used to display Chinese characters in the console.

Check the modification.

 

 show variables like "char%";

+ -------------------------- + -------------------------------------- +
| Variable_name | Value |
+ -------------------------- + -------------------------------------- +
| Character_set_client | gbk |
| Character_set_connection | gbk |
| Character_set_database | utf8 |
| Character_set_filesystem | binary |
| Character_set_results | gbk |
| Character_set_server | utf8 |
| Character_set_system | utf8 |
| Character_sets_dir | d: \ WebServer \ MySQL \ share \ charsets \ |

+ -------------------------- + -------------------------------------- +

② Unified character sets for databases, data tables, and table fields.

Step 1: view specific database character sets

View the character set of database db_test

show create database db_test;

+ ---------- + ------------------------------------------------------------------ +
| Database | Create Database |
+ ---------- + ------------------------------------------------------------------ +
| Db_test | create database 'db _ test '/*! 40100 default character set utf8 */|
+ ---------- + ------------------------------------------------------------------ +

It can be seen that the character set of the db_test database is utf8.

Step 2: view the character set of a specified data table

View the character set of the person data table

show create table person;

Create table 'person '(
'Name' varchar (30) NOT NULL
) ENGINE = InnoDB default charset = utf8 |

It can be seen that my data table is also the utf8 Character Set

Step 3: view the character set of the specified table Field

show full columns from person;

+ ------- + ------------- + ----------------- + ------ + ----- + --------- + ------- + ------------------------------- + ----------- +
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+ ------- + ------------- + ----------------- + ------ + ----- + --------- + ------- + ------------------------------- + ----------- +
| Name | varchar (30) | utf8_general_ci | NO | NULL | select, insert, update, references |
+ ------- + ------------- + ----------------- + ------ + ----- + --------- + ------- + ------------------------------- + ----------- +

This is the sorting Character Set utf8_general_ci, but if there is no human modification, you can think of the field character set as utf8. If you are not sure, you can directly modify it.

Step 4: Modify the character set to utf8

Modify database Character Set

alter database db_test charset utf8;

Modify Table Character Set

 

 

alter table person charset utf8;

 

Modify Field Character Set

alter table `person` modify column `name` varchar(30) character set utf8 not null;

(The field name cannot be empty and is of the varchar (30) type)

 

For more modification methods, refer to Baidu !!

The preceding settings are temporary settings. After mysql is restarted, it will be restored to its original state. Therefore, add one sentence to the [mysqld] node in the my. ini configuration file under the mysql directory:

Character_set_server = utf8

4.Solve PHP Data garbled

Add charset = utf8 to the connection string.

5. Test

Add a Chinese character to pma and view it in the console and pma and php,

Execute an insert Chinese statement in php, and then view it in console-pma-php.

Insert a Chinese character in cmd and view it elsewhere.

The results are as follows:

Cmd:

Pma:

In php (Webpage encoding format for UTF-8):