使用sharding-jdbc的4.1.1版本,update操作使用updateByPrimaryKey相关的方法,无法更新数据

方便更快捷的说明问题,可以按需填写(可删除)
没有更新分片键,update操作中,sql使用 where 分片键=xx 作为条件

使用环境:

场景、问题:

没有报错,日志看起来sql正常执行,只是没有更新数据

已进行操作:

现状:

这种实现无法对数据进行更新,感觉分片键在update操作中不能出现在 where条件里

@zheng 能否提供下配置文件,以及 sql-show log,需要看下在执行什么语句。

sql log

[INFO] 2021-11-19 15:38:22,054 [XNIO-2 task-2] 90245b11e14046b7b263e414bb6374a3 ShardingSphere-SQL (SQLLogger.java:74) -Actual SQL: saas1 ::: update saas_case1
SET company_id = ?,

    agent_id = ?,


    batch = ?,


    business_type = ?,


    customer_id = ?,


    customer_name = ?,


    customer_sex = ?,


    customer_phone = ?,


    customer_linkman = ?,


    customer_area = ?,


    customer_id_num = ?,


    product_type = ?,


    breakpoint_customer = ?,


    create_time = ?,


    create_user = ?,


    update_time = ?,


    update_user = ?,









    key_customer = ?,


    customer_status = ?,


    customer_remarks = ?,


    customer_tags = ?,


    overdue_time = ?,


    overdue_loans = ?,


    reject_reasons = ?,


    score = ?,


    owner_id = ?,


    owner_name = ?,


    owner_skill_group = ?,


    owner_seat_factory = ?,


    record_state = ?,


    ext_field1 = ?,


    ext_field2 = ?,


    ext_field3 = ?,


    ext_field4 = ?,


    ext_field5 = ?,


    ext_field6 = ?,


    ext_field7 = ?,


    ext_field8 = ?,


    ext_field9 = ?,


    ext_field10 = ?,


    ext_field11 = ?,


    ext_field12 = ?,


    ext_field13 = ?,


    ext_field14 = ?,


    ext_field15 = ?,


    ext_field16 = ?,


    ext_field17 = ?,


    ext_field18 = ?,


    ext_field19 = ?,


    ext_field20 = ?
where id = ? ::: [ , 1000, 2, 1, 1, test, 男, 1300100xxxx, [{"linkId":"123","name":"吴","sex":"男","phone":"13001004876","status":0}], , 410xxxx, 支付宝借呗, 0, 2021-11-19T10:48:35.000+0800, , 2021-11-19T10:48:35.000+0800, , 1, , , ["标签项1","标签2"], 100, 1000.0, , 0.0, , , , , 0, , , , , , , , , , , , , , , , , , , , , 1]

配置

##-----------------------------sharding-jdbc begin-----------------------------##
spring.shardingsphere.datasource.names=saas,saas0,saas1
#默认的数据源,不需要分库分表的数据表
spring.shardingsphere.sharding.default-data-source-name=saas

spring.shardingsphere.datasource.saas.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saas.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.saas.url=jdbc:mysql://localhost:3306/saas?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.saas.username=root
spring.shardingsphere.datasource.saas.password=

spring.shardingsphere.datasource.saas0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saas0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.saas0.url=jdbc:mysql://localhost:3306/saas0?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.saas0.username=root
spring.shardingsphere.datasource.saas0.password=

spring.shardingsphere.datasource.saas1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saas1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.saas1.url=jdbc:mysql://localhost:3306/saas1?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.saas1.username=dfx
spring.shardingsphere.datasource.saas1.password=
#案件表配置
spring.shardingsphere.sharding.tables.saas_case.actual-data-nodes=saas$->{0…1}.saas_case$->{0…1}
spring.shardingsphere.sharding.tables.saas_case.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.saas_case.table-strategy.inline.algorithm-expression=saas_case$->{id % 2}
spring.shardingsphere.sharding.tables.saas_case.key-generator.column=id
spring.shardingsphere.sharding.tables.saas_case.key-generator.type=SNOWFLAKE

##绑定表
spring.shardingsphere.sharding.binding-tables=saas_case
#分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=db_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=saas$->{db_id % 2}
#sql展示
spring.shardingsphere.props.sql.show=true
##-----------------------------sharding-jdbc end-----------------------------##

看日志路由没有问题,根据 id = 1 路由到 saas_case1 分片执行 SQL,请问你期望的结果是什么?

期望结果:字段 customer_tags会被赋值,之前这个字段没值

直接拿日志里面的 SQL 去 DB 执行,可以 update 成功吗?

直接拿日志里面的 SQL 去 DB 执行,可以成功

我看你配置了分库,id为1的数据是实际库和分片的库是一致的吗

sql log中其实两个库都有执行

[INFO] 2021-11-19 15:38:22,053 [XNIO-2 task-2] 90245b11e14046b7b263e414bb6374a3 ShardingSphere-SQL (SQLLogger.java:74) -Actual SQL: saas0 ::: update saas_case1

[INFO] 2021-11-19 15:38:22,054 [XNIO-2 task-2] 90245b11e14046b7b263e414bb6374a3 ShardingSphere-SQL (SQLLogger.java:74) -Actual SQL: saas1 ::: update saas_case1

@zheng 方便的话,请上传一个 demo 帮助复现问题,这个问题之前没有遇到类似的反馈

我找时间上传一个,现在update操作的where条件不包含分片键

京ICP备2021015875号