Mysql garbled solution, mysql garbled

Mysql garbled solution, mysql garbled
How can I solve the problem of Chinese garbled MYSQL Data?

The first method is summarized as follows:

The VM is often changed, and the MYSQL versions of different service providers are different. After data is imported, garbled characters and other problems cannot be displayed normally. I checked a lot of information and summarized my skills:

This is the case when importing data in WINDOWS.
Use MYSQL commands
Run the doscommand to enter the mysql bin directory and enter the mysql-uroot-p Password Database Name <the database to be restored. For example, we need to name disk D as test. the SQL database is restored to the local database test2, so mysql is used for foreign hosts before Mysql-uroot-p password test2. x series, I feel better, no matter GBK and UTF-8 are not garbled, did not expect the new host Mysql is 5.0 version, after importing data, read out with Php is question mark, garbled text. I remember that I had a previous experience with Garbled text during Switching. The reason must be the difference between Mysql versions.

I had to check the information and found a solution. I added a set names UTF8 sentence after mysql_connect to eliminate garbled characters in the UTF8 database. For GBK databases, I used SET NAMES GBK, the Code is as follows:

$ Mysql_mylink = mysql_connect ($ mysql_host, $ mysql_user, $ mysql_pass );
Mysql_query ("set names 'gbk '");

 

The database character set is UTF-8.

The connection statement uses this
Mysql_query ("set names 'utf8 '");
Mysql_query ("set character set UTF8 ");
Mysql_query ("SET CHARACTER_SET_RESULTS = utf8 '");

Another way is to change the MYSQL character set if your machine is running. It is generally valid in MYSQL 4 and MYSQL 5.

 

 

 

Method 2: A good explanation. Try it.

Summary of mysql garbled processing:

(1) Chinese characters are processed normally in java. Displaying garbled characters in the cmd client is a problem with character sets.

(2) The length of the field is long enough. However, when a Chinese character is inserted, the system prompts com. mysql. jdbc. MysqlDataTruncation: Data truncation: Data too long for column.

(3) garbled characters are the final issue of character sets. We should consider the following aspects from Character Set settings: server, client, database, connection, and results.

------------------------- Solution ----------------------

(1) modify the my. ini (MySQL Server Instance Configuration file)

# CLIENT SECTION

[Client]

Port = 3306

[Mysql]

Default-character-set = gbk


# SERVER SECTION

[Mysqld]

Default-character-set = gbk

(2) modify the db. opt configuration file under the corresponding database directory in the data Directory

Default-character-set = gbk
Default-collation = gbk_chinese_ci

(3) Character Set specified in the database connection string

URL = jdbc: mysql: // yourIP/college? User = root & password = yourPassword & useUnicode = true & characterEncoding = gbk Note: useUnicode = true & characterEncoding = gbk must be added to MySQL 5.0 and later, adding or not does not matter!

(4) Specify the character set when creating a database

Create database yourDB character set gbk;

(5) On the doscommand, Set

Set names gbk

(6) When installing the database, specify the character set, such as the character set specified as the UTF-8 ,:

Okay. Check the above four settings. It should be OK! Such
Com. mysql. jdbc. MysqlDataTruncation: Data truncation: Data too long for column.

 

Mysql Chinese garbled Solution
I often see mysql garbled characters in the group, but I don't care much because I have been using oracle and SQL server, I used mysql yesterday and met the legendary Garbled text ...... Now let's talk about mysql garbled characters. First you enter your mysql
Use show variables to view

Here we can see your character set. character_set_server is the server code. Now, the default value is that latin1 must restart the server to change the server encoding. We will first use mysqladmin-uroot shutdown to close the server, then mysqld-c gbk starts the server-C is a shorthand for character_set_server (you can also write it as mysqld-character_set_server gbk ). after the modification, we can go to mysql to view the result:

In this way, we can change the server and database encoding to GBK.
Now we can insert a Chinese string, but why is there time?
Exception in thread "main" com. mysql. jdbc. MysqlDataTruncation: Data truncation: Data too long for column 'name' at row 1
This is because you only modified the character encoding of the mysql server and did not modify the encoding of the database. At this time, you need to manually change the encoding in db. opt in the database under the data Directory
Default-character-set = gbk
Default-collation = gbk_chinese_ci
In this way, you can insert Chinese characters After restarting the server.
But there is another problem at this time, that is, the Chinese characters are displayed in your application, but garbled characters are still displayed in the Command window.
At this time, you should pay attention to the character_set_client character or latin1 shown in the figure above. Of course, it is garbled. Therefore, when you enter the mysql client program, you must change the default encoding of the client to gbk to display it normally.
You can modify it in mysql or mysql-uroot-default-character-set = gbk at login.
So far, the Chinese problem has been solved.
At the beginning, I added a characterEncoding variable () to the program, but I thought it was useless when I thought about it, so I can still display it normally after I removed it.
Jdbc: mysql: // 127.0.0.1: 3306/ee? CharacterEncoding = gbk"

 

The third method is too complicated. I don't know how to do it. You can try it. Maybe it can be used.

1. Modify the/etc/my. cnf file as follows:
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/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. pid
Note: A default-character-set = utf8 statement is added.
2./etc/init. d/mysqld restart to restart mysql;
3. open phpmyadmin, select "Chines simplifies (zh-utf-8)" for lang, and select "utf8_general_ci" point "display MySQL 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, we can see that character has all changed to utf8.
Someone may ask, why do they all need to be changed to utf8? Can it be changed to GB2312?
Explanation:
I also don't want to change it to utf8, but phpmyadmin2.6 only uses utf8 when mysql4.1 is used. Even the charset on other pages is also utf8. If it is changed to gb2312, it will be garbled. We can only get together phpmyadmin.
Only when mysql3.23 is used, phpmyadmin will have an additional gb2312 page charset, which is normal at this time.
3. Import the previous mysql3 library file to the library of mysql4.1
There are two scenarios:
First, it is imported from phpmyadmin. At this time, you should note that there is a "file Character Set:" at the bottom left of the page for selecting library files. The default value is utf8, which must be changed to gb2312, otherwise, garbled characters are imported;
The second is to import data in linux. At this time, you need to add a line in the header of the library file:
Set names 'gb2312'; pay attention to.
Then execute mysql-u username-p password xxx. SQL> Database Name
After the import is complete, use phpmyadmin to open the file. The text in the file is correct.
4. Export a file from mysql4.1
1. Export with phpmyadmin
Export is not a problem. If the Chinese characters displayed on the phpmyadmin browser page are normal, export is certainly normal.
Ii. Export data on linux
If garbled characters are exported using mysqldump, 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, pay attention to the following:
1. Try to add set names 'gb2312' at the beginning of the library file to be imported; tell mysql that you want to import a gb2312 file;
2. You may need this:
Set names 'utf8 ';
After logging on to mysql, you can use it to change some default character parameters to utf8, which can reduce some troubles, but is not necessary.
Use on mysql:
Show variables like 'character _ set _ % ';
Used to view the current status.
3. Do not be afraid of garbled characters. First, you should pay attention to retaining the original backup, and second, use iconv for conversion.
Before normal use, perform Import and Export tests to ensure that nothing is lost.

I upgraded MYSQL to 4.1.2, and phpmyadmin uses 2.6.2. The Chinese fields in the data table are garbled, And the exported data is garbled. I have no problem with the previous 2.5.7. I would like to ask, which setting should I change in the phpmyadmin file to display normal Chinese text?
These variables related to characters are related to SQL:
Character_set_client
Character_set_connection
Character_set_results
In addition, it is the charact set for the corresponding fields in the database. If no field is set, it is the charact set of the table by default. If no table is specified, the database is used by default.
The preceding three variables are used in this way. The client indicates the character set sent by the client, results indicates the character set sent to the client (the two are separated because the sent and sent are not necessarily the same client), and connection acts as a connection between the client and the database.
For example, in the mysql command line, Set client to gbk, connection to utf8, results to gbk, and database to big5,
When I send an insert statement, this statement is converted into utf8 code (connection) and big5 (database) for database insertion as gbk code.
When running a select statement, the result obtained from the database is the opposite process, from big5 to utf8, and then to gbk. You get the result 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 webpage is utf8 encoded, you need to set these two to utf8.
In mysql command line, I use 2000 and set it to gbk.
The set names XXX we use is actually setting 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 three above settings are correct, you can use different character sets in the database.
Make sure that the characters in your database have used the correct character set. For example, if you set an error at the beginning, the data encoding is incorrect after the data is inserted, then, even if the settings are changed back, they cannot be correctly displayed.

Hope to help you