暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / mysql字符集问题

mysql字符集问题

开发架构二三事 2019-09-08
542


报错信息

  1. ### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
  2. ### The error may involve com.ambition.business.mapper.MedicineOrderTotalMapper.insert-Inline
  3. ### The error occurred while setting parameters
  4. ### 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  5. ### Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
  6. ; 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

  8. org.springframework.jdbc.UncategorizedSQLException: 
  9. ### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
  10. ### The error may involve com.ambition.business.mapper.MedicineOrderTotalMapper.insert-Inline
  11. ### The error occurred while setting parameters
  12. ### 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  13. ### Cause: java.sql.SQLException: Incorrect string value: '\xE5\xBA\x97\xE9\x93\xBA...' for column 'shop_name' at row 1
  14. ; 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
  15.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  16.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  17.     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  18.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-2.0.0.jar!/:2.0.0]
  19.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-2.0.0.jar!/:2.0.0]
  20.     at com.sun.proxy.$Proxy81.insert(Unknown Source) ~[na:na]
  21.     at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) ~[mybatis-spring-2.0.0.jar!/:2.0.0]
  22.     at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:64) ~[mybatis-plus-core-3.1.0.jar!/:3.1.0]
  23.     at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61) ~[mybatis-plus-core-3.1.0.jar!/:3.1.0]
  24.     at com.sun.proxy.$Proxy147.insert(Unknown Source) ~[na:na]
  25.     at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.save(ServiceImpl.java:105) ~[mybatis-plus-extension-3.1.0.jar!/:3.1.0]
  26.     at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl.saveMedicineOrderTotal(MedicineOrderTotalServiceImpl.java:71) ~[classes!/:1.0-SNAPSHOT]
  27.     at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$FastClassBySpringCGLIB$$4e30bb24.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
  28.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  29.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  30.     at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$EnhancerBySpringCGLIB$$a6122abe.saveMedicineOrderTotal(<generated>) ~[classes!/:1.0-SNAPSHOT]
  31.     at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$FastClassBySpringCGLIB$$4e30bb24.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
  32.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  33.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  34.     at com.ambition.business.service.impl.MedicineOrderTotalServiceImpl$$EnhancerBySpringCGLIB$$20daa1f4.saveMedicineOrderTotal(<generated>) ~[classes!/:1.0-SNAPSHOT]
  35.     at com.ambition.business.service.impl.MedicineOrderServiceImpl.saveMedicOrder(MedicineOrderServiceImpl.java:197) ~[classes!/:1.0-SNAPSHOT]
  36.     at com.ambition.business.service.impl.MedicineOrderServiceImpl$$FastClassBySpringCGLIB$$76c08402.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
  37.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  38.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  39.     at com.ambition.business.service.impl.MedicineOrderServiceImpl$$EnhancerBySpringCGLIB$$f4bf0018.saveMedicOrder(<generated>) ~[classes!/:1.0-SNAPSHOT]
  40.     at com.ambition.business.service.impl.MedicineOrderServiceImpl$$FastClassBySpringCGLIB$$76c08402.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
  41.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  42.     at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  43.     at com.ambition.business.service.impl.MedicineOrderServiceImpl$$EnhancerBySpringCGLIB$$9bd9e5a2.saveMedicOrder(<generated>) ~[classes!/:1.0-SNAPSHOT]
  44.     at com.ambition.business.controller.MedicineOrderController.save(MedicineOrderController.java:43) ~[classes!/:1.0-SNAPSHOT]
  45.     at com.ambition.business.controller.MedicineOrderController$$FastClassBySpringCGLIB$$8d0f8652.invoke(<generated>) ~[classes!/:1.0-SNAPSHOT]
  46.     at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  47.     at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  48.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  49.     at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  50.     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) ~[spring-aop-5.1.8.RELEASE.jar!/:5.1.8.RELEASE]
  51.     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，运行起来还是报错。可以在客户端工具上直接修改，也可以使用以下语句修改:

  1. alter table medicine_product default character set utf8;

库字符集：

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

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

SHOW CREATE TABLE medicine_order_total;

  1. CREATE TABLE `medicine_order_total` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `medicine_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  4.   `medicine_code` bigint(20) DEFAULT NULL,
  5.   `shop_id` bigint(20) DEFAULT NULL,
  6.   `shop_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  7.   `order_id` bigint(20) DEFAULT NULL,
  8.   `customer_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  9.   `customer_age` int(5) DEFAULT NULL,
  10.   `customer_address` varchar(128) CHARACTER SET latin1 DEFAULT NULL,
  11.   `need_pay` bigint(20) DEFAULT NULL,
  12.   `actual_pay` bigint(20) DEFAULT NULL,
  13.   `total_price` bigint(20) DEFAULT NULL,
  14.   `payment_status` int(2) DEFAULT NULL,
  15.   `pay_channel` int(2) DEFAULT NULL,
  16.   `create_user_id` bigint(20) DEFAULT NULL,
  17.   `create_user_name` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
  18.   `create_time` datetime DEFAULT NULL,
  19.   PRIMARY KEY (`id`)
  20. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

可以通过以下语句修改:

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

  3. 其他的字段也这样处理

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

mysql
文章转载自开发架构二三事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论