ShardingJDBC_学习笔记

水平分表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 配置数据源，数据源命名
spring.shardingsphere.datasource.names=m1

# 允许一个实体类对应两张表
spring.main.allow-bean-definition-overriding=true

# 配置数据源及连接池相关属性
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=qweasdzxc

# 指定user表分布情况,配置表在哪个数据库里面,表名称  m1.user_1 , m1.user_2
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m1.user_$->{1..2}

# 指定user表里面主键uid 生成策略  SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.column=uid
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

# 指定分片策略,约定uid值偶数添加到user_1表，如果uid是奇数添加到user_2表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=uid
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{uid % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
水平分库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 配置数据源，数据源命名
spring.shardingsphere.datasource.names=m1,m2

# 允许一个实体类对应两张表
spring.main.allow-bean-definition-overriding=true

# 配置第一个数据源及连接池相关属性
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/test_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=qweasdzxc

# 配置第二个数据源及连接池相关属性
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/test_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=qweasdzxc

#指定数据库分布情况，数据库里面表分布情况 
# m1  m2  user_1 user_2
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m$->{1..2}.user_$->{1..2}


# 指定user表里面主键uid 生成策略  SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.column=uid
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

# 指定分片策略,约定uid值偶数添加到user_1表，如果uid是奇数添加到user_2表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=uid
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{uid % 2 + 1}

# 指定数据库分片策略，(Android或IOS) 约定cid是偶数添加m1，是奇数添加到m2
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{cid % 2 + 1}

spring.shardingsphere.sharding.tables.user.database-strategy.inline..sharding-column=cid
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=m$->{cid % 2 + 1}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
垂直分库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 配置数据源，数据源命名
spring.shardingsphere.datasource.names=m1,m0

# 允许一个实体类对应两张表
spring.main.allow-bean-definition-overriding=true

# 配置第一个数据源及连接池相关属性
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/test_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=qweasdzxc

# 配置第二个数据源及连接池相关属性
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/test_3?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=qweasdzxc

# 配置专库专表
spring.shardingsphere.sharding.tables.t_only.actual-data-nodes=m$->{0}.t_only

# 指定t_only表中主键的生成策略
spring.shardingsphere.sharding.tables.t_only.key-generator.column = oid
spring.shardingsphere.sharding.tables.t_only.key-generator.type =SNOWFLAKE

# 打开sql的输出日志
spring.shardingsphere.props.sql.show = true
配置公共表
1
spring.shardingsphere.sharding.broadcast-tables=biz_dict