ShardingSphere修改报错-MysqlDataTruncation: Data truncation: Out of range value for column ‘xx‘ at row 1

以你之姓@ 2023-10-10 18:13 53阅读 0赞

#### 目录 ####

*   *  一、场景
     *  二、报错信息
     *  三、原因
     *  四、解决

--------------------

### 一、场景 ###

1、项目使用ShardingJDBC操作数据库

2、修改SQL执行报错

--------------------

### 二、报错信息 ###

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'MSGID' at row 1
    ### The error may involve com.xxx.mapper.service.KeyWordMapper.updateByExample-Inline
    ### The error occurred while setting parameters
    ### SQL: UPDATE t_wx_keyword  SET keyword = ?,msgType = ?,msgId = ?,content = ?,createTime = ?,eable = ?,matchType = ?,originId = ?,vdnId = ? WHERE (       (  vdnId = ?                                                and originId = ?                                                and id = ? ) )
    ### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'MSGID' at row 1
    ; Data truncation: Out of range value for column 'MSGID' at row 1; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'MSGID' at row 1
    org.springframework.dao.DataIntegrityViolationException: 
    ### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'MSGID' at row 1
    ### The error may involve com.xxx.mapper.service.KeyWordMapper.updateByExample-Inline
    ### The error occurred while setting parameters
    ### SQL: UPDATE t_wx_keyword  SET keyword = ?,msgType = ?,msgId = ?,content = ?,createTime = ?,eable = ?,matchType = ?,originId = ?,vdnId = ? WHERE (       (  vdnId = ?                                                and originId = ?                                                and id = ? ) )
    ### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'MSGID' at row 1
    ; Data truncation: Out of range value for column 'MSGID' at row 1; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'MSGID' at row 1

--------------------

### 三、原因 ###

表字段的数据类型与实体类属性的类型不一致

在本例中：表字段MSGID的数据类型是`int`，而实体类的msgId属性类型是`String`，类型不一致

--------------------

### 四、解决 ###

方法1、更改表字段数据类型，与实体类属性类型一致

方法2、更改实体类属性类型，与表字段数据类型一致