Completely solve MySQL Chinese garbled

MySQL is a very common data-type database in our project. But because we need to save Chinese characters in the database, we often encounter database garbled situation. Here is how to completely solve the database in Chinese garbled situation.

1, Chinese garbled 1.1, Chinese garbled

create table user(name varchar(11));    insert into table user("carl");         select * from user;

insert into user value("哈哈");

Cannot insert Chinese characters:

1.2. View Table character encoding

mysql> show create table user \G;*************************** 1. row ***************************       Table: user`user` (  `name` varchar(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

we can see that the default character set for the table is latin1.
So we need to specify the table's character set when we create the table:

create table user(name varchar(11)) default charset=utf8;

This can be accessed in Linux and can be inserted and accessed by this table.

1.3. Database and operating system code

Although the Chinese can be displayed on the server side normally, the client may display garbled characters. Because our server is UTF8.

And there are problems with the coding of the database.

Here we can see that the character sets of Character_sert_database and Character_set_server are latin1. So in the MySQL database, server,database, The character set for table is latin1 by default. Let's take a look at how to solve MySQL garbled situation.

2. mysql set the range of variables 2.1. Session Scope

To view the database encoding:

show variables like ‘%char%‘;

To modify the character encoding:

set character_set_server=utf8;set character_set_database=utf8;show variables like ‘%char%‘;

We can see that the character set has been modified Chengdu is UTF8. But here's the problem: we re-open a command window and look at the data encoding, and the following screen appears.

2.2. Global scope

The range of MySQL settings variables is the session range by default. If you set the character set for multiple sessions then you need to set the global scope: Set[global|session] variables ...

set global character_set_database=utf8;set global character_set_server=utf8;show variables like ‘%char%‘;

When we view the MySQL character set across sessions, we see both UTF8. If you think everything is going to be all right, then you're wrong.

2.3. Set the data global scope

When our database restarts, you find that setting the global range value becomes latin1.

service mysqld restartmysql -uroot -pyourpasswordshow variables like ‘%char%‘;

Don't be afraid, here's your ultimate trick:

Modify the MySQL configuration file/etc/my.cnf.

[mysqld]character-set-server=utf8 [client]default-character-set=utf8 [mysql]default-character-set=utf8

Note that the location of these several parameters is configured, or it may not start up MySQL service:

Ok. If you restart the MySQL service you will also find that its character set is UTF8.

And we do not need to specify the character encoding when creating the table, it is utf8 by default;

drop database test;create database test;use test;create table user(name varchar(11));show create table user \G;

3. Summary

I see many of the answers on the Internet are directly at the session level to set the MySQL character encoding, which is a temporary solution to the root of the method. We still have to solve this problem from the source. That is to modify the MySQL default configuration file, its character set to be able to use Chinese characters of the UTF8 is OK.

Completely solve MySQL Chinese garbled