shardingsphere分库分表跨库访问 添加分片规则

shardingsphere分库分表跨库访问 添加分片规则

建立 JDBC 环境

创建表

t_order：

CREATE TABLE `t_order` (`tid` bigint(20) NOT NULL,`tname` varchar(255) DEFAULT NULL,`goods_id` bigint(20) DEFAULT NULL,`tstatus` varchar(255) DEFAULT NULL,PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立 SpringBoot 工程

修改 pom.xml：

<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.3.7.RELEASE</version><relativePath/> <!-- lookup parent from repository -->
</parent>

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><scope>provided</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.22</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.0.5</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId><version>5.0.0-beta</version></dependency>
</dependencies>

创建实体类：

/*** 订单** @author BNTang* @date 2021/10/11*/
@Data
@TableName("t_order")
public class Order {private Long tid;private String tname;private Long goodsId;private String tstatus;
}

创建 Mapper：

/*** @author BNTang* @version 1.0* @project ShardingSpherePro* @description* @since Created in 2021/10/11 011 20:47**/
public interface OrderMapper extends BaseMapper<Order> {
}

修改启动类，添加注解：

@MapperScan("top.it6666.shardingspherepro.mapper")

application.properties

spring.shardingsphere.datasource.names=shardingspheredb1
spring.shardingsphere.datasource.shardingspheredb1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.shardingspheredb1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.shardingspheredb1.url=jdbc:mysql://localhost:3310/shardingspheredb1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.shardingspheredb1.username=root
spring.shardingsphere.datasource.shardingspheredb1.password=root
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

编写测试类：

@SpringBootTest
@RunWith(SpringRunner.class)
class ShardingSphereProApplicationTests {@Resourceprivate OrderMapper orderMapper;@Testvoid addOrder() {for (int i = 0; i < 10; i++) {Order order = new Order();order.setTid((long) i);order.setTname("订单" + i);order.setGoodsId(Long.valueOf("" + (1000 + i)));order.setTstatus("1");System.out.println(order);this.orderMapper.insert(order);}}
}

数据分片存储

建立分片真实表，t_order_0，t_order_1 SQL如下：

CREATE TABLE `t_order_0` (`tid` bigint(20) NOT NULL,`tname` varchar(255) DEFAULT NULL,`goods_id` bigint(20) DEFAULT NULL,`tstatus` varchar(255) DEFAULT NULL,PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_order_1` (`tid` bigint(20) NOT NULL,`tname` varchar(255) DEFAULT NULL,`goods_id` bigint(20) DEFAULT NULL,`tstatus` varchar(255) DEFAULT NULL,PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

分表配置

修改 application.properties 添加如下相关的配置内容：

# 配置t_order真实表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=shardingspheredb1.t_order_$->{0..1}# 配置分表策略 主键+分片算法
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=tid
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=table-inline# 配置 分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_order_$->{tid % 2}# 主键盘生成策略
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=tid
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=1# 打印执行sql
spring.shardingsphere.props.sql-show=true

spring.shardingsphere.props.sql-show=true
这里就来一一解释一下如上配置当中比较关键的几个内容 spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes 该内容就是配置 t_order 真实表规则, 我如上配置的就是 0，1
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression 配置的内容就是真实表的寻找算法
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column 指定了分表以 tid 进行分表操作
如上的内容配置完毕之后再次运行测试类，在运行测试类之前其实可以将 id 的设置给去除因为如上配置了 主键盘生成策略

分库分表
添加第二个数据源，修改 application.properties：

spring.shardingsphere.datasource.names=shardingspheredb1,shardingspheredb2

# 配置第 2 个数据源
spring.shardingsphere.datasource.shardingspheredb2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.shardingspheredb2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.shardingspheredb2.url=jdbc:mysql://localhost:3306/shardingspheredb2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.shardingspheredb2.username=root
spring.shardingsphere.datasource.shardingspheredb2.password=root

修改表规则，修改配置文件，都是同一个配置文件内容修改，不再强调了：

# 水平拆分  水平分片
# 配置 t_order 表规则                                                 数据源.真实表
# 配置t_order真实表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=shardingspheredb$->{1..2}.t_order_$->{0..1}

配置配置分库，主键 + 分片算法策略：

# 配置分库策略  主键+分片算法
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=goods_id
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=database-inline
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=shardingspheredb$->{goods_id % 2 + 1}

最终 application.properties 配置文件内容如下：

spring.shardingsphere.datasource.names=shardingspheredb1,shardingspheredb2
spring.shardingsphere.datasource.shardingspheredb1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.shardingspheredb1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.shardingspheredb1.url=jdbc:mysql://www.yangbuyi.top:3310/shardingspheredb1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.shardingspheredb1.username=root
spring.shardingsphere.datasource.shardingspheredb1.password=yangbuyiya
# 配置第 2 个数据源
spring.shardingsphere.datasource.shardingspheredb2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.shardingspheredb2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.shardingspheredb2.url=jdbc:mysql://www.yangbuyi.top:3310/shardingspheredb2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.shardingspheredb2.username=root
spring.shardingsphere.datasource.shardingspheredb2.password=yangbuyiya
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 水平拆分  水平分片
# 配置 t_order 表规则                                                 数据源.真实表
# 配置t_order真实表规则
spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=shardingspheredb$->{1..2}.t_order_$->{0..1}
# 配置分表策略 主键+分片算法
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=tid
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=table-inline
# 配置 分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_order_$->{tid % 2}
# 主键盘生成策略
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=tid
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=1
# 打印执行sql
spring.shardingsphere.props.sql-show=true
# 配置分库策略  主键+分片算法
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-column=goods_id
spring.shardingsphere.rules.sharding.tables.t_order.database-strategy.standard.sharding-algorithm-name=database-inline
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=shardingspheredb$->{goods_id % 2 + 1}