1.说明
(1)分库,根据逻辑表shop和product的id奇偶拆分。
(2)product分表,根据shop_id奇偶,分为product1和product2。
2.yml配置
spring:
profiles:
active: dev
#Sharding-JDBC的配置
shardingsphere:
datasource:
#数据源名称,多个用逗号分开
names: ds1,ds2
ds1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.0.1:3306/ds1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
ds2:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.0.1:3306/ds2?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
#分片的配置
sharding:
#表的分片策略
tables:
product:
actual-data-nodes: ds$->{1..2}.product_$->{1..2}
database‐strategy:
inline:
sharding‐column: shop_id
algorithm‐expression: ds$->{shop_id%2+1}
table‐strategy:
inline:
##分片键
sharding‐column: id
##分片算法,内置的精确算法
algorithm‐expression: product_$->{id%2+1}
#主键生成策略
key-generator:
#主键
column: id
#生成算法
type: SNOWFLAKE
shop:
database‐strategy:
inline:
##分片键
sharding‐column: id
##分片算法,内置的精确算法
algorithm‐expression: ds$->{id%2+1}
#主键生成策略
key-generator:
#主键
column: id
#生成算法
type: SNOWFLAKE
props:
sql:
show: true
(1)actual-data-nodes: ds$->{1..2}.product_$->{1..2}:表示ds1,ds2分辨有product_1和product_2.
(2)其中查看table‐strategy和database‐strategy的配置表示:
ds1.product_1:存shop_id为偶数,id为偶数
ds1.product_2:存shop_id为偶数,id为奇数
ds2.product_1:存shop_id为奇数,id为偶数
ds2.product_2:存shop_id为奇数,id为奇数
3.测试代码
/**
* 水平拆分2
* 生成两个shop,每个shop,生成三个商品
*/
@Test
public void testInline2(){
for(long i=1;i<=2;i++){
Shop shop = new Shop();
shop.setId(i);
shop.setName("汉服专卖店"+i);
shopService.save(shop);
for(long j=1;j<=3;j++){
Product product = new Product();
product.setLocation("广东省东莞市"+j);
product.setName(shop.getName()+":迷彩服"+j);
product.setShopId(shop.getId());
product.setId(j);
productService.save(product);
}
}
}
4.bug
上面的test代码,product的id会重复,需要注意