MySQL Chinese garbled solution summary, mysql garbled

MySQL Chinese garbled solution summary, mysql garbled

MySQL Chinese garbled Solution

Preface:

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

1. Chinese garbled characters

1.1 Chinese garbled characters

Create table user (name varchar (11); # create a user table insert into table user ("carl"); # add data select * from user;

Insert into user value ("Haha ");

Chinese characters cannot be inserted:

1.2 View table character encoding

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

The default Character Set of the table is latin1.

Therefore, when creating a table, we need to specify the character set of the table:

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

In this way, you can access the table in Linux and insert and access the table.

1.3 database and operating system code

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

The encoding of the database also has problems.

Here we can see that character_sert_database and character_set_server both use latin1. in mysql databases, the character sets of server, database, and table are all latin1. here we will look at how to solve mysql garbled characters.

2. mysql sets the range of variables.

2.1. session range

View the database code:

show variables like '%char%';

Modify 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 to utf8. But there is a problem here, that is, we open a command window again and view the data encoding, the following picture will appear:

2.2. global scope

Mysql sets the range of variables to the default session range. If you Set the character Set of multiple sessions, you need to Set the global range: 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 will see utf8. If you think everything is fine, you are wrong.

2.3 set the global data range

When our database is restarted, you will find that the value of the global range is changed to latin1.

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

Don't be afraid. Here are the ultimate tips:

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

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

Pay attention to the location where these parameters are configured, or the mysql service may not be started:

OK. If you restart mysql, you will find that its character set is utf8.

We do not need to specify the character encoding when creating a table. The default value is utf8;

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

3. Summary

I think many answers on the Internet are to directly set mysql character encoding at the session level, which is a temporary solution. We still need to solve this problem from the source. That is, modify the default configuration file of mysql and change its character set to UTF8 that can use Chinese characters.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!