欢迎光临
感受代码之美

mysql插入生僻字/表情符号Incorrect string value错误解决

mariadb.jpg

环境信息

  • 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\

这样就插入数据成功。

  1. SpringBoot配置属性之DataSource
  2. MySql 中文写入数据库乱码及Incorrect string value: ‘\xF0\x9F…’ for column ‘XXX’ at row 1解决
  3. 如何在mysql中存取utf8mb4编码的字符
转载请注明来源:四个空格 » mysql插入生僻字/表情符号Incorrect string value错误解决

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址