介绍

基于shardingsphere-jdbc 5.1.0 进行单库分表.

复合行表达式分片算法

类型：COMPLEX_INLINE

属性名称	数据类型	说明	默认值
sharding-columns (?)	String	分片列名称，多个列用逗号分隔。如不配置无法则不能校验	-
algorithm-expression	String	分片算法的行表达式	-
allow-range-query-with-inline-sharding (?)	boolean	是否允许范围查询。注意：范围查询会无视分片策略，进行全路由	false

1. maven项目依赖

<dependencies>
	<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.0</version>
        </dependency>
	 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
</dependencies>

2.application.yml配置

spring:
  application:
    name: jdbc-jpa-complex
  profiles:
    include: jdbc
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none
      naming:
        implicit-strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
    database-platform: org.hibernate.dialect.MySQL8Dialect
    properties:
      hibernate.enable_lazy_load_no_trans: true

logging:
  file:
    name: logs/${spring.application.name}.log
  level:
    org.springframework: info
    com.lance.sharding.complex: debug

3.application-jdbc.yml配置

spring:
  shardingsphere:
    datasource:
      ds:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        password: li123456
        username: root
      names: ds
    rules:
      sharding:
        binding-tables:
          - t_order,t_order_item
        broadcast-tables: t_address
        sharding-algorithms:
          t-order-inline:
            type: COMPLEX_INLINE
            props:
              algorithm-expression: t_order_$->{city}_$->{order_id % 2}
              sharding-columns: city,order_id
          t-order-item-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_item_$->{order_id % 2}
        tables:
          t_order:
            actual-data-nodes: ds.t_order_$->{['shanghai','beijing']}_$->{0..1}
            table-strategy:
              complex:
                sharding-algorithm-name: t-order-inline
                sharding-columns: city, order_id
          t_order_item:
            actual-data-nodes: ds.t_order_item_$->{0..1}
            table-strategy:
              standard:
                sharding-algorithm-name: t-order-item-inline
                sharding-column: order_id
    props:
      sql-show: true

4.测试Sql脚本

CREATE TABLE `t_order_beijing_0`
(
    `order_id`    bigint NOT NULL AUTO_INCREMENT,
    `user_id`     int    NOT NULL,
    `address_id`  bigint NOT NULL,
    `city`        varchar(32) NULL DEFAULT NULL,
    `status`      tinyint NULL DEFAULT NULL,
    `creator`     varchar(32) NULL DEFAULT NULL,
    `create_time` datetime NULL DEFAULT NULL,
    `updater`     varchar(32) NULL DEFAULT NULL,
    `update_time` datetime NULL DEFAULT NULL,
    PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic
CREATE TABLE `t_order_shanghai_0`
(
    `order_id`    bigint NOT NULL AUTO_INCREMENT,
    `user_id`     int    NOT NULL,
    `address_id`  bigint NOT NULL,
    `city`        varchar(32) NULL DEFAULT NULL,
    `status`      tinyint NULL DEFAULT NULL,
    `creator`     varchar(32) NULL DEFAULT NULL,
    `create_time` datetime NULL DEFAULT NULL,
    `updater`     varchar(32) NULL DEFAULT NULL,
    `update_time` datetime NULL DEFAULT NULL,
    PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic

5.单元测试Test

class OrderRepositoryTests {
	@Autowired
	private OrderRepository orderRepository;

	@Test
	@Disabled
	void save() {
		ThreadLocalRandom random = ThreadLocalRandom.current();
		String[] cities = {"beijing", "shanghai"};

		IntStream.range(0, 20).forEach(i -> {
			OrderEntity order = new OrderEntity();
			order.setOrderId(System.nanoTime() + i);
			order.setAddressId(i);
			order.setCity(cities[i % 2]);
			order.setUserId(Math.abs(random.nextInt()));
			order.setCreator("user.0" + i);
			order.setUpdater(order.getCreator());
			orderRepository.save(order);

		});
	}

	@Test
	@Disabled
	void findOne() {
		long orderId = 782568039714202L;
		String city = "beijing";
		OrderEntity orderEntity = orderRepository.findByOrderIdAndCity(orderId, city);
		log.info("===>{}", orderEntity);
	}
}

6.项目完整地址

shardingsphere-jdbc之JPA复合分片算法 Github 地址

shardingsphere-jdbc之JPA复合分片算法 Gitee 地址