场景

mysql 中文报错,如下:

  [plaintext]
1
Incorrect string value: '\xF0\xA0\xBA\x98\xE5\x8F...' for column 'title' at row 1

常用命令

  • 查看
  [sql]
1
SHOW VARIABLES LIKE 'character%'
  • 修改
  [sql]
1
2
3
4
5
6
SET character_set_client = utf8mb4 ; SET character_set_connection = utf8mb4 ; SET character_set_database = utf8mb4 ; SET character_set_results = utf8mb4 ; SET character_set_server = utf8mb4 ; SET character_set_system = utf8mb4 ;
  • 统一设置

一般就算设置了表的mysql默认字符集为utf8并且通过UTF-8编码发送查询,你会发现存入数据库的仍然是乱码。问题就出在这个connection连接层上。

解决方法是在发送查询前执行一下下面这句:

  [sql]
1
SET NAMES 'utf8';

它相当于下面的三句指令:

  [sql]
1
2
3
SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8;

解决方案

  • 注意

characterEncoding=utf8 是必须指定的,不然数据库就是 ??? 之类的数据。

  [plaintext]
1
2
3
4
5
6
7
8
datasource: # 数据库连接 url: jdbc:mysql://localhost:3306/xxx?useUnicode=true&characterEncoding=utf8 # 数据库连接用户名称 username: root # 数据库连接用户密码 password: driver-class-name: com.mysql.jdbc.Driver

需要重启

(1)设置

  [plaintext]
1
2
3
4
5
6
[mysql] default-character-set=utf8mb4 [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci

命令设置:

  [plaintext]
1
2
3
SET default-character-set = utf8mb4; SET character-set-server = utf8mb4; SET collation-server = utf8mb4_unicode_ci;

(2)重启服务

(3)设置表和列的编码为 utf8mb4

(4)在 Jdbc 连接中停止指定 characterEncoding=UTF-8characterSetResults=UTF-8

因为这会重写 character_set_client,character_set_connection, character_set_results to utf8。

实际操作

(1) 设置数据库和表的编码为 utf8mb4

  [sql]
1
ALTER SCHEMA crawl DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci ;

(2) 修改数据表的编码为utf8mb4

  [sql]
1
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4;

(3) 修改连接数据库的连接代码

比如 springboot 可以使用如下配置

  [yml]
1
2
3
4
5
6
7
8
9
10
11
spring: datasource: # 数据库连接 url: jdbc:mysql://localhost:3306/crawl?useUnicode=true&characterEncoding=utf8 # 数据库连接用户名称 username: root # 数据库连接用户密码 password: 123456 driver-class-name: com.mysql.jdbc.Driver tomcat: initSQL: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

参考资料

https://my.oschina.net/lixin91/blog/639270

https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc

  • github

https://github.com/Asqatasun/Asqatasun/issues/123

https://github.com/jaywcjlove/mysql-tutorial/blob/master/chapter2/2.3.md