MySQL utf8 garbled characters

Since MySQL supports Unicode, to keep pace with the times, our webProgramWe also started to consider using utf8. In fact, utf8 has been used for several years, and the program can basically run, there is no big problem, but when data is swapped, it is always uncomfortable.

[Symptom]

The webpage XXX. php uses editplus to save it as utf8. MySQL sets default-character-set = utf8 in my. ini and addsCreate Table 'xxx' (myname varchar (255) engine = MyISAM default charset = utf8, with xxx. PHP executes insert, update, and select statements in Chinese. It seems that there is no problem, but it is garbled by phpMyAdmin. If you use a third-party tool software (such as sqlyog), the SELECT statement is garbled, mysqldump is garbled. Of course, if you select the binary/varbinary/BLOB type during table creation, no garbled characters will be found, because the binary storage is specified, and MySQL does not have the encoding concept when saving data.

[Search for problems]

Although default-character-set = utf8 is set in my. ini, it is found that the following command is executed:

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)

It is found that all values in the value column are utf8, and some are Latin1, such as the client and connection. The webpage XXX. PHP is like this: from XXX. enter Chinese characters on the PHP page because XXX. PHP is UTF-8 encoded, so xxx. PHP converts the input Chinese Characters in utf8 format and submits them to MySQL using utf8. However, the mysql client and connection are both Latin1 and the table is utf8. Therefore, when MySQL is stored, set XXX first. the Chinese characters submitted by PHP are converted to Latin1 format, and then to utf8 character format. If we use a third-party software or phpMyAdmin to select this table, and the data stored in the table is utf8 characters After Latin1, it is obtained in utf8 format, of course it looks garbled.The solution is to make all the processesUtf8.

[Solve the problem]

1. Start with my. ini.

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

Default-character-set = utf8 must be added to all the preceding three sections. Normally, only mysqld is added.

Restart MySQL and run

Mysql> show variables like 'character % ';

Mysql> show variables like 'collation _ % ';

Make sure that all value items are utf8.

2. Add utf8 when creating the table. The collation of the table field can be added but not added. The default value is utf8_general_ci.

Create Table 'tablename4 '( 'Id' int (11) not null auto_increment, 'Varchar1' varchar (255) default null, 'Varbinary1 'varbinary (255) default null, Primary Key ('id ') ) Engine = MyISAM default charset = utf8

3. Select utf8 encoding when saving the webpage XXX. php. It is best to add

Header ('conten-type: text/html; charset = UTF-8 ');

Execute the CRUD operation first.

Mysql_query ("set names utf8 ");

TestCodeXxx. php is as follows:

<? PHP Header ('conten-type: text/html; charset = UTF-8 '); Mysql_connect ("localhost", "root", "password") or die ("cocould not connect:". mysql_error ()); Mysql_select_db ("test "); Mysql_query ("set names utf8 "); $ STR = "CHN Software Development Co., Ltd., JPN zookeeper and zookeeper Co., Ltd., KORToo many other usersZookeeperZookeeperZookeeper, RUS has been written into our records before they were written into our records. ". Time (); $ SQL = "insert into tablename4 (varchar1, varbinary1) values ('". $ Str. "', '". $ Str ."')"; Echo $ SQL. "<HR> "; 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 ); ?>

After this setting, whether it is to insert any utf8 characters on the PHP page, obtained from the PHP page, obtained from phpMyAdmin, and obtained from the MySQL third-party client software, they are all the same Chinese characters and no garbled characters will be found. mysqldump is also a Chinese character. OK. Solve the problem.

The code page in bytes is cp936 or GBK, and cannot display all utf8 characters. It is normal to see garbled characters in character terminals, this problem can be solved in Shell terminals of Unix-like systems.