简单实现shardingSphere + MybatisPlus分库分表2025

准备

环境

• mysql 5.7 +
• Springboot 3.2 +
• mybatis-plus 3.5.5
• druid 1.2.23
• shardingsphere-jdbc 5.5
• JDK 21

Pom

<dependencies><!-- Spring Boot --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-spring-boot3-starter</artifactId><version>3.5.5</version></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.23</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc</artifactId><version>5.5.0</version><exclusions><exclusion><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-test-util</artifactId></exclusion></exclusions></dependency><dependency><groupId>com.alibaba.fastjson2</groupId><artifactId>fastjson2</artifactId><version>2.0.54</version></dependency>
</dependencies>

数据库准备

新建两个库、分别建四张表

CREATE DATABASE `order_db_0` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE `order_db_1` /*!40100 DEFAULT CHARACTER SET utf8 */;CREATE TABLE `order_0` (`order_id` bigint(20) NOT NULL,`user_id` bigint(20) NOT NULL,`amount` decimal(10,2) NOT NULL,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`order_id`),KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 其他四张表结构一致即可

代码部分

配置文件

在resource目录下新建两个文件：

application.yaml

spring:main:allow-bean-definition-overriding: trueapplication:name: ls-backenddatasource:driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:sharding.yamlservlet:multipart:max-file-size: 100MBmax-request-size: 100MB
server:port: 8085servlet:context-path: /encoding:force-response: true#Mybatis扫描
mybatis:config-location: classpath:/mybatis-config.xmlmybatis-plus:mapper-locations: classpath:/mapper/*Mapper.xmlconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

sharding.yaml

dataSources:ds0:driverClassName: com.mysql.cj.jdbc.DriverdataSourceClassName: com.alibaba.druid.pool.DruidDataSourceurl: jdbc:mysql://xxxusername: testpassword: testds1:driverClassName: com.mysql.cj.jdbc.DriverdataSourceClassName: com.alibaba.druid.pool.DruidDataSourceurl: jdbc:mysql://yyyusername: testpassword: testrules:- !SHARDINGtables:order:actualDataNodes: ds${0..1}.order_${0..3}tableStrategy:standard:shardingColumn: order_idshardingAlgorithmName: order-id-inlinedatabaseStrategy:standard:shardingColumn: user_idshardingAlgorithmName: db-inlinebindingTables:- ordershardingAlgorithms:db-inline:type: INLINEprops:algorithm-expression: ds${user_id % 2}order-id-inline:type: INLINEprops:algorithm-expression: order_${order_id % 4}props:sql-show: true

实体类

@Data
@TableName("`order`") // MyBatis-Plus 表名（与分片表名一致）
public class Order {@TableId(type = IdType.ASSIGN_ID) // 手动输入主键（由雪花算法生成）private Long orderId; private Long userId;private BigDecimal amount;private LocalDateTime createTime;
}

Mapper

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

Service

@Service
@Slf4j
public class OrderService {@Autowiredprivate OrderMapper orderMapper;@Transactionalpublic int createOrder(Long userId, BigDecimal amount) {SnowflakeIdGenerator generator = new SnowflakeIdGenerator(1, 1);Order order = new Order();order.setOrderId(generator.nextId()); // 自动生成 BIGINT 类型的雪花 IDorder.setUserId(userId);order.setAmount(amount);log.info("即将插入的order: {}", JSON.toJSONString(order));// MyBatis-Plus 插入return orderMapper.insert(order);}public List<Order> getOrderById(Long orderId, Long userId) {log.info("查询订单，orderId: {}", orderId);QueryWrapper<Order> wrapper = new QueryWrapper<>();wrapper.eq("user_id", userId);wrapper.eq("order_id", orderId);return orderMapper.selectList(wrapper);}
}

雪花算法工具

public class SnowflakeIdGenerator {// 起始时间戳（2024-01-01 00:00:00）private static final long TWEPOCH = 1704067200000L;// 工作机器 ID（10位，范围：0~1023）private final long workerId;// 数据中心 ID（10位中的高位，范围：0~1023）private final long datacenterId;// 序列号（12位，范围：0~4095）private long sequence = 0L;// 上次生成 ID 的时间戳（毫秒）private long lastTimestamp = -1L;/*** 构造函数（需指定唯一的工作机器 ID 和数据中心 ID）* @param workerId 工作机器 ID（0~1023）* @param datacenterId 数据中心 ID（0~1023）*/public SnowflakeIdGenerator(long workerId, long datacenterId) {// 校验参数范围if (workerId > 1023 || workerId < 0) {throw new IllegalArgumentException("Worker ID must be between 0 and 1023");}if (datacenterId > 1023 || datacenterId < 0) {throw new IllegalArgumentException("Datacenter ID must be between 0 and 1023");}this.workerId = workerId;this.datacenterId = datacenterId;}/*** 生成下一个 ID* @return 64位 BIGINT 类型的雪花算法 ID*/public synchronized long nextId() {long timestamp = System.currentTimeMillis();// 处理时间戳回拨（可选）if (timestamp < lastTimestamp) {throw new RuntimeException("Clock moved backwards. Refusing to generate ID");}// 同一毫秒内生成多个 IDif (timestamp == lastTimestamp) {sequence = (sequence + 1) & 0xFFF; // 12位掩码（0~4095）if (sequence == 0) { // 序列号溢出，等待下一毫秒timestamp = waitNextMillis(lastTimestamp);}} else {sequence = 0L; // 新毫秒，序列号重置}lastTimestamp = timestamp;// 计算 ID（按位拼接）return ((timestamp - TWEPOCH) << 22) // 时间戳（41位）| (datacenterId << 17)       // 数据中心 ID（5位）| (workerId << 12)           // 工作机器 ID（5位）| sequence;                  // 序列号（12位）}/*** 等待下一毫秒（解决同一毫秒内序列号溢出）*/private long waitNextMillis(long lastTimestamp) {long timestamp = System.currentTimeMillis();while (timestamp <= lastTimestamp) {timestamp = System.currentTimeMillis();}return timestamp;}// 测试示例public static void main(String[] args) {// 示例：工作机器 ID=1，数据中心 ID=1（需根据实际环境调整）SnowflakeIdGenerator generator = new SnowflakeIdGenerator(1, 1);for (int i = 0; i < 10; i++) {System.out.println("Generated ID: " + generator.nextId());}}
}

实测

创建

 INFO 35088 --- [ls-backend] [nio-8085-exec-1] ShardingSphere-SQL                       : Logic SQL: INSERT INTO `order`  ( order_id, user_id, amount )  VALUES (  ?, ?, ?  )
INFO 35088 --- [ls-backend] [nio-8085-exec-1] ShardingSphere-SQL                       : Actual SQL: ds1 ::: INSERT INTO `order_0`  ( order_id, user_id, amount )  VALUES (?, ?, ?) ::: [191145194425552896, 987654321095, 8]

查询

INFO 35088 --- [ls-backend] [nio-8085-exec-3] ShardingSphere-SQL                       : Logic SQL: SELECT  order_id,user_id,amount,create_time  FROM `order`      WHERE  (user_id = ? AND order_id = ?)
INFO 35088 --- [ls-backend] [nio-8085-exec-3] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  order_id,user_id,amount,create_time  FROM `order_0`      WHERE  (user_id = ? AND order_id = ?) ::: [987654321095, 191145194425552896]