Ubuntu18.04/Mysql5.7改成utf8编码

2022-08-08

Table of Contents

## 问题背景

向MySQL插入中文内容显示问号

## 原因

Server端的MySQL不支持utf8，部分是latin1编码格式。

mysql>show variables like "%char%";

+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | latin1                     |
| character_set_connection             | latin1                     |
| character_set_database               | utf8mb4                    |
| character_set_filesystem             | binary                     |
| character_set_results                | latin1                     |
| character_set_server                 | latin1                     |
| character_set_system                 | utf8                       |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+
9 rows in set (0.02 sec)

## 解决办法

1、直接登录MySQL改数据库, 但是MySQL重启后，修改会失效。

2、修改MySQL配置

cd /etc/mysql/conf.d
vim mysql.cnf

2.1、在[mysql]添加 default-character-set=utf8

[mysql]
default-character-set=utf8

2.2、进入/etc/mysql/mysql.conf.d目录，打开mysql.conf.d，在[mysqld]添加一行 character-set-server=utf8，如下所示

cd /etc/mysql/mysql.conf.d

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
character-set-server=utf8
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1

2.3、配置成功修改后，重启MySQL服务

service mysql restart

2.4、检查编码是否修改成功

mysql>show variables like "%char%";

+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8                       |
| character_set_connection             | utf8                       |
| character_set_database               | utf8mb4                    |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8                       |
| character_set_server                 | utf8                       |
| character_set_system                 | utf8                       |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+
9 rows in set (0.02 sec)

2.5、配置成utf8生效后，再插入中文内容就可以正常显示了。