关联查询无法查询其他库信息的问题

Apache ShardingSphere 提问
#1

实际中遇到的一个问题A left join B 查询信息如果B信息存在于其他分库那么查询出来的结果是null
简化sql为

@Query(value = " select if(f.mutually_flag is null or f.mutually_flag=false,0,1) mutuallyFlag, u.did did, u.nick_name nickName " +
		"from fans  f  left  join   did_user u on f.fans_id = u.did " +
		"where f.did= ?1  " ,
		countQuery = "select count(*) from ( select if(f.mutually_flag is null or f.mutually_flag=false,0,1) mutuallyFlag, u.did did,u.nick_name nickName  " +
				" from fans  f  left  join   did_user u on f.fans_id = u.did " +
				" where f.did= ?1  ) f  " , nativeQuery = true)
Page<Map< String, Object>> fansInfos(Pageable pageable, String userId);

执行后会拿到类似这种的数据
image

有什么解决办法么或者说我这里有什么使用方面的误区 希望大佬指点下 :grinning:

#2

分库表配置信息如下

spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.jdbc.batch_size=1000
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.shardingsphere.datasource.names=u0,u1

spring.main.allow-bean-definition-overriding=true

spring.shardingsphere.props.sql.show=true

gs.jdbc.type=org.apache.commons.dbcp2.BasicDataSource
gs.jdbc.driver-class-name=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
gs.jdbc.url1=jdbc:log4jdbc:mysql://192.168.3.211:3307/
gs.jdbc.url2=?useUnicode=true&characterEncoding=utf-8&noAccessToProcedureBodies=true&allowMultiQueries=true&autoReconnect=true
gs.jdbc.username=root
gs.jdbc.password=root
gs.jdbc.nodes=u$->{0..1}.
gs.jdbc.node.number=2

spring.shardingsphere.datasource.u0.type=${gs.jdbc.type}
spring.shardingsphere.datasource.u0.driver-class-name=${gs.jdbc.driver-class-name}
spring.shardingsphere.datasource.u0.url=${gs.jdbc.url1}gs_user_0${gs.jdbc.url2}
spring.shardingsphere.datasource.u0.username=${gs.jdbc.username}
spring.shardingsphere.datasource.u0.password=${gs.jdbc.password}

spring.shardingsphere.datasource.u1.type=${gs.jdbc.type}
spring.shardingsphere.datasource.u1.driver-class-name=${gs.jdbc.driver-class-name}
spring.shardingsphere.datasource.u1.url=${gs.jdbc.url1}gs_user_1${gs.jdbc.url2}
spring.shardingsphere.datasource.u1.username=${gs.jdbc.username}
spring.shardingsphere.datasource.u1.password=${gs.jdbc.password}

#####
spring.shardingsphere.rules.sharding.key-generators.tinyid.type=tinyid
spring.shardingsphere.rules.sharding.default-key-generate-strategy.key-generator-name=tinyid
spring.shardingsphere.rules.sharding.default-key-generate-strategy.column=id

spring.shardingsphere.rules.sharding.sharding-algorithms.mod.type=inline
spring.shardingsphere.rules.sharding.sharding-algorithms.mod.props.algorithm-expression=u$->{Math.abs(did.hashCode()) % ${gs.jdbc.node.number}}
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=mod
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=did
#####
spring.shardingsphere.rules.sharding.binding-tables[0]=did_user,fans,follow

spring.shardingsphere.rules.sharding.tables.did_user.actual-data-nodes=${gs.jdbc.nodes}did_user
spring.shardingsphere.rules.sharding.tables.fans.actual-data-nodes=${gs.jdbc.nodes}fans
spring.shardingsphere.rules.sharding.tables.follow.actual-data-nodes=${gs.jdbc.nodes}follow
京ICP备2021015875号