mysql字符集问题

报错信息

### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
### The error may involve com.ambition.business.mapper.MedicineOrderTotalMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO medicine_order_total (shop_id, shop_name, order_id, customer_name, customer_age, customer_address, need_pay, actual_pay, total_price, payment_status, pay_channel, create_user_id, create_user_name, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1

org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
### The error may involve com.ambition.business.mapper.MedicineOrderTotalMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO medicine_order_total (shop_id, shop_name, order_id, customer_name, customer_age, customer_address, need_pay, actual_pay, total_price, payment_status, pay_channel, create_user_id, create_user_name, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-2.0.0.jar!/:2.0.0]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-2.0.0.jar!/:2.0.0]
    at com.sun.proxy.$Proxy81.insert(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) ~[mybatis-spring-2.0.0.jar!/:2.0.0]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:64) ~[mybatis-plus-core-3.1.0.jar!/:3.1.0]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61) ~[mybatis-plus-core-3.1.0.jar!/:3.1.0]
    at com.sun.proxy.$Proxy147.insert(Unknown Source) ~[na:na]
    at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.save(ServiceImpl.java:105) ~[mybatis-plus-extension-3.1.0.jar!/:3.1.0]
    at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl.saveMedicineOrderTotal(MedicineOrderTotalServiceImpl.java:71) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$FastClassBySpringCGLIB$$4e30bb24.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$EnhancerBySpringCGLIB$$a6122abe.saveMedicineOrderTotal(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$FastClassBySpringCGLIB$$4e30bb24.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$EnhancerBySpringCGLIB$$20daa1f4.saveMedicineOrderTotal(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.service.impl.MedicineOrderServiceImpl.saveMedicOrder(MedicineOrderServiceImpl.java:197) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.service.impl.MedicineOrderServiceImpl$$FastClassBySpringCGLIB$$76c08402.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at com.ambition.business.service.impl.MedicineOrderServiceImpl$$EnhancerBySpringCGLIB$$f4bf0018.saveMedicOrder(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.service.impl.MedicineOrderServiceImpl$$FastClassBySpringCGLIB$$76c08402.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at com.ambition.business.service.impl.MedicineOrderServiceImpl$$EnhancerBySpringCGLIB$$9bd9e5a2.saveMedicOrder(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.controller.MedicineOrderController.save(MedicineOrderController.java:43) ~[classes!/:1.0-SNAPSHOT]
    at com.ambition.business.controller.MedicineOrderController$$FastClassBySpringCGLIB$$8d0f8652.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
    at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:55) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]

查看数据库字符情况

SHOW VARIABLES LIKE 'char%';

一看都是utf-8编码的,但是库里还是报错,就很奇怪。

表字符集:

将表字符集都改成utf8,运行起来还是报错。可以在客户端工具上直接修改,也可以使用以下语句修改:

alter table medicine_product default character set utf8;

库字符集:

将库字符集也改成utf8,还是报错,吐血!!!但是还得继续。

最后查看表创建时设置的字符集

SHOW CREATE TABLE medicine_order_total;

CREATE TABLE `medicine_order_total` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `medicine_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `medicine_code` bigint(20) DEFAULT NULL,
  `shop_id` bigint(20) DEFAULT NULL,
  `shop_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `customer_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `customer_age` int(5) DEFAULT NULL,
  `customer_address` varchar(128) CHARACTER SET latin1 DEFAULT NULL,
  `need_pay` bigint(20) DEFAULT NULL,
  `actual_pay` bigint(20) DEFAULT NULL,
  `total_price` bigint(20) DEFAULT NULL,
  `payment_status` int(2) DEFAULT NULL,
  `pay_channel` int(2) DEFAULT NULL,
  `create_user_id` bigint(20) DEFAULT NULL,
  `create_user_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

可以通过以下语句修改:

alter table medicine_order_total change medicine_order_total medicine_name varchar(50) character utf8;

其他的字段也这样处理

也可以将表删除,重新创建。

原文发布于微信公众号 - 开发架构二三事(gh_d6f166e26398)

原文发表时间:2019-09-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券