ShardingSphere修改报错-ShardingSphereException: Can not update sharding key

快来打我* 2023-10-10 18:08 127阅读 0赞

#### 目录 ####

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

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

### 一、场景 ###

1、使用ShardingJDBC操作数据库

2、修改SQL执行报错，但将sql复制到navicat中执行，是正常的

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

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

Caused by: org.apache.ibatis.exceptions.PersistenceException: 
    ### Error updating database.  Cause: org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can not update sharding key, logic table: [t_busi_announceType], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@62c6587d].
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: UPDATE t_busi_announceType   SET annTypeName = ?,   memo = ?,   VDNID = ?,   lastUpdateDate = ?,   lastUpdateOper = ?   WHERE  annTypeId = ?
    ### Cause: org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can not update sharding key, logic table: [t_busi_announceType], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@62c6587d].
    	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:498)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    	... 105 common frames omitted

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

### 三、原因 ###

update语句中涉及了分片键值的更新，更新后的键值可能会导致数据所在分片位置发送改变，所以分片键的值不可更新

在本例中`VDNID`是作为分片键的，却在SQL中更新该键的值，导致报错

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

### 四、解决 ###

1、不更新分片键值

2、在`@Column`指定`updatable = false`

//updatable = false，意思是永远不更新该字段值
    @Column(name = "xxx", updatable = false)