Sharding-JDBC分库分表简单使用

建表sql

点击查看代码 
CREATE database ds0;
use ds0;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for t_address
-- ----------------------------
DROP TABLE IF EXISTS `t_address`;
CREATE TABLE `t_address` (
  `address_id` bigint(20) NOT NULL,
  `address_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=532651227377807361 DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_2
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_3
-- ----------------------------
DROP TABLE IF EXISTS `t_order_3`;
CREATE TABLE `t_order_3` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_4
-- ----------------------------
DROP TABLE IF EXISTS `t_order_4`;
CREATE TABLE `t_order_4` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_item
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item`;
CREATE TABLE `t_order_item` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
SET FOREIGN_KEY_CHECKS = 1;
 
-- ------------第二个库------------------
CREATE database ds1;
use ds1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for t_address
-- ----------------------------
DROP TABLE IF EXISTS `t_address`;
CREATE TABLE `t_address` (
  `address_id` bigint(20) NOT NULL,
  `address_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=532651227377807361 DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_2
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2`;
CREATE TABLE `t_order_2` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_3
-- ----------------------------
DROP TABLE IF EXISTS `t_order_3`;
CREATE TABLE `t_order_3` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_4
-- ----------------------------
DROP TABLE IF EXISTS `t_order_4`;
CREATE TABLE `t_order_4` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_code` varchar(255) DEFAULT NULL,
  `address_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_order_item
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item`;
CREATE TABLE `t_order_item` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
SET FOREIGN_KEY_CHECKS = 1;

导入依赖

<dependencies>
        <!-- shardingsphere -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!-- for spring namespace -->
        <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.1.1</version>
        </dependency>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
	<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
</dependencies>

application.yml

server:
  port: 9991

spring:
  profiles:
    active: table #使用table.yml

分表使用：application-table.yml

#分表
spring:
  shardingsphere:
    datasource:   #数据源配置
      names: ds0 # 库名
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 1234
    # 分表配置
    sharding:
      default-table-strategy:
        inline:
          sharding-column: user_id  # 分片字段
          algorithm-expression: t_order_${user_id % 4 + 1} # 对user_id取余数 + 1，计算存入哪个表中
      # 配置广播表
      broadcast-tables: t_user
      binding-tables: t_order,t_order_item

      tables:
        t_order:  # 逻辑表名称
          actual-data-nodes: ds0.t_order_$->{1..4}  # 代表真实的表为 t_order_1, t_order_2, t_order_3, t_order4
          key-generator:  # id生成策略
            column: order_id
            type: SNOWFLAKE
          table-strategy:
            inline: #使用inline分片策略
              sharding-column: user_id
              algorithm-expression: t_order_${user_id % 4 + 1}
    props:
      sql.show: true

分库使用：application-table.yml

# 分表
#分表
spring:
  shardingsphere:
    datasource:   #数据源配置
      names: ds0 # 库名
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 1234
    # 分表配置
    sharding:
      default-table-strategy:
        inline:
          sharding-column: user_id  # 分片字段
          algorithm-expression: t_order_${user_id % 4 + 1} # 对user_id取余数 + 1，计算存入哪个表中
      # 配置广播表
      broadcast-tables: t_user
      binding-tables: t_order,t_order_item

      tables:
        t_order:  # 逻辑表名称
          actual-data-nodes: ds0.t_order_$->{1..4}  # 代表真实的表为 t_order_1, t_order_2, t_order_3, t_order4
          key-generator:  # id生成策略
            column: order_id
            type: SNOWFLAKE
            props:
              worker:
                id: 123
          table-strategy:
            inline: #使用inline分片策略
              sharding-column: user_id
              algorithm-expression: t_order_${user_id % 4 + 1}
    props:
      sql.show: true

如果分库就使用table.yml，如果分表就使用db.yml

mapper和pojo层
image

Controller层代码


/**
 * @author Jodie
 */
@RestController
@RequestMapping("/test")
@Slf4j
public class TestController {

    private final TUserMapper userMapper;
    private final TOrderMapper orderMapper;
    private final TOrderItemMapper orderItemMapper;
    private final TAddressMapper addressMapper;

    public TestController(TUserMapper userMapper, TOrderMapper orderMapper, TOrderItemMapper orderItemMapper, TAddressMapper addressMapper) {
        this.userMapper = userMapper;
        this.orderMapper = orderMapper;
        this.orderItemMapper = orderItemMapper;
        this.addressMapper = addressMapper;
    }


    @RequestMapping(value = "/user")
    public String testUser() {
        for (int i = 1; i <= 10; i++) {
            TUser user = new TUser();
            user.setUserId(Long.valueOf(i));
            user.setUserName("张三" + i);
            userMapper.insert(user);
        }
        return "sucess";
    }

    @RequestMapping("/order")
    public String testOrder() {
        for (int i = 1; i <= 10; i++) {
            TOrder order = new TOrder();
            order.setUserId(Long.valueOf(i));
            order.setOrderCode("acb" + i);
            order.setAddressId(String.valueOf(i));
            orderMapper.insert(order);
        }
        return "sucess";
    }

    @RequestMapping("/listOrder")
    public Object testListOrder() {
        List<TOrder> tOrders = orderMapper.selectList(new LambdaQueryWrapper<TOrder>()
                .in(TOrder::getUserId, Arrays.asList(1, 2, 3, 4, 5))
                .orderByAsc(TOrder::getUserId));
        return tOrders;
    }


    @RequestMapping("/{one}")
    public Object testOner(@PathVariable String one) {
        return orderMapper.selectById(Long.valueOf(one));
    }
}

本次使用table.yml启动，访问/order发现会根据userId取余+1觉得最终数据落入哪张表
表1：
image
表2：
image

参考 https://lsqingfeng.blog.csdn.net/article/details/110433948

posted @   姜晓姜晓  阅读(60)  评论(0)  编辑  收藏  举报
相关博文：
· Oracle数据库实现分页查询
· EasyExcel实现100w数据导入导出
· Sharding-JDBC学习记录
· ShardingSphere分库分表实战Demo
· 060_分库分表-年月案例
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示