介绍
基于shardingsphere-jdbc 5.1.0 进行单库分表.根据分表字段取模
哈希取模分片算法
类型:MOD
属性名称 | 数据类型 | 说明 | 默认值 |
---|---|---|---|
sharding-count | int | 分片数量 | - |
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-mod
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.mod: 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-algorithm:
type: MOD
props:
sharding-count: '6'
t-order-item-inline:
type: INLINE
props:
algorithm-expression: t_order_item_$->{order_id % 2}
tables:
t_order:
actual-data-nodes: ds.t_order_$->{0..5}
table-strategy:
standard:
sharding-algorithm-name: t-order-algorithm
sharding-column: 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_1`
(
`order_id` bigint NOT NULL AUTO_INCREMENT,
`price` double(12,2) NOT 0 ,
`user_id` int NOT NULL,
`address_id` bigint NOT NULL,
`city` varchar(32) NULL DEFAULT NULL,
`status` tinyint NULL DEFAULT NULL,
`interval_time` datetime 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 {
private final static String[] CITIES = {"shanghai", "beijing"};
/**
* -120000, 0, 18, 20000, 40000, 50000, 60000, 64000, 80000, 99000
*/
private final static long[] PRICES = {-120000, 0, 18, 20000, 40000, 50000, 60000, 64000, 80000, 99000};
@Autowired
private OrderRepository orderRepository;
@Test
@Disabled
void save() {
ThreadLocalRandom random = ThreadLocalRandom.current();
Date[] dates = create();
IntStream.range(0, 20).forEach(i -> {
OrderEntity order = new OrderEntity();
order.setOrderId(System.nanoTime() + i);
order.setPrice(PRICES[i % PRICES.length]);
order.setAddressId(i);
order.setCity(CITIES[i % 2]);
order.setUserId(Math.abs(random.nextInt()));
order.setIntervalTime(dates[i % 6]);
order.setCreator("user.0" + i);
order.setCreateTime(new Date());
order.setUpdater(order.getCreator());
order.setUpdateTime(order.getCreateTime());
orderRepository.save(order);
});
}
@Test
@Disabled
void findOne() {
long orderId = 379216929739712L;
Optional<OrderEntity> orderEntity = orderRepository.findById(orderId);
log.info("===>{}", orderEntity);
}
private Date[] create() {
Date[] dates = new Date[6];
try {
Date date = DateUtils.parseDate("2022-02-01 00:00:00", Locale.CHINA, "yyyy-MM-dd HH:mm:ss");
IntStream.range(0, 6).forEach(i -> dates[i] = DateUtils.addDays(date, i));
} catch (ParseException e) {
log.error("date parse fail: ", e);
}
return dates;
}
}