Mysql's Chinese UTF8 garbled problem _mysql

Since MySQL supports Unicode, our web programs are starting to think about using UTF8 to keep up with the times. In fact, UTF8 also used for several years, the basic program can run, no big problem, but the data switching, always encounter unpleasant things.

"Problem phenomenon"

Web page xxx.php with EditPlus Save as UTF8 format, MySQL set Default-character-set=utf8 in My.ini, add create table ' xxx ' (myname varchar ( 255) Engine=myisam DEFAULT Charset=utf8, with xxx.php implementation of the Insert/update/select out are all Chinese, seemingly no problem, but with phpMyAdmin see Select is garbled, With Third-party tool software (such as SQLyog) see Select is garbled, mysqldump is also garbled, very uncomfortable. Of course, if you build the table, choose the type of Binary/varbinary/blob, will not find garbled, because the specified is binary save, MySQL save data when there is no coding concept.

"Find Problems"

Although the Default-character-set=utf8 is set in My.ini, there are new discoveries when executing the following command:

Mysql> show VARIABLES like ' character% '; +----------------------------------------+------------------------- | Variable_name | Value +----------------------------------------+------------------------- | Character_set_client | latin1 | character_set_connection | Latin1 | Character_set_database | Utf8 | Character_set_filesystem | Binary | Character_set_results | latin1 | Character_set_server | UTF8 | Character_set_system | UTF8 | Character_sets_dir | D:\mysql\share\charsets\ +----------------------------------------+------------------------- 8 rows in Set (0.00 sec) Mysql> show VARIABLES like ' collation_% '; +---------------------------------------+------------------ | Variable_name | Value +---------------------------------------+------------------ | Collation_connection | latin1_swedish_ci | Collation_database | utf8_general_ci | Collation_server | utf8_general_ci +--------------------------------------+------------------ 3 Rows in Set (0.00 sec)

found that the Value column is not full of UTF8, there are still some latin1, such as the client and connection. The Web page xxx.php's work process is like this: Enter Chinese characters from the xxx.php page, because xxx.php is UTF8 encoded, so xxx.php converts the input characters in UTF8 format and submits them to MySQL in UTF8, but MySQL's client and connection Are latin1, and the table is UTF8, so the MySQL storage, the first will be xxx.php submitted to the Latin1 format, and then converted to UTF8 character format exists in the table. If at this time we use Third-party Software or phpMyAdmin to select to view this table, and the table stored in the data is latin1 UTF8 characters, out of the UTF8 format, of course, look garbled. the solution is to make all the processes UTF8 is OK.

"Problem Solving"

1. From My.ini

[Client] Default-character-set=utf8 [MySQL] Default-character-set=utf8 [Mysqld] Default-character-set=utf8

The above 3 section all must add Default-character-set=utf8, usually we may only add mysqld one item.

Then restart MySQL, execute

Mysql> show VARIABLES like ' character% ';

Mysql> show VARIABLES like ' collation_% ';

Make sure all the value items are UTF8.

2, add utf8, table field collation can add, not overtime default is Utf8_general_ci.

CREATE TABLE ' Tablename4 ' ( ' id ' int (one) not NULL auto_increment, ' varchar1 ' varchar (255) DEFAULT NULL, ' Varbinary1 ' varbinary (255) DEFAULT NULL, PRIMARY KEY (' id ') ) Engine=myisam DEFAULT Charset=utf8

3, the Web page xxx.php save the choice of UTF8 code, the best page with the first plus

Header (' Conten-type:text/html;charset=utf-8 ');

Perform the CRUD operations before performing the

mysql_query ("Set names UTF8");

The test code xxx.php as follows:

<?php Header (' Conten-type:text/html;charset=utf-8 '); mysql_connect ("localhost", "root", "password") or Die ("Could not connect:".) Mysql_error ()); mysql_select_db ("test"); mysql_query ("Set names UTF8"); $STR = "CHN Software Development Co., Ltd., jpnソフトウェア 発, KOR 소프트웨어 개발 유한, 공사 rusсудпрограммногообеспечения ". Time (); $sql = "INSERT into Tablename4 (VARCHAR1, Varbinary1) VALUES ('. $str." ', ' ". $str.") "; echo $sql. " mysql_query ($sql); $result = mysql_query ("Select ID, varchar1, varbinary1 from Tablename4"); while ($row = Mysql_fetch_array ($result, Mysql_both)) { printf ("ID:%s, Varchar1:%s, Varbinary1:%s<br>", $row [0], $row ["Varchar1"], $row ["Varbinary1"]); } Mysql_free_result ($result); ?>

So set, either in the PHP page to insert any UTF8 characters, taken out in the PHP page, taken out in the phpMyAdmin, in the MySQL third-party client software taken out, are the same Chinese characters, will not find garbled, mysqldump out is also Chinese characters. OK, problem solved.

"Another" under the Chinese Windows system, Run Mysql.exe character terminal in Cmd.exe, cannot use the above rule, because by default, the code page in the Chinese Windows system cmd.exe is cp936 namely GBK, cannot display all UTF8 characters, so see in character terminal garbled is normal phenomenon, don't be strange, this problem in class UN Can be solved in the shell terminal of the IX system.