mysql进阶-修改linux服务器中MySQL的字符集

1.背景

linux中mysql8默认的字符集是latin1，在插入中文时会报错，所以一般在配置好mysql时需要修改字符集为utf8【又叫utfmb3,一般开发够用，一个字符用3个字节表示】或者utfmb4【一个字符用4个字节表示，如果存储emoji表情，必须用utfmb4】

1366 - Incorrect string value: xE4 xBA xBA(xE7 x94 x9F’ for column ‘book name’ at row 1

查看当前数据库的字符集

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

发现默认是latin1

2. 修改字符串为utf8

• 修改/etc/my.cnf配置文件
• 重启mysql
• 重新查看字符集

2.1 修改/etc/my.cnf配置文件

[root@VM-4-6-centos /]# cd etc
[root@VM-4-6-centos etc]# vim my.cnf

进入到编辑页面

保存并退出

2.2 重启mysql

systemctl restart mysqld.service

2.3 重新查看字符集

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

大功告成！！！