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 {

    @Resource
    private OrderMapper orderMapper;

    @Test
    void 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}