Incorrect string value: ‘\xF0\xA0\x99\xB6\xE5\xB1...‘ for column ‘ADDRESS‘ at row

问题：Incorrect string value: ‘\xF0\xA0\x99\xB6\xE5\xB1…’ for column ‘ADDRESS’ at row

变更mysql中的数据报错

java.sql.BatchUpdateException: Incorrect string value: '\xF0\xA0\x99\xB6\xE5\xB1...' for column 'ADDRESS' at row 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1823)
	at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1307)
	at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:3118)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeBatch(FilterAdapter.java:2507)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeBatch(FilterEventAdapter.java:279)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:3116)
	at com.alibaba.druid.filter.FilterAdapter.statement_executeBatch(FilterAdapter.java:2507)
	at com.alibaba.druid.filter.FilterEventAdapter.statement_executeBatch(FilterEventAdapter.java:279)
	at com.alibaba.druid.filter.FilterChainImpl.statement_executeBatch(FilterChainImpl.java:3116)
	at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.executeBatch(StatementProxyImpl.java:202)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:565)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:950)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646)
	at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:365)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:353)
	at com.unicom.microserv.fwtm.aoam.web.batch_custInfo.dao.impl.BatchCustInfoDaoImpl.batchInsert(BatchCustInfoDaoImpl.java:166)
	at com.unicom.microserv.fwtm.aoam.web.batch_custInfo.dao.impl.BatchCustInfoDaoImpl$$FastClassBySpringCGLIB$$7082bf2f.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.unicom.microserv.fwtm.aoam.web.batch_custInfo.dao.impl.BatchCustInfoDaoImpl$$EnhancerBySpringCGLIB$$ce87268b.batchInsert(<generated>)
	at com.unicom.microserv.fwtm.aoam.web.batch_custInfo.thread.VerifyTask.run(VerifyTask.java:228)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\xA0\x99\xB6\xE5\xB1...' for column 'ADDRESS' at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1801)
	... 28 common frames omitted

分析：

原因是mysql默认使用utf8存储，utf8使用三字节存储，但是你要存入的数据是四字节存储的，比如一些生僻字，表情。

解决：将表字段的字符集和数据库校验规则修改为utf8mb4

-- 将表的字符集和数据库校验规则修改为utf8mb4
ALTER TABLE `custinfo` 
CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_general_ci ;

-- 将字段的字符集和数据库校验规则修改为utf8mb4（我修改字段的时候才生效）
ALTER TABLE `custinfo` 
CHANGE COLUMN `ADDRESS` `ADDRESS` VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT '地址' ;