插入 Momoji 不与弹簧启动和 MariaDB 合作
原标题：Insert emoji does not work with spring-boot and MariaDB
I would like to insert emoji like ? in mariaDB database but I always get a sql error.
Here is the stacktrace:
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 [, 2018-01-12 15:01:44.454 , 2018-01-12 15:01:44.454 ,2159, 20 rue raspail
,, LEVALLOIS PERRET , , FRANCE , , 00c55854-99df-4db1-88b9-34f7e5608477 , claude , amsellem , ???? , , , 92300 , 2152ed7d-80a1-4305-9fcb-4e21f5947e32 ]
I my Maria db, the table has
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
and the field "notes" has also "utf8mb4 - Unicode" charset.
When I insert via a sql query
update customer set notes= ???? where id = f5920301-5ee0-4d58-a786-d4701d9e9d73 ;
it works, but when I want to insert emoji with my program (spring-boot, spring-data-jpa, hibernate) I always get the error (see stacktrace).
EDIT
FYI Here is the creation script
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;
EDIT 2
Here is the connection params (spring-boot 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
最佳回答
Okay, I found the problem.
The solution was to add
spring:
datasource:
connectionInitSql: "SET NAMES utf8mb4 "
in the application.yml.
connectionInitSql is used by HikariCP when it opens the connection.
问题回答
spring-boot 2.0.0.RC2
mysql 5.7.14
mysql
mysql-connector-java
org.springframework.boot
spring-boot-starter-data-jpa
Caused by: java.sql.SQLException: Incorrect string value: xF0x9Fx98xAD 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 is not work for me
i see hikari.pool
then try
spring.datasource.hikari.connectionInitSql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
it works so do
spring.datasource.hikari.connection-init-sql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
ref https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby
For Spring boot with tomcat embedded use:
spring.datasource.tomcat.initSQL = SET NAMES utf8mb4
When you are connecting, set the charset to utf8mb4. Please provide the connection details.
Use utf8mb4 on the column(s) in the table. Please provide SHOW CREATE TABLE so we can verify that the column, not just the table default is utf8mb4.
