插入表情符号不适用于Spring-Boot和MariaDB [英] Insert emoji does not work with spring-boot and MariaDB

问题描述

我想在MariaDB数据库中插入像😃这样的表情符号，但总是收到Sql错误。

堆栈跟踪如下：

12-01-2018 16:01:44.466 [Executor - Migration - 1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions:129 - SQL Error: 1366, SQLState: 22007
12-01-2018 16:01:44.466 [Executor - Migration - 1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions:131 - (conn:498) Incorrect string value: 'xF0x9Fx92xB3xF0x9F...' for column 'notes' at row 1
Query is: insert into customer (backend_archiving_date, backend_creation_date, backend_update_date, genius_client_id, address, birthday, city, company_name, country, email, fidelity_account_id, first_name, last_name, notes, phone, siret, zip_code, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters [<null>,'2018-01-12 15:01:44.454','2018-01-12 15:01:44.454',2159,'20 rue raspail
',<null>,'LEVALLOIS PERRET','','FRANCE','','00c55854-99df-4db1-88b9-34f7e5608477','claude','amsellem','💳💳💳💳','','','92300','2152ed7d-80a1-4305-9fcb-4e21f5947e32']

我的Maria数据库，桌子上有

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

"NOTES"字段也有"utf8mb4-unicode"字符集。

通过SQL查询插入时

update customer set notes='💳💳💳💳' where id = 'f5920301-5ee0-4d58-a786-d4701d9e9d73';

它是有效的，但当我想在我的程序中插入emoji表情符号(Spring-Boot、Spring-Data-JPA、Hibernate)时，我总是收到错误消息(参见stacktrace)。

编辑

仅供参考，这里是创建脚本

CREATE TABLE `customer` (
`id` char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`first_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`notes` varchar(2048) DEFAULT NULL,
`address` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`country` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`zip_code` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`company_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`siret` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`fidelity_account_id` char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`backend_creation_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`backend_update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`backend_archiving_date` timestamp NULL DEFAULT NULL,
`genius_client_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `fk_customer_fidelity_account` (`fidelity_account_id`),
CONSTRAINT `fk_customer_fidelity_account` FOREIGN KEY (`fidelity_account_id`) REFERENCES `fidelity_account` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

编辑%2

这里是连接参数(SpringBoot应用程序.yml)

# Datasource configuration
spring:
  datasource:
    type: "com.zaxxer.hikari.HikariDataSource"
    url: "jdbc:mariadb://localhost:3306/genius_back useUnicode=yes&characterEncoding=UTF8"
    hikari:
      driver-class-name: "org.mariadb.jdbc.Driver"
      username: "mariadb"
      password: "mariadb"
      minimum-idle: 5
      maximum-pool-size: 20
      validation-query: "SET NAMES utf8mb4"
  jackson:
     serialization:
      write_dates_as_timestamps: false
  http:
     client-user-agent: "Genius"
     multipart:
      max-file-size: 100Mb
      max-request-size: 150Mb

推荐答案

好的， 我找到了问题所在。

解决方案是添加

spring:
  datasource:
     connectionInitSql: "SET NAMES 'utf8mb4'" 

在应用程序.yml.

connectionInitSql由HikariCP在打开连接时使用。

这篇关于插入表情符号不适用于Spring-Boot和MariaDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！