分库分表之_分库分表 + 复杂查询

最新推荐文章于 2024-05-13 02:49:30 发布

HealerJean.

最新推荐文章于 2024-05-13 02:49:30 发布

阅读量3.7k

点赞数 1

分类专栏： Database

本文链接：https://blog.csdn.net/u012954706/article/details/105253421

版权

前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

代码配置暂时和和分库分表之_分库分表相同。但是为了测试下面的join查询将user表的数量调整到了和company表的数量一致，以及给company添加了一个新的字段ref_user_id

1、开始Demo

1.1、`hlj-08-sharding_db_table-range_group.sql`


drop database if exists ds_0;
create database ds_0 character set 'utf8' collate 'utf8_general_ci';
use ds_0;

drop table if exists user_0;
create table `user_0`
(
  id          bigint(20) unsigned not null,
  city        varchar(20)         not null default '',
  name        varchar(20)         not null default '',
  age         int(11)             not null default 0,
  status      int(10)             not null default '0' comment '状态',
  create_time datetime            not null default current_timestamp comment '创建时间',
  update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key (id)
) engine = innodb
  default charset = utf8;

drop table if exists user_1;
create table `user_1`
(
  id          bigint(20) unsigned not null,
  city        varchar(20)         not null default '',
  name        varchar(20)         not null default '',
  age         int(11)             not null default 0,
  status      int(10)             not null default '0' comment '状态',
  create_time datetime            not null default current_timestamp comment '创建时间',
  update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key (id)
) engine = innodb
  default charset = utf8;




drop table if exists company_0;
create table `company_0`
(
  id                   bigint(20) unsigned not null comment '主键',
  ref_user_id          bigint(20) unsigned not null comment '主键',
  name                 varchar(20)         not null default '' comment '企业名称',
  company_name_english varchar(128)        not null default '' comment '企业英文名称',
  status               int(10)             not null default '0' comment '状态',
  create_time          datetime            not null default current_timestamp comment '创建时间',
  update_time          datetime            not null default current_timestamp on update current_timestamp comment '修改时间',

  primary key (id)
) engine = innodb
  default charset = utf8;

drop table if exists company_1;
create table `company_1`
(
  id                   bigint(20) unsigned not null comment '主键',
  ref_user_id          bigint(20) unsigned not null comment '主键',
  name                 varchar(20)         not null default '' comment '企业名称',
  company_name_english varchar(128)        not null default '' comment '企业英文名称',
  status               int(10)             not null default '0' comment '状态',
  create_time          datetime            not null default current_timestamp comment '创建时间',
  update_time          datetime            not null default current_timestamp on update current_timestamp comment '修改时间',

  primary key (id)
) engine = innodb
  default charset = utf8;



CREATE TABLE `demo_entity`
(
  id            bigint(20) unsigned NOT NULL COMMENT '主键',
  `name`        varchar(64)         NOT NULL,
  `phone`       varchar(20)                  DEFAULT '' COMMENT '手机号',
  `email`       varchar(64)                  DEFAULT '' COMMENT '邮箱',
  `age`         int(10)                      DEFAULT NULL,
  `status`      varchar(8)          NOT NULL COMMENT '状态',
  `create_user` bigint(16) unsigned          DEFAULT NULL COMMENT '创建人',
  `create_name` varchar(64)                  DEFAULT '' COMMENT '创建人名称',
  `create_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user` bigint(16) unsigned          DEFAULT NULL COMMENT '更新人',
  `update_name` varchar(64)                  DEFAULT '' COMMENT '更新人名称',
  `update_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;



drop database if exists ds_1;
create database ds_1 character set 'utf8' collate 'utf8_general_ci';
use ds_1;

drop table if exists user_0;
create table `user_0`
(
  id          bigint(20) unsigned not null,
  city        varchar(20)         not null default '',
  name        varchar(20)         not null default '',
  age         int(11)             not null default 0,
  status      int(10)             not null default '0' comment '状态',
  create_time datetime            not null default current_timestamp comment '创建时间',
  update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key (id)
) engine = innodb
  default charset = utf8;

drop table if exists user_1;
create table `user_1`
(
  id          bigint(20) unsigned not null,
  city        varchar(20)         not null default '',
  name        varchar(20)         not null default '',
  age         int(11)             not null default 0,
  status      int(10)             not null default '0' comment '状态',
  create_time datetime            not null default current_timestamp comment '创建时间',
  update_time datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  primary key (id)
) engine = innodb
  default charset = utf8;



drop table if exists company_0;
create table `company_0`
(
  id                   bigint(20) unsigned not null comment '主键',
  ref_user_id          bigint(20) unsigned not null comment '主键',
  name                 varchar(20)         not null default '' comment '企业名称',
  company_name_english varchar(128)        not null default '' comment '企业英文名称',
  status               int(10)             not null default '0' comment '状态',
  create_time          datetime            not null default current_timestamp comment '创建时间',
  update_time          datetime            not null default current_timestamp on update current_timestamp comment '修改时间',

  primary key (id)
) engine = innodb
  default charset = utf8;

drop table if exists company_1;
create table `company_1`
(
  id                   bigint(20) unsigned not null comment '主键',
  ref_user_id          bigint(20) unsigned not null comment '主键',
  name                 varchar(20)         not null default '' comment '企业名称',
  company_name_english varchar(128)        not null default '' comment '企业英文名称',
  status               int(10)             not null default '0' comment '状态',
  create_time          datetime            not null default current_timestamp comment '创建时间',
  update_time          datetime            not null default current_timestamp on update current_timestamp comment '修改时间',

  primary key (id)
) engine = innodb
  default charset = utf8;

1.1.1、数据库图文

1585561704857

1.2、依赖


<!--shardingsphere-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

1.3、配置文件：`application.properties`

server.port=8888


# 配置 mybatis的一些配置，也可以在 application.properties 中配置，如果配置了就不需要了mybatis.xml
#mybatis-plus.config-location=classpath:mybatis.xml
#Maven 多模块项目的扫描路径需以 classpath*: 开头 （即加载多个 jar 包下的 XML 文件）
mybatis-plus.mapper-locations=classpath*:mapper/*.xml
mybatis-plus.type-aliases-package=com.healerjean.proj.pojo
##主键类型  0:"数据库ID自增，非常大", 1:"用户输入ID（如果用户不输入，则默认是0）",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
mybatis-plus.id-type: 0
#字段策略 0:"忽略判断",1:"非 NULL 判断"),2:"非空判断"
mybatis-plus.field-strategy: 2
#数据库大写下划线转换
mybatis-plus.capital-mode: true
mybatis-plus.refresh-mapper: true


# #当遇到同样名字的时候，是否允许覆盖注册
spring.main.allow-bean-definition-overriding=true
# 显示SQL
spring.shardingsphere.props.sql.show=true



##############################
## 分库分表
#############################
spring.shardingsphere.datasource.names=ds0,ds1

# 数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds_0?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds_1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456


# 分库配置
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.healerjean.proj.config.datasource.CustomShardingDBAlgorithm


# user  company 分表
# user_0,user_1,user_2（自定义分表算法）
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->{0..1}
spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.healerjean.proj.config.datasource.CustomShardingTableAlgorithm
# company_0,company_1 （inline分表策略 表达式 id%2）
spring.shardingsphere.sharding.tables.company.actual-data-nodes=ds$->{0..1}.company_$->{0..1}
spring.shardingsphere.sharding.tables.company.database-strategy.inline.sharding-column=ref_user_id
spring.shardingsphere.sharding.tables.company.database-strategy.inline.algorithm-expression=ds${ref_user_id.longValue() % 2}
spring.shardingsphere.sharding.tables.company.table-strategy.inline.sharding-column=ref_user_id
spring.shardingsphere.sharding.tables.company.table-strategy.inline.algorithm-expression=company_${ref_user_id.longValue() % 2}


## 默认数据源指定（不分库的表）
spring.shardingsphere.sharding.default-data-source-name=ds0

1.4、具体测试方法和类

1.4.1、实体类

1.4.1.1、`User.java`

@Data
@Accessors(chain = true)
public class User implements Serializable {
   
    private static final long serialVersionUID = 1L;

    /** 主键  */
    private Long id;
    private String name;
    private String city;
    private Integer age;
    private String status;
    private Date createTime;
    private Date updateTime;
}

1.4.1.2、`Company.java`

@Data
public class Company {
   

	private Long id;
	private Long refUserId;
	private String name;
	private String companyNameEnglish;
	private String status;
	private Date createTime;
	private Date updateTime;
}

1.4.1.3、`DemoEntity.java`

@Data
@Accessors(chain = true)
public class DemoEntity implements Serializable {
   
    private static final long serialVersionUID = 1L;

    private Long id;
    private String name;

最低0.47元/天解锁文章

HealerJean.

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
分库分表之_分库分表 + 复杂查询

前言Github：https://github.com/HealerJean博客：http://blog.healerjean.com代码配置暂时和和分库分表之_分库分表相同。但是为了测试下面的join查询将user表的数量调整到了和company表的数量一致，以及给company添加了一个新的字段ref_user_id1、开始Demo1.1、hlj-08-sharding_db_t...
复制链接

扫一扫