方便更快捷的说明问题,可以按需填写(可删除)
没有更新分片键,update操作中,sql使用 where 分片键=xx 作为条件
使用环境:
场景、问题:
没有报错,日志看起来sql正常执行,只是没有更新数据
已进行操作:
现状:
这种实现无法对数据进行更新,感觉分片键在update操作中不能出现在 where条件里
方便更快捷的说明问题,可以按需填写(可删除)
没有更新分片键,update操作中,sql使用 where 分片键=xx 作为条件
没有报错,日志看起来sql正常执行,只是没有更新数据
这种实现无法对数据进行更新,感觉分片键在update操作中不能出现在 where条件里
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
我找时间上传一个,现在update操作的where条件不包含分片键