How to solve mysql Chinese garbled characters-mysql tutorial

Mysql Chinese garbled characters may occur in various ways, such as Chinese garbled characters, reading garbled characters, writing database garbled characters, and importing Chinese garbled characters. I will introduce them to you below, to solve the problem of mysql Chinese garbled characters, you can refer Modify mysql configuration to solve garbled characters

First:

Use show variables like "% colla %"; show varables like "% char %"; these two commands to view the character set settings of the database and the server

If you see it is gbk2312, or gbk, then only supports Simplified Chinese, traditional and some special symbols can not be inserted, we only modify the character set to UTF-8,

The modification method is as follows:

Use Notepad or UitraEdit to open my. open the INI file and press Ctrl + F to search for default-character-set and change the character set to UTF8. Note that you need to modify the character set in two places: client and server.

Save and restart the mysql service. go in and continue using show variables like "% colla %"; show varables like "% char %"; query the character set with two statements.

Encoding settings for data tables and connections

Set database and data table encoding

To solve the garbled problem, you must first find out the encoding used by the database and data table. If not specified, the default value is latin1.
The most commonly used character sets are gb2312, gbk, and utf8.
How to specify the character set of databases and data tables? The following example uses gbk:
[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)

Among them, the 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.

You can also use the following command to modify the character set of the database data table:

alter database mysqlcode default character set 'gbk'.

The encoding of servers, databases, and data tables has been set before. the encoding in the database is gbk, and Chinese characters can be stored.
However, if you want to execute insert or select operations, Chinese garbled characters will still occur, because the encoding of "connection" has not been set, database operations, such as insert and select, all include database connection actions. If you do not believe it, you can execute the following SQL statement to try it:

Mysql> insert into mysqlcode values (null, 'Code sharing ');

Set connection encoding

The server, database, and data table encoding must be set. The connection encoding settings are as follows:

mysql> SET character_set_client='gbk';mysql> SET character_set_connection='gbk'mysql> SET character_set_results='gbk'

After the connection encoding is set, you can successfully insert Chinese characters below:

Mysql> insert into mysqlcode values (null, 'Java hobby'); Query OK, 0 rows affected (0.02 sec)

In fact, the preceding three commands for connection encoding can be simplified to one:

mysql> set names 'gbk';

After the connection encoding is set, Chinese characters can be correctly displayed during the select query:

Mysql> select * from mysqlcode; + ---- + ----------- + | id | content | + ---- + ----------- + | 1 | java hobbies | + ---- + ----------- + 1 row in set (0.00 sec)

Query mysql Chinese record garbled

Here we will introduce the gb2312 garbled code as an example.

If you want to use gb2312 encoding, we recommend that you use latin1 as the default character set of the data table so that you can insert data directly in the command line tool in Chinese and display it directly. do not use the gb2312 or gbk character sets. if you are worried about query sorting and other issues, you can use the binary attribute constraints, such:

create table my_table ( name varchar(20) binary not null default'')type=myisam default charset latin1;

Appendix: Methods for upgrading old data
Take the original character set latin1 as an example to upgrade it to the utf8 character set.

Original Table: old_table (default charset = latin1), new table: new_table (defaultcharset = utf8 ).

Step 1: Export old data

mysqldump --default-character-set=latin1 -hlocalhost -uroot -Bmy_db --tables old_table > old.sql

Step 2: convert the encoding

iconv -t utf-8 -f gb2312 -c old.sql > new.sql

Here, we assume that the original data is gb2312 by default.

Step 3: Import

Modify old. SQL and add an SQL statement: "SET NAMES utf8;" to save.

mysql -hlocalhost -uroot my_db < new.sql

Success !!

Mysql data import garbled

Garbled statement: mysql-hlocalhost-uroot-proot test

Correct statement: mysql-hlocalhost-uroot-proot test

Solve the problem of garbled data during mysql import and export

The first thing to do is to determine the encoding format of the exported data. when using mysqldump, add -- default-character-set = utf8, for example, the following code:

mysqldump   -uroot  -p  --default-character-set=utf8   dbname tablename  >  bak.sql

When importing data, use -- default-character-set = utf8:

mysql -uroot -p --default-character-set=utf8 dbname < bak.sql

In this way, the unified encoding solves the garbled characters in mysql data migration.