MySQL Chinese garbled characters, phpMyAdmin garbled characters, causes of PHP garbled characters and Solutions

Causes of garbled characters

MySQL character encoding is introduced in version 4.1 and supports multiple languages. In addition, some features have exceeded those of other database systems.

Run the following command under MySQL command line client to view the MySQL character set:

Mysql> show character set;
+ ---------- + ----------------------------- + --------------------- + -------- +
| Charset | description | default collation | maxlen |
+ ---------- + ----------------------------- + --------------------- + -------- +
| Big5 | big5 traditional Chinese | big5_chinese_ci | 2 |
| Dec8 | dec West European | dec8_swedish_ci | 1 |
| Cp850 | dos West European | cp850_general_ci | 1 |
| HP8 | HP West European | hp8_english_ci | 1 |
| Koi8r | KOI8-R relcom Russian | koi8r_general_ci | 1 |
| Latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| Latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| Swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ASCII | us ASCII | ascii_general_ci | 1 |
| Ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| Sjis | shift-JIS Japanese | sjis_japanese_ci | 2 |
| Hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| Tis620 | tis620 Thai | tis620_thai_ci | 1 |
| Euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| Koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| Gb2312 | gb2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| Greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| Cp1250 | Windows Central European | cp1250_general_ci | 1 |
| GBK Simplified Chinese | gbk_chinese_ci | 2 |
| Latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| Armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| Utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| Ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| Cp866 | dos Russian | cp866_general_ci | 1 |
| Keybcs2 | dos kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| Macce | Mac Central European | macce_general_ci | 1 |
| Macroman | Mac West European | macroman_general_ci | 1 |
| Cp852 | dos Central European | cp852_general_ci | 1 |
| Latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| Cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| Cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| Cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| Binary pseudo charset | binary | 1 |
| Geostd8 | geostd8 Georgian | geostd8_general_ci | 1 |
| Cp932 | sjis for Windows Japanese | cp932_japanese_ci | 2 |
| Eucjpms | ujis for Windows Japanese | eucjpms_japanese_ci | 3 |
+ ---------- + ----------------------------- + --------------------- + -------- +
36 rows in SET (0.02 Sec)

MySQL 4.1 character set support has two aspects: Character Set and collation ). The support for character sets is refined to four levels: Server, database, table, and connection ).
You can run the following two commands to view the character set and sorting method settings of the system:

Mysql> show variables like 'character _ SET _ % ';
+ -------------------------- + ------------------------------------------- +
| Variable_name | value |
+ -------------------------- + ------------------------------------------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir | D: \ mysql \ MySQL Server 5.0 \ share \ charsets \ |
+ -------------------------- + ------------------------------------------- +
8 rows in SET (0.06 Sec)

Mysql> show variables like 'collation _ % ';
+ ---------------------- + ------------------- +
| Variable_name | value |
+ ---------------------- + ------------------- +
| Collation_connection | latin1_swedish_ci |
| Collation_database | latin1_swedish_ci |
| Collation_server | latin1_swedish_ci |
+ ---------------------- + ------------------- +
3 rows in SET (0.02 Sec)

The values listed above are the default values of the system. Latin1 adopts latin1_swedish_ci by default, and Latin1 adopts Swedish sorting.
Why is it latin1_swedish_ci by default? It is easy to trace the MySQL history.

In 1979, a Swedish company tcx wanted to develop a fast multi-thread, multi-user database system. At first, tcx wanted to use msql and their own low-level fast routine (indexed sequential access method, isam) to connect to the database table. However, after some tests, it came to the conclusion: msql is not fast and flexible enough for its needs. This generates a new SQL interface for the connector database, which uses almost the same APIs as msql. This API is designed as a third party that can be written by msql.CodeIt is easier to port data to MySQL.

I believe that if MySQL is developed in China, the Chinese language is also the default encoding.

Of course, you can also modify the default Character Set of MySQL.
In the MySQL configuration file my. ini, find the following two sentences:

[MySQL]

Default-character-set = Latin1

And

# Created and no character set is defined
Default-character-set = Latin1

You can modify the following values.

We do not recommend that you change the default value.
That is to say, when MySQL is started, if a default character set is not specified, this value is inherited from the configuration file;
Character_set_server is set as the default character set. When a new database is created,
Unless explicitly specified, the character set of this database is set to character_set_server by default. When a database is selected,
Character_set_database is set to the default Character Set of this database; When a table is created in this database,
The default Character Set of the table is set to character_set_database, which is the default Character Set of the database;
When you set a column in a table, unless explicitly specified, the default character set in this column is the default Character Set of the table.

This problem arises, for example, a database is GBK encoded. If the character set is not specified when you access the database, it is GBK.
This value will inherit the Latin1 of the system, so that MySQL Chinese garbled characters will be generated.

Garbled Solution

To solve the garbled problem, you must first find out the encoding used by your database. If not specified, the default value is Latin1.
The three character sets gb2312, GBK, and utf8 are used most.

So how do we specify the character set of the database? The following GBK is also used as an example.

[Create a database in MySQL command line client]

mysql> Create Table 'mysqlcode' (
-> 'id' tinyint (255) unsigned not null auto_increment primary key,
-> 'content' varchar (255) not null
->) type = MyISAM Character Set GBK collate gbk_chinese_ci;
query OK, 0 rows affected, 1 warning (0.03 Sec)

Mysql> DESC mysqlcode;
+ --------- + --------------------- + ------ + ----- + --------- + ------------------ +
| FIELD | type | null | key | default | extra |
+ --------- + --------------------- + ------ + ----- + --------- + ------------------ +
| ID | tinyint (255) unsigned | no | pri | auto_increment |
| Content | varchar (255) | no |
+ --------- + --------------------- + ------ + ----- + --------- + ------------------ +
2 rows in SET (0.02 Sec)

Type = MyISAM Character Set GBK collate gbk_chinese_ci;
Is to specify the character set of the database, collate (), so that MySQL supports multiple encoding databases at the same time.

Of course, we can also use the following command to modify the character set of the database:
Alter database da_name default Character Set 'charset '.

The client sends data in GBK format. The following configurations can be used:

Set character_set_client = 'gbk'
Set character_set_connection = 'gbk'
Set character_set_results = 'gbk'

This configuration is equivalent to set names 'gbk '.

Perform operations on the database you just created

Mysql> use test;
Database changed

Mysql> insert into mysqlcode values (null, 'php hobby ');
Error 1406 (22001): Data too long for column 'content' at Row 1

If the character set is not specified as GBK, an error occurs during insertion.

Mysql> set names 'gbk ';
Query OK, 0 rows affected (0.02 Sec)

The specified character set is GBK.

Mysql> insert into mysqlcode values (null, 'php hobby ');
Query OK, 1 row affected (0.00 Sec)

Inserted successfully

 

Mysql> select * From mysqlcode;
+ ---- + ----------- +
| ID | content |
+ ---- + ----------- +
| 1 | PHP hobbies |
+ ---- + ----------- +
1 row in SET (0.00 Sec)

Garbled characters are also displayed when the character set GBK is not specified.

Mysql> select * From mysqlcode;
+ ---- + --------- +
| ID | content |
+ ---- + --------- +
| 1 | PHP ??? |
+ ---- + --------- +
1 row in SET (0.00 Sec)

[Create a database in phpMyAdmin and specify the character set]

Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018560 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018560.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

Select the table type as needed. Here, select MyISAM (full-text search is supported );
Sort and select gbk_chinese_ci, that is, the GBK character set.
Gbk_bin Simplified Chinese, binary. Gbk_chinese_ci (Simplified Chinese), case-insensitive.

Insert a database into the database you just created

screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018561 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018561.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

garbled characters found during browsing again
screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018562 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018562.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

Why? Because the database is a GBK character set, but we did not specify it as GBK during the operation
back to the database homepage
screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018563 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018563.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

the default latin1_bin for MySQL connection verification is displayed. Change it to gbk_chinese_ci
screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018564 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018564.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

Insert a data record. Check that this article is normal.
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018565 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018565.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

For more information about phpMyAdmin garbled characters, refer to
Http://www.phpfans.net/bbs/viewthread.php? Tid = 146 & extra = Page % 3d1
PHP lovers station http://www.phpfans.net/

[Solve PHP to read database garbled characters]

Take database mysqlcode as an Example

Code: [copy to clipboard] $ conn = mysql_connect ("localhost", "root", "");
mysql_query ("set names 'gbk '"); // This is the specified database character set. It is usually placed after the database is connected.
mysql_select_db ("test ");

$ SQL = "select * From mysqlcode";
$ result = mysql_query ($ SQL, $ conn );

?>


MySQL character encoding




echo "




";
}?>


id	content
". $ row ['id']. "	". $ row ['content']. "







if we comment out mysql_query ("set names 'gbk'"), it must be garbled.
screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018566 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018566.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

The sentence is normal again.
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open ('/ddimg/uploadimg/20061129/1018567 .jpg');} "alt =" "src =" http://www.souzz.net/ddimg/uploadimg/20061129/1018567.jpg "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

One sentence
What encoding does your database use? Set names 'your encoding' before you operate on the database ';

PS: Page declarative encoding: in the HTML code head, you can use <meta http-equiv = "Content-Type" content = "text/html; charset = "XXX"/> to tell the browser what encoding used by the web page, the current Chinese website development is mainly used in gb2312 and UTF-8 two types of encoding.