mysql Chinese garbled solution collection

The first method:
The problem of Chinese garbled characters in MySQL 4.1
I recently upgraded MySQL 4.0 to MySQL 4.1 and discovered the problem of Chinese garbled characters. I hope the following insights will be useful to everyone.
1. MySQL 4.1 has greatly improved the text. It has the concepts of Character Set and Collation.
2. In MySQL 4.0, general programs will store text in Latin. Even if we input Chinese characters, the result will still be placed in the text column set in Latin. This is different between MySQL 4.0 and MySQL 4.0. For Ji Chu's program, there is no problem.
3. However, the system encoding of MySQL 4.1 is UTF-8 by default. When you want to restore the backup file of MySQL 4.0 to MySQL 4.1, garbled characters appear. The reason is that MySQL 4.1 converts latin codes, and then the conversion is not completely perfect, which results in a small amount of text being garbled.
Solve the garbled problem of PHP accessing MySQL 4.1
QUOTE:
The multi-language support introduced from MySQL 4.1 is really great, and some features have surpassed other database systems. However, during the test, I found that using PHP statements suitable for MySQL before 4.1 to operate the MySQL database will cause garbled characters, even if the table character set is set. After reading Chapter 10 "Character Set Support" in the new MySQL online manual, I finally found the solution and passed the test.
MySQL 4.1’s character set support (Character Set Support) has two aspects: character set (Character set) and sorting method (Collation). Support for character sets is refined to four levels: server, database, table and connection.
To view the system's character set and sorting settings, you can use the following two commands:
CODE:
mysql> SHOW VARIABLES LIKE 'character_set_%';
+------------- ------------+----------------------------+
| Variable_name | Value |
+ -----------------------------+----------------------- -----+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_system | utf8 |
| character_sets_dir | / usr/share /mysql/charsets/ |
+--------------------------+------------- -------------+
7 rows in set (0.00 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.00 sec)
The values ​​listed above are the system default values. If you are wondering why the system defaults to the Swedish sorting method of latin1, the reason is that MySQL was developed by the Swedish company T.c.
When we access the MySQL database through PHP in the original way, even if we set the default character set of the table to utf8 and send the query through UTF-8 encoding, you will find that the data stored in the database is still garbled. The problem lies in this connection layer. The solution is to execute the following sentence before sending the query:
SET NAMES 'utf8';
It is equivalent to the following three instructions:
CODE:
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8 ;
Try again, is it normal?
Just add a query after connecting
CODE:
$this->query("SET NAMES ‘utf8＇");
After reading Chapter 10 of the manual, I think the main problem is Character Sets.
The three operating variables of character_set_client, character_set_results, and character_set_connection are the key to causing garbled characters. mysql converts the query submitted by the client from character_set_client to character_set_connection
, because the query submitted by the default web page is gb2312 (can be seen in the form page meta), and mysql treats it as utf8 by default (you can find the character_set_client=utf8 at this time ), so it must be garbled. In the same way, the results returned by mysql have been converted into character_set_results encoding (it has nothing to do with the encoding of the table). The default is utf8, and the web page treats it as gb2312, so there must be garbled fields such as titles and other fields read from the database. The text in other departments is not garbled.
The above example is the utf8 character set. Use this method and set it to gbk to solve the problem.
The third method:
The ultimate solution to mysql 4.1 garbled characters.
Please note that this article is only for mysql 4.1. For other versions of mysql, please see article.
mysql4.1 is quite annoying. It supports multi-language detailed settings. In addition, phpmyadmin2.6 is also relatively stupid. The default is utf8 which cannot be changed. No matter how you make it, it will be garbled.
Okay, without further ado, let’s solve this problem step by step:
1. Modify the /etc/my.cnf file to this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/ lib/mysql/mysql.sock
default-character-set=utf8
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Note: Just add the sentence default-character-set=utf8.
2./etc/init.d/mysqld restart Restart mysql;
3. Open phpmyadmin, select lang as "China simplifies(zh-utf-8)", select "MySQL connection proofreading" as "utf8_general_ci" and click "Show" MySQL running information"-"Variables", you can see:
character set client utf8 utf8
character set connection utf8 utf8
character set database utf8 utf8
character set results utf8 utf8
character set server utf8 utf8
character set system utf8 utf8
collation connection utf8_general_ci utf8_general_ci
collation database utf8_general_ci utf8_general_ci
collation server utf8_general_ci utf8_general_ci
From here you can see that all characters have become utf8.
Someone may ask, why do we have to change it to utf8? Isn’t it possible to change it to GB2312?
The explanation is as follows:
I don’t want to change it to utf8, but phpmyadmin2.6 will only use utf8 when running mysql4.1. Even the charset of other pages is utf8. Changing it to gb2312 will definitely cause garbled characters. We can only make up phpmyadmin. .
Only in mysql3.23, phpmyadmin will have an additional page charset of gb2312, which is normal at this time.
3. Import the previous mysql3 library file into the mysql4.1 library
There are two situations:
First, import from phpmyadmin. At this time, you should pay attention to the fact that there is a "File" in the lower left foot of the page to select the library file. Character set: ", the default is utf8, it needs to be changed to gb2312, otherwise the import will be garbled;
The second is to import under Linux, then you need to add a line to the head of the library file first:
SET NAMES 'gb2312'; Note the last It’s also a ; number, don’t miss it.
Then execute mysql -u username -p password xxx.sql > library name
After the import is completed, open it with phpmyadmin and see that the Chinese characters inside are correct.
4. Export the library file from mysql4.1
1. Export using phpmyadmin
The export is not a big problem. If the Chinese displayed on the phpmyadmin browsing page is normal, then the export must be normal
2. Export on linux
It doesn’t matter if garbled characters appear when exporting with mysqldump, you can run iconv to convert it
iconv -c -f UTF-8 -t GB2312 library file name > New gb2312 library file name
In summary, you need Note:
1. Try to add SET NAMES 'gb2312' at the beginning of the library file that needs to be imported; tell mysql that what you want to import is a gb2312 file;
2. Maybe you need this:
SET NAMES 'utf8';
Use after logging in to mysql. Change some default parameters of character to utf8. Sometimes it can reduce some troubles, but it is not necessary.
Use on mysql:
SHOW VARIABLES LIKE 'character_set_%';
Used to view the current status.
3. Don’t be afraid if garbled characters appear. First, you should pay attention to retaining the original backup, and second, use iconv to convert.
Be sure to do import and export tests before normal use to ensure nothing goes wrong.
Let me explain again. Many friends on the Internet say that Mysql4.1 can only be upgraded, not downgraded. This is incorrect. Also use mysqldump to export the database by adding a --compatible parameter. Don't forget --default. -character-set=This parameter sets the character set.
Demonstration: mysqldump -uroot -pPassword --compatible=mysql40 --default-character-set=gb2312 discuz>d:discuz.sql
ok The file exported like this, It can be used in Mysql4.0. It is quite annoying, no matter how you make it, it is garbled. Now mysql5 is said to be a new era of mysql, but mysql5 downloaded and installed from the official website still has garbled characters. Here is a discussion on this phenomenon:
It is recommended that it is best to download the source code of mysql and compile it. , this is because the default supported encoding of the officially compiled mysql is the latin character set, so Chinese characters are garbled when viewed in the database. Use withcharset= encoding when compiling MySQL. You can easily compile mysql into this encoding form, so that MySQL will directly support Chinese search and sorting. The --with-extra-charsets parameter specifies other available character sets.
Download and unzip the source code package, open INSTALL-SOURCE, here is the detailed installation method under Linux, so all you need to pay attention to is the following configure command:
groupadd mysql
useradd -g mysql mysql
./configure --with-charset=gbk --with-collation=gbk_chinese_ci --with-extra-charsets=gb2312,big5,utf8,binary,ascii --prefix=/usr/local/mysql
make
make install
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql var
chgrp -R mysql .
bin/mysqld_safe --user=mysql &
bin/mysql
mysql> show variables;
You can see that the global character set parameters are all gbk, and the gb2312 character set should be included in gbk, so we will not discuss it.
Perform the same PHP operation as normal mysql4.0, you will find that Chinese garbled characters still appear.
What needs to be explained here is: Starting from mysql 4.1, the character set of the application must be specified after the mysql database is connected, otherwise the text access with non-English letters cannot be interpreted and becomes a ? sign.
The solution is to adapt to the requirements of the new version of mysql:
After connecting to the mysql database, execute set character set character set. This command can be exempted in the latest version of mysql 5 if the default character set matches the storage:
set character set gbk;
It should be written like this in php: mysql_query("set character set gbk");
The instructions can be uppercase or lowercase
phpwind and discuz are widely used domestic PHP forums. A large number of friends are using them. After understanding the above steps, you can also You can make small modifications to the forum source code and safely upgrade the database to mysql5:
Find include/db_mysql.php and modify function connect(...){.....}
Select the database mysql_select_db($dbname) ;Add the sentence mysql_query('set character set gbk'); after it to save.
2. Import and export of files: If you store non-gbk characters, you need to add a line to the head of the imported file: SET NAMES 'Character set'; and pay attention to the ; sign at the end, don't miss it.
File import and export execution
mysql -u username-p password database name
mysqldump -u username-p password database name>data.sql
It doesn’t matter if the data exported with mysqldump is garbled, you can run iconv to Convert it:
iconv -c -f utf8 -t gbk library file name > new gbk library file name
In summary, you should pay attention to:
1. Try to add set names 'gbk' at the beginning of the library file that needs to be imported; tell mysql that what you want to import is a gbk file;
2. Use show variables; or show variables like 'character_set_%'; on mysql to view the current character set status.
3. Don’t be afraid if garbled characters appear. Firstly, you should pay attention to retaining the original backup, and secondly, use iconv to convert it.
#PHP connection problem:
Due to the password hash algorithm change starting from MySQL version 4.1, the Client does not support authentication protocol problem may occur when connecting to the database
This problem does not exist in mysql 5, and mysql 5 does not require the following settings.
You can solve the problem of database user password inconsistency through the following two methods:
One: mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
Second: mysql> Update mysql.user SET Password = OLD_PASSWORD('newpwd') Where Host = 'some_host' AND User = 'some_user'
Other garbled code problems in PHP output:
If mysql is compiled into the default encoding gbk, it will cause non-gbk data to be directly imported and displayed as garbled code. For example, some characters stored in utf8, if most of your data is in the utf8 character set, of course, mysql must be compiled into the default encoding utf8.
After the release of Mysql 4.1. `mytest` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
But just changing the database collation to utf-8 is not enough. You must also set it with this statement after connecting to the mysql database: set names 'utf8'; to set it in php The program gets the correctly encoded characters and displays them in the web page.
mysql_query("set names 'utf8'",$connection);
Let me explain again. Many friends on the Internet say that Mysql4.1 can only be upgraded, not downgraded. This is incorrect. Also use mysqldump to export the database. Add a --compatible parameter. Don't forget --default-character-set=, the parameter that sets the character set.
Demonstration: mysqldump -uroot -pPassword --compatible=mysql40 --default-character-set=gb2312 discuz>d:discuz.sql
ok The file exported in this way can be used in Mysql4.0.X and Mysql.3.2. Used in version , but think about it, it still makes sense to write it down, for four reasons:
MySQL 4.1 has a big change in multi-language support (which leads to problems);
Although MySQL 3 still dominates in most places (including personal use and hosting providers); but MySQL 4.1 is the official version of MySQL Recommended databases have already been provided by hosting providers and will be provided more and more;
Many PHP programs use MySQL as the default database management software, but they generally do not distinguish between MySQL 4.1 and versions below 4.1, and generally refer to it as " "MySQL 3.xx.xx or above" can meet the installation requirements;
Because latin1 is used as the default character set in many places (the specific places will be described in detail below), it has successfully deceived many PHP program developers and users. Covering up problems that may occur in Chinese and other language environments;
Simply put, the changes in MySQL itself and the PHP program using MySQL ignore this, leading to the emergence and complexity of the problem, and because most users use English , so that this issue is not taken seriously. The PHP program mentioned here is mainly about WordPress.
The principle of character set support in MySQL 4.1. The specification of character set in MySQL 4.1 can be refined to what character set should be used for MySQL installed on a machine, one of the databases, one of the tables, and one of the columns. However, traditional Web programs do not use such complex configurations when creating databases and data tables. They use default configurations. So, where does the default configuration come from?
When compiling MySQL, a default character set is specified, which is latin1;
When installing MySQL, you can specify a default character set in the configuration file (my.ini). If not specified, this value is inherited from Specified during compilation;
When starting mysqld, you can specify a default character set in the command line parameters. If not specified, this value is inherited from the configuration file;
At this time, character_set_server is set to this default character set ;
When creating a new database, unless explicitly specified, the character set of the 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 the database;
In 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 this database;
When setting a column in the table, unless explicitly specified, the default character set of this column Set is the default character set of the table;
This character set is the character set actually used to store data in the database, and the content output by mysqldump is under this character set;
To summarize briefly, if nothing is modified, then all All fields of all tables in the database are stored in latin1. However, if we install MySQL, we usually choose multi-language support. That is to say, the installation program will automatically set default_character_set to UTF-8 in the configuration file, which ensures By default, all fields in all tables in all databases are stored in UTF-8.
When a PHP program establishes a connection with MySQL, what character set does the data sent by the program to MySQL use? MySQL has no way of knowing (it can only guess at best), so MySQL 4.1 requires the client to specify this character set, which is character_set_client. The weird thing about MySQL is that the character set obtained is not immediately converted to the character set stored in the database. That character set, but first converted to a character set specified by the character_set_connection variable; I don't quite understand what the connection layer is used for, but after converting to the character set of character_set_connection, it also needs to be converted to the default character set of the database. That is to say, it needs to be converted twice; when the data is output, it needs to be converted from the default character set of the database to the character set specified by character_set_results.
A typical environment A typical environment takes MySQL 4.1 installed on my own computer as an example. Apache 2, PHP 5 and WordPress 1.5.1.3 are installed on my own computer. The default_character_set is utf8 specified in the MySQL configuration file. So the problem arises:
WordPress is installed by default, so all tables use UTF-8 to store data;
The default browsing character set adopted by WordPress is UTF-8 (set in Options->Reading), so all WP pages The meta will indicate that the charset is utf-8;
So the browser will display all WP pages in utf-8 mode; in this way, all Posts and Comments of Write will be sent from the browser to Apache in UTF-8 format. Then Apache hands it over to PHP;
So the data WP gets from all forms is utf-8 encoded; WP sends the data directly to MySQL without conversion;
MySQL's default settings of character_set_client and character_set_connection are both latin1. At this time, something weird happened. It was actually data in UTF-8 format, which was converted "as latin1"... but it was actually converted to latin1, and then this latin1 Convert to utf-8. After these two conversions, some utf-8 characters are lost and become ??. In the final output, character_set_results defaults to latin1, which means the output is strange.
The most amazing thing is not this. If WordPress is set to read in GB2312 format, then the GB2312 encoded data sent by WP to MySQL will be "as latin1" converted and stored in the database in a strange format (really It is a strange format. You can find it after mysqldump. No matter it is read as utf-8 or gb2312, it is garbled), but if this format is output as latin1, it can actually change back to GB2312!
What phenomenon will this cause? If WP uses MySQL 4.1 database, it will be normal if the encoding is changed to GB2312. Unfortunately, this normality is just superficial.
How to solve the problem If you are impatient (almost certainly), google it, you will find that most of the answers are to execute the query before: SET NAMES 'utf8', yes, this is the solution, but The purpose of this article is to explain why this is the solution.
To ensure that the results are correct, we must ensure that the format used in the data table is correct, that is, it can at least store all Chinese characters. Then we have only two choices, gbk or utf-8. Let’s discuss the situation of utf-8 below.
Because the default_character_set set in the configuration file is utf8, the data table is created using utf-8 by default. This should be the configuration adopted by all hosting providers using MySQL 4.1. So all we have to ensure is that the specified encoding between the client and MySQL is correct.
There are only two possibilities, the client sends data in gb2312 format, or sends data in utf-8 format.
If sending in gb2312 format:
SET character_set_client='gb2312'
SET character_set_connection='utf8' or
SET character_set_connection='gb2312'
Both are acceptable, both of which can ensure that the data will not be lost during encoding conversion, that is, guaranteed What is stored in the database is the correct content.
How to ensure that the correct content is retrieved? Considering that most clients (including WP), the encoding of the data sent is the encoding of the data they expect to receive, so:
SET character_set_results='gb2312'
It can be guaranteed that the format displayed to the browser is gb2312.
If it is the second case, the client sends in utf-8 format (WP's default), you can use the following configuration:
SET character_set_client='utf8'
SET character_set_connection='utf8'
SET character_set_results='utf8'
This configuration is equivalent to SET NAMES 'utf8'.
What modifications WP should make is still the same sentence. It is impossible for the database to know exactly what coded data the client wants to send to the database. It can only let the client explain clearly. Therefore, WP must send the correct SET.. . for MySQL. What's the most appropriate way to send it? Taiwan's pLog colleagues gave some suggestions:
First, test whether the server is >= 4.1 and whether UTF-8 support is added when compiling; if yes, continue
Then test the format in which the database is stored ($dbEncoding);
SET NAMES $dbEncoding
For the second point, the situation of WP is different. According to the above typical configuration, as long as you use WP, the database must be stored in UTF-8, so it must be judged according to the user settings of browsing in GB2312 or UTF-8. (bloginfo('charset')), but this value can only be obtained after connecting to the database, so the most efficient way is to set SET NAMES once according to this configuration after connecting to the database, instead of setting it again before each query.
My modification is like this, add in wp_includes/wp-db.php:
function set_charset($charset)
{
// check mysql version first.
$serverVersion = mysql_get_server_info($this->dbh) ;
$version = explode('.', $serverVersion);
if ($version[0] < 4) return;
// check if utf8 support was compiled in
$result = mysql_query("SHOW CHARACTER SET like 'utf8'",
$this->dbh);
if (mysql_num_rows($result) < = 0) return;
if ($charset == 'utf-8' || $charset == 'UTF- 8')
$charset = 'utf8';
@mysql_query("SET NAMES '$charset'", $this->dbh);
}
In wp-settings.php require (ABSPATH . WPINC . '/ vars.php'); and then add:
$wpdb->set_charset(get_bloginfo('charset'));

1. MySQL 4.1 has greatly improved the text, it has the concepts of Character Set and Collation.
2. In MySQL 4.0, general programs will store text in Latin. Even if we input Chinese characters, the result will still be placed in the text column set in Latin. This is different between MySQL 4.0 and MySQL 4.0. For Ji Chu's program, there is no problem.
3. However, the system encoding of MySQL 4.1 is UTF-8 by default. When you want to restore the backup file of MySQL 4.0 to MySQL 4.1, garbled characters appear. The reason is that MySQL 4.1 converts latin codes, and then the conversion is not completely perfect, which results in a small amount of text being garbled.
4. It is not difficult to solve this garbled code problem. First, when backing up MySQL 4.0, first change all text fields to binary type, and then perform a normal backup. In the second step, you can restore the previous backup in MySQL 4.1. Finally, revert the text field that was changed to binay type earlier to text type again. In this way, the problem of Chinese encoding should be completely solved.
5. When changing the text field to the binay type, you must set the length of the binary column to be greater than or equal to (>=) the length of the text field, otherwise the data will be lost.
6. In addition, the MySQL database that has been upgraded in this way will work normally in MySQL 4.1, and there will no longer be garbled problems no matter how you backup and restore it.
Author: MySQL Release date: 2005-12-14
mysql4.1 is quite annoying, it supports multi-language detailed settings, and phpmyadmin2.6 is also relatively stupid. The default is utf8 that cannot be changed, and it is garbled no matter how you try it. .
Okay, without further ado, let’s solve this problem step by step:
1. Modify the /etc/my.cnf file to this:
[mysqld]
datadir=/var/lib/mysql
socket=/var /lib/mysql/mysql.sock
default-character-set=utf8
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Note: Just add the sentence default-character-set=utf8.
2./etc/init.d/mysqld restart Restart mysql;
3. Open phpmyadmin, select lang as "China simplifies(zh-utf-8)", select "MySQL connection proofreading" as "utf8_general_ci" and click "Show" MySQL running information"-"Variables", you can see:
character set client utf8 utf8
character set connection utf8 utf8
character set database utf8 utf8
character set results utf8 utf8
character set server utf8 utf8
character set system utf8 utf8
collation connection utf8_general_ci utf8_general_ci
collation database utf8_general_ci utf8_general_ci
collation server utf8_general_ci utf8_general_ci
From here you can see that all characters have become utf8.
Someone may ask, why do we have to change it to utf8? Isn’t it possible to change it to GB2312?
The explanation is as follows:
I don’t want to change it to utf8, but phpmyadmin2.6 will only use utf8 when running mysql4.1. Even the charset of other pages is utf8. Changing it to gb2312 will definitely cause garbled characters. We can only make up phpmyadmin. .
Only in mysql3.23, phpmyadmin will have an additional page charset of gb2312, which is normal at this time.
3. Import the previous mysql3 library file into the mysql4.1 library
There are two situations:
First, import from phpmyadmin. At this time, you should pay attention to the fact that there is a "File" in the lower left foot of the page to select the library file. Character set: ", the default is utf8, it needs to be changed to gb2312, otherwise the import will be garbled;
The second is to import under Linux, then you need to add a line to the head of the library file first:
SET NAMES 'gb2312'; Note the last It’s also a ; number, don’t miss it.
Then execute mysql -u username -p password xxx.sql > library name
After the import is completed, open it with phpmyadmin and see that the Chinese characters inside are correct.
4. Export the library file from mysql4.1
1. Export using phpmyadmin
The export is not a big problem. If the Chinese displayed on the phpmyadmin browsing page is normal, then the export must be normal
2. Export on linux
It doesn’t matter if garbled characters appear when exporting with mysqldump, you can run iconv to convert it
iconv -c -f UTF-8 -t GB2312 library file name > New gb2312 library file name
In summary, you need Note:
1. Try to add SET NAMES 'gb2312' at the beginning of the library file that needs to be imported; tell mysql that what you want to import is a gb2312 file;
2. Maybe you need this:
SET NAMES 'utf8';
Use after logging in to mysql. Change some default parameters of character to utf8. Sometimes it can reduce some troubles, but it is not necessary.
Use on mysql:
SHOW VARIABLES LIKE 'character_set_%';
Used to view the current status.
3. Don’t be afraid if garbled characters appear. First, you should pay attention to retaining the original backup, and second, use iconv to convert.
Be sure to do import and export tests before normal use to ensure nothing goes wrong.


I upgraded MYSQL to 4.1.2, and phpmyadmin uses 2.6.2. The Chinese fields in the data table that contain Chinese characters are all garbled, and the exported data is also garbled. I used the previous 2.5.7 without any problem. I would like to ask, which setting should be changed in the phpmyadmin file so that normal Chinese characters can be displayed?
These character-related variables are closely related to SQL:
character_set_client
character_set_connection
character_set_results
In addition, it is the character set set for the corresponding field in the database. If the field is not set, the default is the character set of the table, table If not specified, database will be used by default.
The functions of the above three variables are as follows. client represents the character set sent by the client, and results represents the character set sent to the client (these two are separated because the one sent here and the one sent over are not necessarily the same client) , connection serves as a connection between the client and the database.
This is specifically: For example, on the mysql command line, I set the client to gbk, the connection to utf8, the results to gbk, and the database to big5.
When I send an insert statement, this statement is used as a gbk code and is first converted to utf8 code ( connection), and then convert it to big5 (database) to insert into the database.
When running a select statement, the result obtained from the database goes through the opposite process, from big5 to utf8, and then to gbk, and you get the result of gbk.
So the most important thing is to make the client and results consistent with the client you are using. For example, if your web page is encoded in utf8, you need to set these two to utf8.
When using the mysql command line, I use 2000, which needs to be set to gbk
And the set names XXX we use actually sets these three variables to XXX at the same time.
In this case, we can set different tables or fields in a database to different character sets. As long as the above three settings are correct, different character sets can be used in the database at the same time.
Be careful to ensure that the characters in your database have used the correct character set. For example, if you set it wrong at the beginning, after inserting the data, the encoding of the data itself will be incorrect. Then even if the settings are changed back, it will not be correct. is shown.
Another thing is the compatibility between encodings. If a character exists in gbk but not in utf8, then in the process of gbk-》utf8-》gbk, it becomes "?"
Let me talk about it again Specific solutions.
First of all, you need to specify your upgraded database and the character set of table and field. Generally speaking, we use gb2312 or utf8. If you don’t use multiple encodings at the same time, you only need to specify the database. You can add it in the SQL statement when building the database. The corresponding character set can also be modified in phpMyAdmin.
Then import the old data. First, determine the encoding of your data file. If you use phpMyAdmin to import, there is a file encoding option on the interface, which must be consistent with the encoding of the data file.
If you import from the mysql command line, you need to set the three variables mentioned above yourself, set names xxx.
Be careful when using other client programs.
In this way, the encoding of the old data after being transferred to the new database will be correct. If this step is wrong, it will be impossible to get the correct display later.
Then it’s your own program. After connecting, you can execute set names xxx once, depending on your web page encoding.
This basically ensures that the encoding is correct.
It is very likely that the encoding of the imported data is incorrect.

The default language of the MYSQL database is Swedish, and there is a database with GB2312 characters.
The structure is OK. Why is the content garbled? Is there a way to solve the code without reinstalling the database?
The multi-language support introduced starting from MySQL 4.1 is really great, and Some features have surpassed other database systems. However, during the test, I found that using PHP statements suitable for MySQL before 4.1 to operate the MySQL database will cause garbled characters, even if the table character set is set. After reading Chapter 10 "Character Set Support" in the new MySQL online manual, I finally found the solution and passed the test.
MySQL 4.1’s character set support (Character Set Support) has two aspects: character set (Character set) and sorting method (Collation). Support for character sets is refined to four levels: server, database, table and connection.
To view the system's character set and sorting settings, you can use the following two commands:

mysql> SHOW VARIABLES LIKE 'character_set_%';
+---------------- ----------+--------------------------+
| Variable_name | Value |
+-- ------------------------+------------------------- ---+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_system | utf8 |
| character_sets_dir | / usr/share/mysql /charsets/ |
+--------------------------+--------------------------- ------+
7 rows in set (0.00 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.00 sec)

listed above The value is the system default value. (It’s strange that the system defaults to the Swedish sorting method of latin1)...
When we access the MySQL database through PHP in the original way, even if the default character set of the table is set to utf8 and the query is sent through UTF-8 encoding , you will find that the data stored in the database is still garbled. The problem lies in this connection layer. The solution is to execute the following sentence before sending the query:
SET NAMES 'utf8';
It is equivalent to the following three instructions:
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
Re Give it a try, is it normal? ^_^ Enjoy!
To be specific,
add a line before your query:
mysql_query("SET NAMES 'gb2312';",$this->con);
You should read the manual carefully.
Related to characters Among the variables, these are closely related to SQL:
character_set_client
character_set_connection
character_set_results
In addition, it is the character set set for the corresponding field in the database. If the field is not set, the default is the character set of the table, and the table is not specified. By default, database is used.
The functions of the above three variables are as follows. client represents the character set sent by the client, and results represents the character set sent to the client (these two are separated because the one sent here and the one sent over are not necessarily the same client) , connection serves as a connection between the client and the database.
This is specifically: For example, on the mysql command line, I set the client to gbk, the connection to utf8, the results to gbk, and the database to big5.
When I send an insert statement, this statement is used as a gbk code and is first converted to utf8 code ( connection), and then convert it to big5 (database) to insert into the database.
When running a select statement, the result obtained from the database goes through the opposite process, from big5 to utf8, and then to gbk, and you get the result of gbk.
So the most important thing is to make the client and results consistent with the client you are using. For example, if your web page is encoded in utf8, you need to set these two to utf8.
When using the mysql command line, I use 2000, which needs to be set to gbk
And the set names XXX we use actually sets these three variables to XXX at the same time.
In this case, we can set different tables or fields in a database to different character sets. As long as the above three settings are correct, different character sets can be used in the database at the same time.
Be careful to ensure that the characters in your database have used the correct character set. For example, if you set it wrong at the beginning, after inserting the data, the encoding of the data itself will be incorrect. Then even if the settings are changed back, it will not be correct. is shown.
Another thing is the compatibility between encodings. If a character exists in gbk but not in utf8, then in the process of gbk-》utf8-》gbk, it becomes "?"
Let me talk about it again Specific solutions.
First of all, you need to specify your upgraded database and the character set of table and field. Generally speaking, we use gb2312 or utf8. If you don’t use multiple encodings at the same time, you only need to specify the database. You can add it in the SQL statement when building the database. The corresponding character set can also be modified in phpMyAdmin.
Then import the old data. First, determine the encoding of your data file. If you use phpMyAdmin to import, there is a file encoding option on the interface, which must be consistent with the encoding of the data file.
If you import from the mysql command line, you need to set the three variables mentioned above yourself, set names xxx.
Be careful when using other client programs.
In this way, the encoding of the old data after being transferred to the new database will be correct. If this step is wrong, it will be impossible to get the correct display later.
Then it’s your own program. After connecting, you can execute set names xxx once, depending on your web page encoding.
-----------------------------------------
mysql import garbled code problem - -
When mysql uses mysqldump to export data from 4.1 and import it in 4.0, a sql statement error will occur, and all data will become garbled. Use the following parameters to solve it
mysqldump -uxunai -p --compatible=mysql40 --default-character-set=latin1 xunai>xunai.sql
mysql -uroot -p fmx < fmx3.sql -f --default- character-set=latin1

The above is the collection of solutions to mysql Chinese garbled characters. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!