文章/答案/技术大牛

发布

社区首页 >问答首页 >插入表情符号不适用于spring-boot和MariaDB。

问插入表情符号不适用于spring-boot和MariaDB。
EN

Stack Overflow用户

提问于 2018-01-12 15:19:02

回答 4查看 7.1K关注 0票数 5

我想在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: '\xF0\x9F\x92\xB3\xF0\x9F...' 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']

我的玛丽亚分贝，桌子上有

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

字段"notes“也有"utf8mb4 - Unicode”字符集。

当我通过sql查询插入

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

它可以工作，但是当我想在我的程序(spring，spring 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

下面是连接参数(spring application.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-boot

utf-8

mariadb

utf8mb4

回答 4

Stack Overflow用户

回答已采纳

发布于 2018-01-15 10:26:32

好吧，我发现问题了。

解决办法是增加

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

在application.yml里。

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

票数 10

Stack Overflow用户

发布于 2018-03-22 06:16:35

spring-boot 2.0.0.RC2

mysql 5.7.14

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>


Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xAD' for column 'title' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
... 68 more

spring.datasource.connectionInitSql不是为我工作的

我看到hikari.pool了

那就试试

spring.datasource.hikari.connectionInitSql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

它也是这样做的

spring.datasource.hikari.connection-init-sql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

参考文献https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby

票数 7

Stack Overflow用户

发布于 2018-02-09 11:29:56

对于带有tomcat嵌入式的Spring引导，请使用：

spring.datasource.tomcat.initSQL = SET NAMES 'utf8mb4'

票数 4

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/48229141

复制

相似问题

问插入表情符号不适用于spring-boot和MariaDB。
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入表情符号不适用于spring-boot和MariaDB。EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入表情符号不适用于spring-boot和MariaDB。
EN