环境信息
- Windows 10;
- mysql 5.6;
- MariaDB 10.4;
错误信息
### SQL: insert into author (ID, NAME, `DESC`, `TYPE`) values (?, ?, ?, ?)
### Cause: java.sql.SQLException: Incorrect string value: '\xF0\xA9\x94\xAA' for column 'NAME' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\xA9\x94\xAA' for column 'NAME' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xF0\xA9\x94\xAA' for column 'NAME' at row 1] with root cause
错误原因及解决方式
这是因为插入语句中包含生僻字,解决的办法是更改数据库的字符编码,即,修改mysql的配置,配置文件的位置,运行services.msc
查看mysql服务的属性。
开始我mysql的版本是mysql 5.6版本,修改my.ini
内容,增加下面内容:
[mysqld]
character-set-server=utf8mb4 #增加内容
结果是修改后不生效,服务已经重启。于是,我将mysql修改为MariaDB 10.4
,然后将配置文件my.ini
更改为下面内容:
[mysqld]
datadir=D:/Programs/MariaDB 10.4/data
port=3306
innodb_buffer_pool_size=1014M
character-set-server=utf8mb4
[client]
port=3306
plugin-dir=D:/Programs/MariaDB 10.4/lib/plugin
然后spring boot配置文件数据源配置如下:
spring:
datasource:
name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/shiwo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong
username: root
password: Passw0rd
connection-init-sql: set names utf8mb4
执行SQL命令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 utf8mb4
character_set_system utf8
character_sets_dir D:\Programs\MariaDB 10.4\share\charsets\
这样就插入数据成功。