MySQL - 解决数据库插入中文发生错误问题

问题描述

在向 MySQL 数据库中插入 utf-8 编码格式的中文字符时，由于数据库的字符编码为 latin1 的原因，就会出现如下异常

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x9C' for column 'content' at row 1.

此时，说明由于编码不一致的问题，导致该字段无法正确插入中文字符

解决思路

将数据库的编码转换为 utf-8，并修改已有数据表以及数据字段的字符编码，以确保中文字符能够正确插入

解决方法

一、定位 my.cnf 文件配置

find / -name my.*

/etc/my.cnf.d
/etc/my.cnf
/etc/my.cnf.rpmsave

二、修改 my.cnf 文件内容，添加配置其默认编码格式

vim /etc/my.cnf

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8

三、重启 mysql 服务

systemctl restart mysqld

四、查看当前数据库系统变量是否为 utf8 格式

show variables like '%character_set%';

+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

此时新建的数据库会默认采用 utf8 的格式

五、查看已有数据表的编码格式

show create table black_list;

+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                  |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| black_list | CREATE TABLE `black_list` (
  `user_id` varchar(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------+

此时可以看到，对于已经存在的数据表，其编码格式仍为 latin1，因此需要手动更改其编码格式为 utf8

六、更改数据表的编码格式为 utf8

alter table black_list default character set utf8;

此时再次查看数据表编码格式时发现，虽然其表的编码格式变为 utf8，但是字段列仍为 latin1 编码格式，因此，还需要手动将每个需要更改的字段的编码格式进行修改

+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                  |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| black_list | CREATE TABLE `black_list` (
  `user_id` varchar(20) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------+

七、更改字段列的编码格式为 utf8

alter table black_list modify column user_id varchar(20) CHARACTER SET utf8 NOT NULL;

此时就可以顺利插入 utf-8 格式的中文字符了

最后编辑于：2020.09.28 15:54:03