Incorrect string value: ‘\xE6\x9D\x91\xE4\xB8\x8A...‘ for column ‘name‘错误解决
2022-09-28 17:33:41,045 [XNIO-1 task-2] ERROR [c.s.s.f.intercepter.SaasGlobalExceptionHandler] SaasGlobalExceptionHandler.java:167 - 【系统异常】-通过POST方式请求接口地址：http://10.19.0.112/v3/saas/surveyObject/batchV3 时发生异常，trace-id：null，响应服务器：172.22.0.132，请求参数为：{“list”:[{“address”:“和平里东街11号航星园生活服务中心一层”,“lat”:39.950458,“lng”:116.42231100000001,“pointIndex”:5,“polygonRegion”:“”,“reportId”:6088,“shopName”:“朋克养生PUNK THERAPY”},{“address”:“和平里南街中国航天科工”,“lat”:39.954058,“lng”:116.420311,“pointIndex”:4,“polygonRegion”:“”,“reportId”:6088,“shopName”:“梯饮”}],“merchantId”:146,“projectName”:“北京市-北京市01-20220928”,“reportType”:2,“userId”:860,“userName”:“杨过”}，异常信息：com.collect.server.mapper.SurveyShopTaskMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Incorrect string value: ‘\xE6\x9D\xA8\xE8\xBF\x87’ for column ‘charge_person_name’ at row 1; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: ‘\xE6\x9D\xA8\xE8\xBF\x87’ for column ‘charge_person_name’ at row 1; nested exception is java.sql.BatchUpdateException: Incorrect string value: ‘\xE6\x9D\xA8\xE8\xBF\x87’ for column ‘charge_person_name’ at row 1，堆栈信息：org.springframework.jdbc.UncategorizedSQLException: com.collect.server.mapper.SurveyShopTaskMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Incorrect string value: ‘\xE6\x9D\xA8\xE8\xBF\x87’ for column ‘charge_person_name’ at row 1; uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: ‘\xE6\x9D\xA8\xE8\xBF\x87’ for column ‘charge_person_name’ at row 1; nested exception is java.sql.BatchUpdateException: Incorrect string value: ‘\xE6\x9D\xA8\xE8\xBF\x87’ for column ‘charge_person_name’ at row 1 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.executeBatch(ServiceImpl.java:232) at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.executeBatch(ServiceImpl.java:252)
错误原因:
出现这种问题是因为你的该字段的编码格式问题,一般是插入中文数据的时候出现这样的问题
解决方案:
修改该字段的编码格式为utf-8,为了防止新建的表出现这种问题,最好把数据库的编码格式也修改
解决1
ALTER TABLE saas_network.survey_shop_task MODIFY COLUMN charge_person_name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT ‘***’;
alter table survey_shop_task character set utf8;
总结
1、创建表的时候，我们可以不设置格式，也可以设置utf格式，也可以设置非utf，比如armscii8
2、新建字段的时候，根据创建表默认的格式，带过去了armscii8，如果有中文保存到了这个字段，就会报上面的错误
3、解决的办法：把字段改为utf
ALTER TABLE survey_shop_task MODIFY COLUMN charge_person_name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT ‘负责人名称’;
进一步根治问题，将数据表改为utf
alter table survey_shop_task character set utf8;
解决2
查看数据库stu表的编码格式
SHOW CREATE TABLE stu;
修改stu编码格式为utf8
ALTER TABLE stu DEFAULT CHARACTER SET utf8;
接下来要修改列的编码格式
alter table stu change Sname Sname varchar(20) character set utf8;