Mysql5.0 Chinese insertion garbled problem _ MySQL

Mysql5.0 Chinese language insertion garbled problem solved recently many posts about Chinese display garbled characters, so I also made a summary:

For more information, see Yang Taotao's summary of various gibberish issues.
Http://topic.csdn.net/u/20071124/08/3b7eae69-ed1d-4a77-8895-9930bf3601af.html

Principles of the MySQL character set. Extracted from the official document. Http://dev.mysql.com/doc/refman/5.1/zh/charset.html

Different encoding formats may lead to the same character, and the encoding in different character sets will be different. The characters of the same code in different character sets are also different. When the encoding format (character set) of the string returned by your MySQL is associated with your client tool program (mysql, php, query browser ,...) if the character set used is different, garbled characters are generated. For example, if a British friend tells you Long, when a Chinese elementary school student sees it, he will tell you "dragon" instead of "Long"

It is recommended that you take a moment to take a look at the detailed introduction and examples of character sets.
Http://dev.mysql.com/doc/refman/5.1/zh/charset.html (Chapter 1: Character set support ).

Here is a summary.

In MySQL, the default character set is set to four levels: Server-level, database-level, and table-level. The final result is field-level character set settings. Note that the first three are default settings, and your fields will use this character set setting instead of code. Therefore, we recommend that you use show create table; or show full fields from tableName; to check the character set settings of fields in the current table.

MySQL sets the character set of the connection environment with the Client, connection, and results parameters. through these parameters, MySQL will know what character set your Client tool uses and what character set the result set should be. In this way, MySQL will perform necessary translation. once these parameters are incorrect, the conversion errors of strings during the conversion process will naturally occur. Basically, 99% of garbled characters are caused by some.

Information to be checked after garbled characters. (If you need help from friends on the forum, we suggest you provide the following information)

1. character set settings for fields in the database table. Show create table TableName or show full columns from tableName

Mysql> show create table t1;
+ ------- + ------------------------------------
| Table | Create Table
+ ------- + ------------------------------------
| T1 | create table 'T1 '(
'Id' int (11) not null,
'C1' varchar (30) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = gbk |
+ ------- + ------------------------------------
1 row in set (0.00 sec)
Mysql> show full columns from t1;
+ ------- + ------------- + ---------------- + ------ + ----- +-
| Field | Type | Collation | Null | Key |
+ ------- + ------------- + ---------------- + ------ + ----- +-
| Id | int (11) | NULL | NO | PRI |
| C1 | varchar (30) | gbk_chinese_ci | YES |
+ ------- + ------------- + ---------------- + ------ + ----- +-
2 rows in set (0.00 sec)
Mysql>

2. the currently connected system parameter show variables like 'char %'

Mysql> show variables like 'char % ';
+ -------------------------- + ----------------
| Variable_name | Value
+ -------------------------- + ----------------
| Character_set_client | gbk
| Character_set_connection | gbk
| Character_set_database | latin1
| Character_set_filesystem | binary
| Character_set_results | gbk
| Character_set_server | latin1
| Character_set_system | utf8
| Character_sets_dir | C:/Program File
+ -------------------------- + ----------------
8 rows in set (0.00 sec)
Mysql>

1. chinese. make sure that the character set of this field in the table is Chinese compatible:
Big5 | Big5 Traditional Chinese
Gb2312 | GB2312 Simplified Chinese
Gbk | GBK Simplified Chinese
Utf8 | UTF-8 Unicode

 

2. make sure that the join parameter is consistent with the character set of this field. you can use set name 'charsetname ';
For example, set name 'gbk ';
This command modifies both character_set_client, character_set_connection, character_set_results
(If you use Chinese characters in MySQL, you can add or modify this parameter in my. ini or my. cnf. after modifying the parameter file, you must restart the MySQL service)
[Mysqld] not [mysql]
Default-character-set = gbk

* ** Check whether duplicate default-character-set items exist.

The last time I set this, the Chinese characters inserted into the database will not be garbled.

3. PHP garbled characters, also mysql_query ("set name 'gbk'"); other APIs are similar.

 

4. garbled characters in phpmyadmin
Does $ cfg ['defaultcharset'] = 'utf-8' set in config. inc. php of phpMyAdmin ';

 

5. in Windows, run the command line ("DOS" window.
Left click on the title bar in the upper left corner of your DOS window, properties,
In the font, select "" to confirm
Set names 'gbk' in mysql ';

6. in ADO. NET and ADO, CharSet = UTF8 can be added to the connection string. Similar commands are used to describe the character set of connection.
Server = myServerAddress; Database = myDataBase; Uid = myUsername; Pwd = myPassword; CharSet = UTF8;

7. SQL Manager for MySQL

Use EMS to create a database,

Character Set to UTF-8

Client charset set UTF-8

Font charset is set to GB2312_CHARSET

8. jdbcodbc bridging http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/bridge.html

// Load the JDBC-ODBC bridge driver
Class. forName (sun. jdbc. odbc. JdbcOdbcDriver );

// Setup the properties
Java. util. Properties prop = new java. util. Properties ();
Prop. put ("charSet", "Big5 ");
Prop. put ("user", username );
Prop. put ("password", password );

// Connect to the database
Con = DriverManager. getConnection (url, prop );

9. PHP 5.2 and later versions solve the garbled problem (provided by ljf_ljf [Mark Liang)

$ Conn = mysql_connect ("192.168.1.20."," root "," 123456 ") or
Die ("cocould not connect:". mysql_error ());

$ Program_char = "utf8 ";

$ Conn. mysql_select_db ("test ");
// $ Conn. mysql_query ('set @ character_set_results = "'. $ program_char .'"');
Mysql_set_charset ($ program_char, $ conn );
$ Charset = mysql_client_encoding ($ conn );
Printf ("current character set is % s
", $ Charset );
$ Result = mysql_query ("SELECT id, task_no, pack_path FROM tb_workplan where id = 1", $ conn );
While ($ row = mysql_fetch_array ($ result, MYSQL_BOTH )){
Printf ("ID: % s
Task_no: % s
Pack_path: % s
", $ Row [" id "], $ row [1], $ row [" pack_path "]);
}
$ Conn. mysql_free_result ($ result );
$ Conn. mysql_close ();

9. garbled Stored Procedure parameters

Create procedure t (aa char (10) charset 'gbk ')

Not complete...

This article from The CSDN blog, reproduced please indicate the source: http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx