向数据库插入数据报错Error updating database. Cause: java.sql.SQLException: Incorrect string value: '\xE5\xA4\

素颜马尾好姑娘i 2021-09-16 05:06 927阅读 0赞

之前连接数据库都没问题，可是今天新加一个表之后，向这个表中加入数据就报错：

2018-08-25 14:54:59.082  WARN 8136 --- [nio-8090-exec-7] .m.m.a.ExceptionHandlerExceptionResolver : Resolved exception caused by Handler execution: org.springframework.jdbc.UncategorizedSQLException: 
    ### Error updating database.  Cause: java.sql.SQLException: Incorrect string value: '\xE4\xB9\x9F\xE5\x90\x8C...' for column 'CROSS_NAME' at row 1
    ### The error may involve cn.vrview.mapper.CrossMapper.insert-Inline
    ### The error occurred while setting parameters
    ### SQL: INSERT INTO rg_cross   ( CROSS_NAME,  LONGITUDE,  LATITUDE,  IS_IMPORTANT_ROAD )  VALUES   ( ?,  ?,  ?,  ? )
    ### Cause: java.sql.SQLException: Incorrect string value: '\xE4\xB9\x9F\xE5\x90\x8C...' for column 'CROSS_NAME' at row 1
    ; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366]; Incorrect string value: '\xE4\xB9\x9F\xE5\x90\x8C...' for column 'CROSS_NAME' at row 1; nested exception is java.sql.SQLException: Incorrect string value: '\xE4\xB9\x9F\xE5\x90\x8C...' for column 'CROSS_NAME' at row 1
    2018-08-25 14:55:10.039 ERROR 8136 --- [taskScheduler-1] druid.sql.Statement                      : {conn-10001, pstmt-20003} execute error. SELECT  ID AS id,TASK_ID AS taskId,EXEC_TIME AS execTime,EXEC_STATUS AS execStatus,EXEC_PROGRESS AS execProgress,DEV_NUMBER AS devNumber,DIAG_BAD_DEV_NUM AS diagBadDevNum,EXEC_NUMBER AS execNumber,IS_SENDSERVER AS isSendserver,result_light_views  FROM rg_task_exec_info 
     WHERE  (EXEC_STATUS = ? AND EXEC_PROGRESS = ? AND EXEC_TIME < ?)

后来找到原因：

之前我的数据库连接设置：jdbc:mysql://192.168.1.230:3306/trafficlight?useUnicode=true&characterEncoding=utf-8&useSSL=true

之后改为：jdbc:mysql://192.168.1.230:3306/trafficlight?useUnicode=true&characterEncoding=utf-8&useSSL=true

就OK了，注意将表的字符集和校验规则设置成UTF\_8