shardingSphere 广播表设置

广播表

指所有的分片数据源中都存在的表，表结构及其数据在每个数据库中均完全一致。 适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。

• 使用广播表需满足一下条件：

	1. 数据量不大
	2. 每个数据源中的数据保持一致

• 配置

spring.shardingsphere.datasource.names=ds0,ds1

#分库0
spring.shardingsphere.datasource.ds0.name=mysql
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jmysql://localhost:3306/dev0?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

#分库1
spring.shardingsphere.datasource.ds1.name=mysql
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/dev1?characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

#t_org 表
spring.shardingsphere.rules.sharding.tables.t_org.actual-data-nodes=ds$->{0..1}.t_org
spring.shardingsphere.rules.sharding.tables.t_org.database-strategy.standard.sharding-column=com_id
spring.shardingsphere.rules.sharding.tables.t_org.database-strategy.standard.sharding-algorithm-name=preciseShardingDatabaseAlgorithm
#广播表
spring.shardingsphere.rules.sharding.broadcast-tables=t_org

• 正常编码sql即可，shardingSphere会自动帮助我们落库广播表。

• 验证

2023-02-02 16:43:47.328  INFO 46604 --- [main] ShardingSphere-SQL: Logic SQL: insert into t_org (com_id,organization_name) VALUE('11','测试广播')
2023-02-02 16:43:47.329  INFO 46604 --- [main] ShardingSphere-SQL: SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2023-02-02 16:43:47.329  INFO 46604 --- [main] ShardingSphere-SQL: Actual SQL: ds0 ::: insert into t_org (com_id,organization_name) VALUE('11', '测试广播')
2023-02-02 16:43:47.329  INFO 46604 --- [main] ShardingSphere-SQL: Actual SQL: ds1 ::: insert into t_org (com_id,organization_name) VALUE('11', '测试广播')
2023-02-02 16:43:47.371 DEBUG 46604 --- [main] c.s.r.mapper.GoodsStoreMapper.insertOrg  : <==    Updates: 1

查看日志发现：落库 ds0 和 ds1 。