Detailed explanation of database MySQL Chinese garbled solution summary (pictures and texts)

This article mainly introduces the relevant information of the databaseMySQLThe solution to Chinese garbled characters is summarized. The database saves Chinese characters, so we often encounter database garbled characters. Here are several methods. Friends who need it can Refer to the following

MySQL Chinese garbled solution

Foreword:

MySQL is a very commonly used data database in our projects . But because we need to save Chinese characters in the database, we often encounter database garbled characters. Let’s introduce how to completely solve the problem of Chinese garbled characters in the database.

1, Chinese garbled code

1.1, Chinese garbled code

 create table user(name varchar(11));  # 创建user表
 insert into table user("carl");     # 添加数据
 select * from user;

insert into user value("哈哈");

Unable to insert Chinese characters:

1.2. Check table character encoding

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

We can see that the default character set of the table is latin1.

So we need to specify the table when we create it The character set:

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

can be accessed in Linux and the table can be inserted and accessed.

1.3. Database and operating system encoding

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

And there are also problems with the encoding of the database.

Here we can see that the character sets of character_sert_database and character_set_server are both latin1. Then in mysql database, the character sets of server, database and table are all The default is latin1. Let's take a look at how to solve the mysql garbled situation.

2. Mysql settingsThe scope of variables

2.1.sessionScope

View database encoding :

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, if we reopen a command window and check the data encoding, the following screen will appear:

2.2, global range

The scope of mysql setting variables defaults to the session scope. If you set the character set of multiple sessions, 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 across sessionsmysql character setYou will see that they are all utf8. If you think everything is fine, you are totally wrong.

2.3. Set the global scope of data

When our database restarts, you find that the value of setting the global scope becomes latin1 again.

service mysqld restart
mysql -uroot -pyourpassword
show variables like '%char%';

Don’t be afraid, the following will teach you the ultimate trick:

Modify mysqlConfiguration file/etc/my.cnf.

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

Please pay attention to the location of these parameter configurations, otherwise the mysql service may not be started:

OK. Now 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 defaults to utf8;

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

3. Summary

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

The above is the detailed content of Detailed explanation of database MySQL Chinese garbled solution summary (pictures and texts). For more information, please follow other related articles on the PHP Chinese website!