1. MySQL 4.1 has greatly improved its text, and it has the concept of Character Set and Collation.
2. In MySQL 4.0, general programs will store text in Latin (latin). Even if we enter Chinese characters, the result will still be placed in the text column set in Latin. This will not be a problem for MySQL 4.0 and programs based on MySQL 4.0.
3. However, the system encoding of MySQL 4.1 is preset to use UTF-8. When you want to restore the backup file of MySQL 4.0 to MySQL 4.1, garbled code appears. The reason is that MySQL 4.1 converts the latin code, and the conversion is not completely perfect, which leads to the phenomenon of garbled code in a small amount of text.
4. It is not difficult to solve this garbled problem. First, when backing up MySQL 4.0, first turn all text bars into binary type, and then perform normal backup. The second step is to backup the previous restore in MySQL 4.1. Finally, the text column that was changed to the binay type earlier and restored to the text type again. In this way, the problem of Chinese encoding should be completely solved.
5. When changing the text column to the binary type, it is necessary to set that the length of the binary column is greater than or equal to the length of the (>=) text column, otherwise the data will be lost.
6. In addition, the MySQL database upgraded in this way will work normally in MySQL 4.1, and there will be no garbled problems even if it is backup and restore.
Author: MySQL Release Date: 2005-12-14
MySQL4.1 is quite annoying and supports multi-language detailed settings. In addition, phpmyadmin2.6 is also stupid. By default, it is utf8 that cannot be changed, and it is garbled no matter how you do it.
OK, let's talk about nonsense, let's solve this problem step by step:
1. Modify the /etc/ file and change it to this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/
default-character-set=utf8
[]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/
pid-file=/var/run/mysqld/
Note: It is to add a sentence default-character-set=utf8.
2./etc//mysqld restart Restart mysql;
3. Open phpmyadmin, select lang as "Chinese simplifies(zh-utf-8)", and select "MySQL connection proofreading" as "utf8_general_ci "Tap" to display MySQL's running information--"Variable", 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 the characters have all become utf8.
Someone wants to ask, why do we need to change it to utf8? Isn't it OK to change to GB2312?
The explanation is as follows:
I don't want to change it to utf8, but phpmyadmin2.6 only uses utf8 when it is mysql4.1. Even the charsets of other pages are utf8. If you change it to gb2312, it will definitely be garbled. We can only use phpmyadmin.
Only in mysql3.23, phpmyadmin will have an additional gb2312 page charset, which is normal at this time.
3. Import the previous mysql3 library files into the mysql4.1 library
There are two situations:
First, import from phpmyadmin. At this time, you should pay attention to the "File Character Set:" on the lower left foot of the page where the library file is selected. The default is utf8, and it must be changed to gb2312, otherwise the garbled code will be imported;
The second is to import it in Linux. At this time, you need to add a line to the head of the library file:
SET NAMES 'gb2312'; Note that the last number is also; don't miss it.
Then execute mysql -u username -p password > library name
After the import is completed, open it with phpmyadmin. The Chinese characters inside are correct.
4. Export library files from mysql4.1
1. Export using phpmyadmin
Export is not a big problem. If the Chinese displayed on the browsing page of phpmyadmin is normal, then exporting is definitely normal.
2. Export on linux
If the export of mysqldump is garbled, it doesn't matter. You can run iconv to convert it.
iconv -c -f UTF-8 -t GB2312 library file name > New gb2312 library file name
To sum up, you need to pay attention to:
1. Try to add SET NAMES 'gb2312' at the beginning of the library file you need to import; tell mysql that you are importing a gb2312 file;
2. Maybe you need this:
SET NAMES 'utf8';
After logging in to mysql, change some default parameters of character to utf8, which can sometimes 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 there is garbled code. First, you should pay attention to retaining the original backup, and second, use iconv to convert.
Before normal use, please pay attention to import and export testing to ensure that everything is foolproof.
Finally, add one sentence: Original article, please indicate the source when reprinting. hehe
Email: support@
Author: MySQL Release Date: 2005-12-14
I upgraded MYSQL to 4.1.2, and phpmyadmin uses 2.6.2. The fields in the data table that have Chinese are all garbled in Chinese, and the exported data is also garbled in garbled. I have no problem using the previous 2.5.7. I would like to ask, which setting should I change in the phpmyadmin file to display normal Chinese characters?
Among the variables related to characters, these are very related to SQL:
character_set_client
character_set_connection
character_set_results
In addition, the character set set for the corresponding fields in the database. If the field is not set, the default is the character set of the table, and the table is not specified, the database is used by default.
The function of the above three variables is as follows: client represents the character set sent by the client, results represent the character set sent to the client (these two are separated because the sent and sent past are not necessarily the same client), and connection plays a connection role in the client and the database.
The specific one is as follows: For example, on the mysql command line, I set the client to gbk, the connection to utf8, the results to gbk, and the database is big5,
When I send an insert statement, this statement is used as gbk code, first converted to utf8 code (connection), and then converted to big5 (database) and inserted into the database.
When running a select statement, the results obtained from the database are the opposite process, from big5 to utf8, and then to gbk, and you get the results of gbk.
Therefore, 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 utf8 encoding, you need to set these two to utf8.
And on the mysql command line, I used 2000 and needed to set it to gbk
The set names XXX we use is actually setting these 3 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 3 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 the wrong settings at the beginning, after inserting the data, the encoding of the data itself will be incorrect, and even if the settings are changed back, it will not be possible to get the correct display.
Another is the compatibility between encodings. If a character is found in gbk and not in utf8, then in the process of gbk-》utf8-》gbk, it becomes "?"
Let me talk about the specific solution.
First, you need to specify your upgraded database, table and field character set. Generally speaking, we use gb2312 or utf8. If we do not use multiple encodings at the same time, just specify the database. You can add the corresponding character set to the SQL statement for building the library, and it can also be modified in phpMyAdmin.
Then import the old data. First, you need to determine the encoding of your own data file. If you import it with phpMyAdmin, there is an option for file encoding on the interface, which must be consistent with the encoding of the data file.
If you import from the command line of mysql, you must set the three variables mentioned above by yourself, set names xxx.
Be careful when using other client programs.
This will allow the encoding of the old data to be transferred to the new database. If this step is wrong, it will not be possible to get the correct display later.
Then there is 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 imported data encoding is already wrong.
Reprinted from: /blog/p/
The default language of MYSQL database is Swedish, and there is a GB2312-character database.
The structure is OK. Why is the content garbled? Is there a way to solve the code without reinstalling the database?
Multilingual support introduced since 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 4.1 before operating MySQL databases will cause garbled code, even if the table character set is set. I finally found the solution and passed the test after reading the tenth chapter of "Character Set Support" in the new MySQL online manual.
MySQL 4.1's character set support has two aspects: character set and sorting method. The support for character sets is refined to four levels: server, database, data table and connection.
To view the system's character set and sorting method 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_server | 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. (It's strange how the system defaults to latin1's Swedish sorting method)...
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 stored database is still garbled. The problem lies in this connection 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;
Try it again, isn't it normal? ^_^ Enjoy!
Specifically
Add a line before your query:
mysql_query("SET NAMES 'gb2312';",$this->con);
I should really read the manual carefully.