"MySQL" Unified console-pma-php Encoding! Solve Chinese garbled problem

Toss for a long time, see a lot of solutions, or the PMA can display Chinese, console garbled, or PHP call display is garbled, very egg pain, think must write a summary of the article.

1. Understanding the encoding mechanism of MySQL

When MySQL processes the connection, the SQL requests sent by the external connection are converted according to the following order:
character_set_client//The character set used by the customer connection
|
character_set_connection//mysql Connection Character Set
|
Character_set_database//The character set used by the database (table, column)
|
Character_set_results//client displays the character set used

See: MySQL Code details

In addition, there are database character sets, table character sets, and field character sets.

2. The root cause of garbled characters is

The ① client did not correctly set the client character set, causing the original SQL statement to be converted to the character set connection, which would lose information, and if the client was in UTF8 format, it would lose information if it was converted to gb2312 format. Conversely, it is not lost. Make sure that the connection character set is larger than the client character set to guarantee that the conversion does not lose information.
② database font is not set correctly, if the database font settings are incorrect, then the connection character set is converted to the database character set so that the encoding is lost because it is the same as above.

3. Solve the console-PMA garbled

the method I provide here is to unify all character sets as UTF8

① the character set of the unified client, connection, database.

Set character_set_client = Gbk;set Character_set_connection = Utf8;set character_set_database = Utf8;set character_set_ results = GBK;

This explains that CLIENT=GBK is designed to be able to enter Chinese in the console, RESULTS=GBK is to display Chinese in the console.

Check it out after you've modified it.

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\ |

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

② the character set of a consolidated database, data table, table field.

First step: View Specific database character sets

To view the character set of a 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 already UTF8.

Step Two: view the specified data table character set

To view the character set of a data table person

Show create table person;

CREATE TABLE ' person ' (
' Name ' varchar (+) not NULL
) Engine=innodb DEFAULT Charset=utf8 |

, I can see that my data table is also a UTF8 character set.

Step three: 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 sort character set utf8_general_ci, but if there is no human change, you can think of the field character set is UTF8, if not sure, it is directly modified.

Fourth step: Modify the character set to UTF8

Modifying the database Character set

ALTER DATABASE db_test CharSet UTF8;

Modifying the table character set

ALTER TABLE person CharSet UTF8;

Modify the field character set

ALTER TABLE ' person ' modify column ' name ' varchar (in) character set UTF8 not null;

(I set the field here at the same time name cannot be empty and is varchar (30) type)

Please refer to Baidu for more changes!!

The above is a temporary setting. After restarting MySQL, it will revert to its original state, so add a sentence under the [Mysqld] node in the My.ini configuration file in the MySQL directory:

Character_set_server=utf8

4. solve the garbled situation of PHP get data

Add a sentence in the connection string: Charset=utf8.

5. Testing

Add a Chinese to the PMA and view it in the console and in PMA and PHP,

Execute an INSERT Chinese statement in PHP and view it in the console-pma-php

Insert a Chinese in cmd and then view it elsewhere.

The effect is as follows:

In cmd:

In PMA:

PHP ( page encoding format is UTF-8):

"MySQL" Unified console-pma-php Encoding! Solve Chinese garbled problem