Repeat the MySQL utf8 Problem

 

MySQLSlave4. xSupported versionsUnicode,3. xOnlyLatin1Encoding. I started using it at work.MySQLUsedPHPAccess, webpageXxx. phpYesGb2312Encoding, used to store the dataPHPThe output is in Chinese.PhpMyAdminRunSelect,Update,DumpThey are all Chinese characters and there are no garbled characters.

 

SlaveMySQLSupportedUnicodeTo keep pace with the timesWebProgramAlso begin to consider usingUtf8. ActuallyUtf8It has been used for several years, and the program can basically run. There is no big problem, but it is always uncomfortable when data is swapped.

 

[Symptom]

Webpage Xxx. php Use Editplus Save Utf8 Format, MySQL In My. ini Settings Default-character-set = utf8 , Added during table Creation Create Table 'xxx' (myname varchar (255) engine = MyISAMDefault charset = utf8 , Use Xxx. php Run Insert/update/select The output is in Chinese. It seems no problem, but it is used PhpMyAdmin View Select Is garbled, with third-party tool software (such Sqlyog ) View Select Also garbled, Mysqldump It's also messy. Of course, if you select Binary/varbinary/blob Type, no garbled characters are found, because the specified binary is saved, MySQL There is no encoding concept when saving data.

 

[Search for problems]

AlthoughMy. iniSettingsDefault-character-set = utf8But there are new discoveries when you execute 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 Value Not all columns are Utf8 , There are still some Latin1 For example Client And Connection . Web Page Xxx. php The process is like this: From Xxx. php Enter Chinese characters on the page because Xxx. php Yes Utf8 Encoded, so Xxx. php To Utf8 Format conversion of the input Chinese characters, and then Utf8Submit MySQL , MySQL Of Client And Connection All Latin1 While the table is Utf8 , So MySQL During storage Xxx. php The submitted Chinese characters are converted Latin1 Format, and then convert Utf8 The character format is in the table. If we use third-party software or PhpMyAdmin Go SelectView this table, and the data stored in the table is Latin1 Of Utf8 Character. Utf8 Of course, it looks garbled.The solution is to make all the processes Utf8 .

 

[Solve the problem]

1, SlaveMy. iniStart

[client] default-character-set = utf8 [MySQL] default-character-set = utf8 [mysqld] default-character-set = utf8

Above3ItemsSectionAddDefault-character-set = utf8We may only addMysqldItem.

Then restartMySQL, Execute

Mysql>Show variables like 'character % ';

Mysql>Show variables like 'collation _ % ';

Make sure allValueItemUtf8You can.

 

2, Add at table CreationUtf8, Table FieldCollationCan be added or not added. The default value isUtf8_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 = MyISAMDefault charset = utf8

 

3WebpageXxx. phpSelect when savingUtf8Encoding. It is best to add the page header

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

In executionCrudRun

Mysql_query ("set names utf8 ");

TestCodeXxx. phpAs 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 = "CHNSoftware Development Co., Ltd., JPNAlibaba Cloud Elastic Compute Service Co., Ltd., KORToo many other users Zookeeper Zookeeper Zookeeper, 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 settingPHPInsert anyUtf8Character, inPHPInPhpMyAdminInMySQLThe third-party client software obtained from is the same Chinese characters, and no garbled characters are found,MysqldumpIt is also a Chinese character.OKTo solve the problem.

 

[Also] in ChineseWindowsInCmd.exeRun inMysql.exeCharacter terminal, cannot use the above rules, because by default, ChineseWindowsSystemCmd.exeThe code page in isCp936That isGBK, Cannot display allUtf8Character, so it is normal to see garbled characters in the character terminal. Do not be surprised. This problem occurs in the classUNIXSystemShellTerminal.