How to Fully Resolve MySQL Chinese Character Encoding Issues

MySQL is a frequently used relational database in our projects, but storing Chinese characters often results in garbled text; this article explains how to completely solve the MySQL Chinese encoding problem.

1. Chinese Garbled Text

1.1 Example

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

Attempting to insert Chinese characters fails:

1.2 Check Table Character Set

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

The default character set of the table is latin1 , so we must specify UTF‑8 when creating tables:

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

After this, the table can be accessed and Chinese data can be inserted on Linux.

1.3 Database and OS Encoding

Even if the server displays Chinese correctly, the client may still see garbled text because the server uses UTF‑8 while the database defaults to latin1.

Both character_set_database and character_set_server are latin1, so the whole MySQL environment defaults to latin1.

2. MySQL Variable Scope Settings

2.1 Session Scope

Check current character‑set variables:

show variables like '%char%';

Modify the session character set to UTF‑8:

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

After restarting the client, the session variables revert to latin1, indicating that session changes are not persistent.

2.2 Global Scope

To affect all sessions, set the global variables:

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

Now every new session sees UTF‑8, but the settings still disappear after a MySQL restart.

2.3 Persistent Global Settings

When MySQL is restarted, global values revert to latin1. The permanent solution is to edit the MySQL configuration file /etc/my.cnf :

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

Be careful to place these parameters in the correct sections; otherwise MySQL may fail to start.

After restarting the service, the character set is UTF‑8, and newly created tables default to UTF‑8 without needing explicit charset specifications.

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

3. Summary

Most online solutions only adjust the session‑level character set, which is a temporary fix. The proper way is to modify MySQL’s default configuration file to set the character set to UTF‑8, thereby permanently enabling Chinese character storage.

Original source: blog.csdn.net/u012410733/article/details/61619656