【java_wxid项目】【第八章】【Apache ShardingSphere集成】

SpringBoot与shardingsphere分库分表实战(1) 小编利用空余时间整理了一份《MySQL性能调优手册》,初衷也很简单,就是希望能够帮助到大家,减轻大家的负担和节省时间。关于这个,给大家看一份学习大纲(PDF)文件,每一个分支里面会有详细的介绍。这里都是以图片形式展示介绍,如要下载原文件以及更多的性能调优笔记(MySQL+Tomcat+JVM)!《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》点击传送门,即可获取!3306/blog? 阅读详情

主项目链接:https://gitee.com/java_wxid/java_wxid
项目架构及博文总结:

项目模块:
前期规划,实现部分

java_wxid   
├── demo                                                            // 演示模块
│     └── 模块名称:apache-mybatis-demo模块                            //Apache Mybatis集成(已实现并有博文总结)
│     └── 模块名称:apache-shardingsphere-demo模块                     //Apache ShardingSphere集成(已实现并有博文总结)
│     └── 模块名称:design-demo模块                                    //设计模式实战落地(已实现并有博文总结)
│     └── 模块名称:elasticsearch-demo模块                             //ElasticSearch集成(已实现并有博文总结)
│     └── 模块名称:mongodb-demo模块                                   //MongoDB集成(已实现并有博文总结)
│     └── 模块名称:redis-demo模块                                     //Redis集成(已实现并有博文总结)
│     └── 模块名称:spring-boot-demo模块                               //Spring Boot快速构建应用(已实现并有博文总结)
│     └── 模块名称:spring-cloud-alibaba-nacos-demo模块                //Spring Cloud Alibaba Nacos集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-alibaba-seata-demo模块                //Spring Cloud Alibaba Seata集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-alibaba-sentinel-demo模块             //Spring Cloud Alibaba Sentinel集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-gateway-demo模块                      //Spring Cloud Gateway集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-hystrix-demo模块                      //Spring Cloud Hystrix集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-open-feign-demo模块                   //Spring Cloud Open Feign集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-ribbon-demo模块                       //Spring Cloud Ribbon集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-security-oauth2-demo模块              //Spring Cloud Security Oauth2集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-security-oauth2-sso-client-demo模块   //Spring Cloud Security Oauth2集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-skywalking-demo模块                   //Spring Cloud Skywalking集成(已实现并有博文总结)
│     └── 模块名称:spring-cloud-stream-demo模块                       //Spring Cloud Stream集成(已实现并有博文总结)
│     └── 模块名称:swagger-demo模块                                   //springfox-swagger2集成(已实现并有博文总结)
│     └── 模块名称:xxl-job模块                                        //xxl-job集成(已实现并有博文总结)
│     └── 模块名称:apache-spark-demo模块                              //Apache Spark集成
│     └── 模块名称:etl-hdfs-hive-hbase-demo模块                       //ETL、HDFS、Hive、Hbase集成
│     └── 模块名称:ddd-mode-demo模块                                  //DDD领域设计
│     └── 模块名称:netty-demo模块                                     //Netty集成
│     └── 模块名称:vue-demo模块                                       //前端vue集成
├── document                                                        // 文档
│     └── JavaKnowledgeDocument                                     //java知识点
│           └── java基础知识点.md                     
│           └── mq知识点.md
│           └── mysql知识点.md
│           └── redis知识点.md
│           └── springcould知识点.md
│           └── spring知识点.md
│     └── FounderDocument                                           //创始人
│           └── 创始人.md

系列文章:快速集成各种微服务相关的技术,帮助大家可以快速集成到自己的项目中,节约开发时间。
提示:系列文章还未全部完成,后续的文章,会慢慢补充进去的。

创建apache-shardingsphere-demo项目

项目代码:https://gitee.com/java_wxid/java_wxid/tree/master/demo/apache-shardingsphere-demo
项目结构如下(示例):
在这里插入图片描述

修改pom.xml

代码如下(示例):

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>apache-shardingsphere-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>apache-shardingsphere-demo</name>
    <description>Demo project for Spring Boot</description>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>2.3.1.RELEASE</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>2.4.5</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.4.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

修改ApacheShardingsphereDemoApplication

代码如下(示例):

package com.example.apacheshardingspheredemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.example.apacheshardingspheredemo.mapper")
@SpringBootApplication
public class ApacheShardingsphereDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(ApacheShardingsphereDemoApplication.class, args);
    }

}

创建application.properties

真正运行的配置
代码如下(示例):

#配置数据源
spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#course是逻辑表名,actual-data-nodes是真实表分布,也就是sharding里面的逻辑表course对应的是m0库中course_1和course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
#主键生成策略,cid作为主键
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#使用雪花算法生成主键
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#雪花算法需要有一个参数worker.id,这个是可选的
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#表策略:选择inline依赖策略,sharding-column分片键cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#表策略:algorithm-expression分片算法cid模2加1
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#其他运行属性
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
创建application01.properties

代码如下(示例):

#配置数据源
spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#course是逻辑表名,actual-data-nodes是真实表分布,也就是sharding里面的逻辑表course对应的是m0库中course_1和course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
#主键生成策略,cid作为主键
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#使用雪花算法生成主键
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#雪花算法需要有一个参数worker.id,这个是可选的
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#表策略:选择inline依赖策略,sharding-column分片键cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#表策略:algorithm-expression分片算法cid模2加1
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#其他运行属性
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true


创建application02.properties

代码如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37


spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://110.42.239.246:3306/coursedb2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=591e242ca29b9c37

#真实表分布,分库,分表
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#inline分片策略
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}

#spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}
#standard标准分片策略
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.roy.shardingDemo.algorithem.MyPreciseTableShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.roy.shardingDemo.algorithem.MyRangeTableShardingAlgorithm

spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.roy.shardingDemo.algorithem.MyPreciseDSShardingAlgorithm
spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.roy.shardingDemo.algorithem.MyRangeDSShardingAlgorithm
#complex复杂分片策略
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns= cid, user_id
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.roy.shardingDemo.algorithem.MyComplexTableShardingAlgorithm
#
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.sharding-columns=cid, user_id
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.algorithm-class-name=com.roy.shardingDemo.algorithem.MyComplexDSShardingAlgorithm
#hint强制路由策略
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.roy.shardingDemo.algorithem.MyHintTableShardingAlgorithm
#广播表配置
spring.shardingsphere.sharding.broadcast-tables=t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true


创建application03.properties

代码如下(示例):

spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37


spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m1.t_dict_$->{1..2}

spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}

spring.shardingsphere.sharding.tables.user.actual-data-nodes=m1.t_user_$->{1..2}
spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}
#绑定表示例
spring.shardingsphere.sharding.binding-tables[0]=user,t_dict

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true


创建application04.properties

代码如下(示例):

#配置主从数据源,要基于MySQL主从架构。
spring.shardingsphere.datasource.names=m0,s0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://139.224.137.74:3307/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=ca0a997ee4770063

spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://106.14.132.94:3308/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=JHWLXeT56iJiBwDG
#读写分离规则, m0 主库,s0 从库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names[0]=s0
#基于读写分离的表分片
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=ds0.t_dict

spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true


创建Course

代码如下(示例):

package com.example.apacheshardingspheredemo.entity;

/**
 * @Author: liaozhiwei
 * @Description: TODO
 * @Date: Created in 21:16 2022/9/2
 */
public class Course {

    private Long cid;
    private String cname;
    private Long userId;
    private String cstatus;

    public Long getCid() {
        return cid;
    }

    public void setCid(Long cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getCstatus() {
        return cstatus;
    }

    public void setCstatus(String cstatus) {
        this.cstatus = cstatus;
    }

    @Override
    public String toString() {
        return "Course{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", userId=" + userId +
                ", cstatus='" + cstatus + '\'' +
                '}';
    }
}

创建Dict

代码如下(示例):

package com.example.apacheshardingspheredemo.entity;

import com.baomidou.mybatisplus.annotation.TableName;

/**
 * @Author: liaozhiwei
 * @Description: TODO
 * @Date: Created in 21:16 2022/9/2
 */
@TableName("t_dict")
public class Dict {
    private Long dictId;
    private String ustatus;
    private String uvalue;

    public Long getDictId() {
        return dictId;
    }

    public void setDictId(Long dictId) {
        this.dictId = dictId;
    }

    public String getUstatus() {
        return ustatus;
    }

    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }

    public String getUvalue() {
        return uvalue;
    }

    public void setUvalue(String uvalue) {
        this.uvalue = uvalue;
    }

    @Override
    public String toString() {
        return "Dict{" +
                "dictId=" + dictId +
                ", ustatus='" + ustatus + '\'' +
                ", uvalue='" + uvalue + '\'' +
                '}';
    }
}

创建User

代码如下(示例):

package com.example.apacheshardingspheredemo.entity;

import com.baomidou.mybatisplus.annotation.TableName;

/**
 * @Author: liaozhiwei
 * @Description: TODO
 * @Date: Created in 21:16 2022/9/2
 */
@TableName("user")
public class User {

    private Long userId;
    private String username;
    private String ustatus;
    private int uage;

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUstatus() {
        return ustatus;
    }

    public void setUstatus(String ustatus) {
        this.ustatus = ustatus;
    }

    public int getUage() {
        return uage;
    }

    public void setUage(int uage) {
        this.uage = uage;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", username='" + username + '\'' +
                ", ustatus='" + ustatus + '\'' +
                ", uage=" + uage +
                '}';
    }
}

创建CourseMapper

代码如下(示例):

package com.example.apacheshardingspheredemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.apacheshardingspheredemo.entity.Course;

/**
 * @Author: liaozhiwei
 * @Description: TODO
 * @Date: Created in 21:16 2022/9/2
 */
public interface CourseMapper extends BaseMapper<Course> {
}

创建DictMapper

代码如下(示例):

package com.example.apacheshardingspheredemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.apacheshardingspheredemo.entity.Dict;

/**
 * @Author: liaozhiwei
 * @Description: TODO
 * @Date: Created in 21:16 2022/9/2
 */
public interface DictMapper extends BaseMapper<Dict> {
}

创建UserMapper

代码如下(示例):

package com.example.apacheshardingspheredemo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.apacheshardingspheredemo.entity.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;

/**
 * @Author: liaozhiwei
 * @Description: TODO
 * @Date: Created in 21:16 2022/9/2
 */
public interface UserMapper extends BaseMapper<User> {

    @Select("select u.user_id,u.username,d.uvalue ustatus from user u left join t_dict d on u.ustatus = d.ustatus")
    public List<User> queryUserStatus();
}

创建MyComplexDSShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @Author: liaozhiwei
 * @Description: complex复杂分片策略 多字段的分库分表 库策略
 * @Date: Created in 21:16 2022/9/2
 */
public class MyComplexDSShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
    //    SELECT  cid,cname,user_id,cstatus  FROM course WHERE  cid BETWEEN ? AND ? AND user_id = ?
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");//cid BETWEEN ? AND ?
        Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");// user_id = ?

        Long upperVal = cidRange.upperEndpoint();//上限
        Long lowerVal = cidRange.lowerEndpoint();//下限

        List<String> res = new ArrayList<>();

        for(Long userId: userIdCol){
            BigInteger userIdB = BigInteger.valueOf(userId);
            //实现库策略 course_{userID%2+1}
            BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));

            res.add("m"+target);
        }

        return res;
    }
}

创建MyComplexTableShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.complex.ComplexKeysShardingValue;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @Author: liaozhiwei
 * @Description: complex复杂分片策略 多字段的分库分表 表策略
 * @Date: Created in 21:16 2022/9/2
 */
public class MyComplexTableShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {

    //    SELECT  cid,cname,user_id,cstatus  FROM course WHERE  cid BETWEEN ? AND ? AND user_id = ?
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        Range<Long> cidRange = shardingValue.getColumnNameAndRangeValuesMap().get("cid");
        Collection<Long> userIdCol = shardingValue.getColumnNameAndShardingValuesMap().get("user_id");

        Long upperVal = cidRange.upperEndpoint();
        Long lowerVal = cidRange.lowerEndpoint();

        List<String> res = new ArrayList<>();

        for(Long userId: userIdCol){
            BigInteger userIdB = BigInteger.valueOf(userId);
            //实现表策略 course_{userID%2+1}
            BigInteger target = (userIdB.mod(new BigInteger("2"))).add(new BigInteger("1"));

            res.add(shardingValue.getLogicTableName()+"_"+target);
        }

        return res;
    }
}
创建MyHintTableShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * @Author: liaozhiwei
 * @Description: hint强制路由策略
 * @Date: Created in 21:16 2022/9/2
 */
public class MyHintTableShardingAlgorithm implements HintShardingAlgorithm<Integer> {
    /**
     * 可以只查某个表
     * @param availableTargetNames
     * @param shardingValue
     * @return
     */
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue) {
        String key = shardingValue.getLogicTableName() + "_" + shardingValue.getValues().toArray()[0];
        if(availableTargetNames.contains(key)){
            return Arrays.asList(key);
        }
        throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
    }
}


创建MyPreciseDSShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.math.BigInteger;
import java.util.Collection;

/**
 * @Author: liaozhiwei
 * @Description: 库策略
 * @Date: Created in 21:16 2022/9/2
 */
public class MyPreciseDSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    //select * from course where cid = ? or cid in (?,?)
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String logicTableName = shardingValue.getLogicTableName();
        String cid = shardingValue.getColumnName();
        Long cidValue = shardingValue.getValue();
        BigInteger shardingValueB = BigInteger.valueOf(cidValue);
        //实现库策略 m_$->{cid%2+1)
        BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
        String key = "m" + resB;
        if(availableTargetNames.contains(key)){
            return key;
        }
        //couse_1, course_2
        throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
    }
}


创建MyPreciseTableShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.math.BigInteger;
import java.util.Collection;

/**
 * @Author: liaozhiwei
 * @Description: 表策略:standard标准分片策略
 * @Date: Created in 21:16 2022/9/2
 */
public class MyPreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    //select * from course where cid = ? or cid in (?,?)

    /**
     *
     * @param availableTargetNames 真实表的名称
     * @param shardingValue 条件的值(占位符?的值)
     * @return
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String logicTableName = shardingValue.getLogicTableName();
        String cid = shardingValue.getColumnName();
        Long cidValue = shardingValue.getValue();
        BigInteger shardingValueB = BigInteger.valueOf(cidValue);
        //实现表策略 course_$->{cid%2+1)
        BigInteger resB = (shardingValueB.mod(new BigInteger("2"))).add(new BigInteger("1"));
        String key = logicTableName+"_"+resB;
        if(availableTargetNames.contains(key)){
            return key;
        }
        //couse_1, course_2
        throw new UnsupportedOperationException("route "+ key +" is not supported ,please check your config");
    }
}


创建MyRangeDSShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * @Author: liaozhiwei
 * @Description: 库策略:standard标准分片策略 范围查询
 * @Date: Created in 21:16 2022/9/2
 */
public class MyRangeDSShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        //select * from course where cid between 1 and 100;
        Long upperVal = shardingValue.getValueRange().upperEndpoint();//上限 100
        Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//下限 1
        //可以进行业务分库,这里不作处理了

        //可以拿到它的真实库名
        String logicTableName = shardingValue.getLogicTableName();

        //我这里直接写死返回m1,m2
        return Arrays.asList("m1","m2");
    }
}


创建MyRangeTableShardingAlgorithm

代码如下(示例):

package com.example.apacheshardingspheredemo.algorithem;

import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;

import java.util.Arrays;
import java.util.Collection;

/**
 * @Author: liaozhiwei
 * @Description: 表策略:standard标准分片策略 范围查询
 * @Date: Created in 21:16 2022/9/2
 */
public class MyRangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
        //select * from course where cid between 1 and 100;
        Long upperVal = shardingValue.getValueRange().upperEndpoint();//上限 between 1 and 100中的100
        Long lowerVal = shardingValue.getValueRange().lowerEndpoint();//下限 between 1 and 100中的1
        //可以进行业务分表,这里不作处理了
        String logicTableName = shardingValue.getLogicTableName();
        //直接写死返回真实表course_1、course_2
        return Arrays.asList(logicTableName+"_1",logicTableName+"_2");
    }
}


创建course.sql

代码如下(示例):

-- 在三个库中创建
CREATE TABLE course_1 (
	cid BIGINT(20) PRIMARY KEY,
	cname VARCHAR(50) NOT NULL,
	user_id BIGINT(20) NOT NULL,
	cstatus varchar(10) NOT NULL
);

CREATE TABLE course_2 (
	cid BIGINT(20) PRIMARY KEY,
	cname VARCHAR(50) NOT NULL,
	user_id BIGINT(20) NOT NULL,
	cstatus varchar(10) NOT NULL
);
创建t_dict.sql

代码如下(示例):

-- 在三个库中创建
CREATE TABLE `t_dict`  (
  `dict_id` bigint(0) PRIMARY KEY NOT NULL,
  `ustatus` varchar(100) NOT NULL,
  `uvalue` varchar(100) NOT NULL
);
-- 在userdb中创建
CREATE TABLE `t_dict_1`  (
  `dict_id` bigint(0) PRIMARY KEY NOT NULL,
  `ustatus` varchar(100) NOT NULL,
  `uvalue` varchar(100) NOT NULL
);
CREATE TABLE `t_dict_2`  (
  `dict_id` bigint(0) PRIMARY KEY NOT NULL,
  `ustatus` varchar(100) NOT NULL,
  `uvalue` varchar(100) NOT NULL
);
创建t_user.sql

代码如下(示例):

--在userdb中创建
CREATE TABLE `t_user`  (
  `user_id` bigint(0) PRIMARY KEY NOT NULL,
  `username` varchar(100) NOT NULL,
  `ustatus` varchar(50) NOT NULL,
  `uage` int(3)
);

CREATE TABLE `t_user_1`  (
  `user_id` bigint(0) PRIMARY KEY NOT NULL,
  `username` varchar(100) NOT NULL,
  `ustatus` varchar(50) NOT NULL,
  `uage` int(3)
);
CREATE TABLE `t_user_2`  (
  `user_id` bigint(0) PRIMARY KEY NOT NULL,
  `username` varchar(100) NOT NULL,
  `ustatus` varchar(50) NOT NULL,
  `uage` int(3)
);
修改ApacheShardingsphereDemoApplicationTests

代码如下(示例):

package com.example.apacheshardingspheredemo;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.apacheshardingspheredemo.entity.Course;
import com.example.apacheshardingspheredemo.entity.Dict;
import com.example.apacheshardingspheredemo.entity.User;
import com.example.apacheshardingspheredemo.mapper.CourseMapper;
import com.example.apacheshardingspheredemo.mapper.DictMapper;
import com.example.apacheshardingspheredemo.mapper.UserMapper;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
class ApacheShardingsphereDemoApplicationTests {

    @Resource
    CourseMapper courseMapper;
    @Resource
    DictMapper dictMapper;
    @Resource
    UserMapper userMapper;

    @Test
    public void addCourse(){
        for(int i = 0 ; i < 10 ; i ++){
            Course c = new Course();
//            c.setCid(Long.valueOf(i));//配置文件里面选择了雪花算法生成主键id,所以这里就不需要cid自己生成了
            c.setCname("shardingsphere");
            c.setUserId(Long.valueOf(""+(1000+i)));
            c.setCstatus("1");
            courseMapper.insert(c);
        }
    }

    @Test
    public void queryCourse(){
        //select * from course
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.orderByDesc("cid");
        wrapper.eq("cid",553684818806706177L);
//        wrapper.in()
        List<Course> courses = courseMapper.selectList(wrapper);
        courses.forEach(course -> System.out.println(course));
    }

    @Test
    public void queryOrderRange(){
        //select * from course
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.between("cid",553684818806706177L,553684819184193537L);
//        wrapper.in()
        List<Course> courses = courseMapper.selectList(wrapper);
        courses.forEach(course -> System.out.println(course));
    }

    @Test
    public void queryCourseComplex(){
        QueryWrapper<Course> wrapper = new QueryWrapper<>();
        wrapper.between("cid",553684818806706177L,553684819184193537L);
        wrapper.eq("user_id",1009L);
//        wrapper.in()
        List<Course> courses = courseMapper.selectList(wrapper);
        courses.forEach(course -> System.out.println(course));
    }

    @Test
    public void queryCourseByHint(){
        HintManager hintManager = HintManager.getInstance();
        hintManager.addTableShardingValue("course",2);
        List<Course> courses = courseMapper.selectList(null);
        courses.forEach(course -> System.out.println(course));
        hintManager.close();
    }

    @Test
    public void addDict(){
        Dict d1 = new Dict();
        d1.setUstatus("1");
        d1.setUvalue("正常");
        dictMapper.insert(d1);

        Dict d2 = new Dict();
        d2.setUstatus("0");
        d2.setUvalue("不正常");
        dictMapper.insert(d2);

        for(int i = 0 ; i < 10 ; i ++){
            User user = new User();
            user.setUsername("user No "+i);
            user.setUstatus(""+(i%2));
            user.setUage(i*10);
            userMapper.insert(user);
        }
    }

    @Test
    public void queryUserStatus(){
        List<User> users = userMapper.queryUserStatus();
        users.forEach(user -> System.out.println(user));
    }

    @Test
    public void addDictByMS(){
        Dict d1 = new Dict();
        d1.setUstatus("1");
        d1.setUvalue("正常");
        dictMapper.insert(d1);

        Dict d2 = new Dict();
        d2.setUstatus("0");
        d2.setUvalue("不正常");
        dictMapper.insert(d2);
    }

    @Test
    public void queryDictByMS(){
        List<Dict> dicts = dictMapper.selectList(null);
        dicts.forEach(dict -> System.out.println(dict));
    }

}

校验Apache ShardingSphere是否正常工作

分表插入

把application01.properties的配置复制粘贴到application.properties里面
如下图(示例):
在这里插入图片描述
配置如下(示例):

#配置数据源
spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#course是逻辑表名,actual-data-nodes是真实表分布,也就是sharding里面的逻辑表course对应的是m0库中course_1和course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
#主键生成策略,cid作为主键
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
#使用雪花算法生成主键
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#雪花算法需要有一个参数worker.id,这个是可选的
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#表策略:选择inline依赖策略,sharding-column分片键cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#表策略:algorithm-expression分片算法cid模2加1
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#其他运行属性
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true


生成coursedb库,同时把course.sql执行一下,生成二个表course_1,course_2
如下图(示例):
在这里插入图片描述

去ApacheShardingsphereDemoApplicationTests运行addCourse方法
如下图(示例):
在这里插入图片描述

控制台打印的日志:
如下(示例):

org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.1.RELEASE)

2022-09-03 21:45:11.279  INFO 31084 --- [           main] ApacheShardingsphereDemoApplicationTests : Starting ApacheShardingsphereDemoApplicationTests on DESKTOP-42NQPIS with PID 31084 (started by Machenike in W:\Personal\java_wxid\demo\apache-shardingsphere-demo)
2022-09-03 21:45:11.279  INFO 31084 --- [           main] ApacheShardingsphereDemoApplicationTests : No active profile set, falling back to default profiles: default
2022-09-03 21:45:12.108  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'stringToNoneShardingStrategyConfigurationConverter' of type [org.apache.shardingsphere.spring.boot.converter.StringToNoneShardingStrategyConfigurationConverter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.124  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.sharding-org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.124  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.masterslave-org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.124  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.encrypt-org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.140  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.shadow-org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.140  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere-org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.403  INFO 31084 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$9d45b047] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-03 21:45:12.773  INFO 31084 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2022-09-03 21:45:14.393  INFO 31084 --- [           main] o.a.s.core.log.ConfigurationLogger       : ShardingRuleConfiguration:
tables:
  course:
    actualDataNodes: m1.course_$->{1..2}
    keyGenerator:
      column: cid
      props:
        worker.id: '1'
      type: SNOWFLAKE
    logicTable: course
    tableStrategy:
      inline:
        algorithmExpression: course_$->{cid%2+1}
        shardingColumn: cid

2022-09-03 21:45:14.409  INFO 31084 --- [           main] o.a.s.core.log.ConfigurationLogger       : Properties:
sql.show: 'true'

2022-09-03 21:45:14.462  INFO 31084 --- [           main] ShardingSphere-metadata                  : Loading 1 logic tables' meta data.
2022-09-03 21:45:15.828  INFO 31084 --- [           main] ShardingSphere-metadata                  : Loading 7 tables' meta data.
2022-09-03 21:45:16.730  INFO 31084 --- [           main] ShardingSphere-metadata                  : Meta data load finished, cost 2321 milliseconds.
 _ _   |_  _ _|_. ___ _ |    _ 
| | |\/|_)(_| | |_\  |_)||_|_\ 
     /               |         
                        3.3.2 
2022-09-03 21:45:18.840  WARN 31084 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Course".
2022-09-03 21:45:18.978  WARN 31084 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Dict".
2022-09-03 21:45:19.009  WARN 31084 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.User".
2022-09-03 21:45:19.056  INFO 31084 --- [           main] ApacheShardingsphereDemoApplicationTests : Started ApacheShardingsphereDemoApplicationTests in 8.12 seconds (JVM running for 10.404)
2022-09-03 21:45:19.896  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:19.896  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2d4a0671), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2d4a0671, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1000, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939426555760640])])
2022-09-03 21:45:19.896  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1000, 1, 772939426555760640]
2022-09-03 21:45:20.213  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.213  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@32d1d6c5), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@32d1d6c5, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1001, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939428241870849])])
2022-09-03 21:45:20.213  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1001, 1, 772939428241870849]
2022-09-03 21:45:20.277  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.277  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3e8afc2d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3e8afc2d, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1002, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939428510306304])])
2022-09-03 21:45:20.285  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1002, 1, 772939428510306304]
2022-09-03 21:45:20.359  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.359  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@8ce3f27), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@8ce3f27, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1003, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939428854239233])])
2022-09-03 21:45:20.359  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1003, 1, 772939428854239233]
2022-09-03 21:45:20.440  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.440  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@70805849), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@70805849, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1004, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939429193977856])])
2022-09-03 21:45:20.440  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1004, 1, 772939429193977856]
2022-09-03 21:45:20.511  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.511  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2567c091), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2567c091, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1005, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939429491773441])])
2022-09-03 21:45:20.511  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1005, 1, 772939429491773441]
2022-09-03 21:45:20.573  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.573  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@74bfdd66), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@74bfdd66, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1006, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939429751820288])])
2022-09-03 21:45:20.573  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1006, 1, 772939429751820288]
2022-09-03 21:45:20.624  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.624  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7e76a66f), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7e76a66f, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1007, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939429965729793])])
2022-09-03 21:45:20.624  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1007, 1, 772939429965729793]
2022-09-03 21:45:20.673  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.673  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@23f8036d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@23f8036d, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1008, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939430171250688])])
2022-09-03 21:45:20.673  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1008, 1, 772939430171250688]
2022-09-03 21:45:20.720  INFO 31084 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-03 21:45:20.720  INFO 31084 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31433df9, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b058bfd), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2b058bfd, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1009, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[772939430368382977])])
2022-09-03 21:45:20.720  INFO 31084 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1009, 1, 772939430368382977]
2022-09-03 21:45:20.773  INFO 31084 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-09-03 21:45:20.789  INFO 31084 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
Disconnected from the target VM, address: '127.0.0.1:50297', transport: 'socket'

Process finished with exit code 0

course_1如下图(示例):
在这里插入图片描述course_2如下图(示例):
在这里插入图片描述分别插入了五条记录,实现了分表插入

分库分表插入

清理一下表course_1,course_2的数据

把application02.properties的配置复制粘贴到application.properties里面
如下图(示例):
在这里插入图片描述这个策略是往m1的course_1和m2的course_2表里面插入数据
配置如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#分表策略:inline分片策略(不支持范围查询比如between and)
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#分库策略
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

去ApacheShardingsphereDemoApplicationTests运行addCourse方法
如下图(示例):
在这里插入图片描述控制台打印的日志:
如下(示例):



  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.1.RELEASE)

2022-09-04 10:25:23.829  INFO 28784 --- [           main] ApacheShardingsphereDemoApplicationTests : Starting ApacheShardingsphereDemoApplicationTests on DESKTOP-42NQPIS with PID 28784 (started by Machenike in W:\Personal\java_wxid\demo\apache-shardingsphere-demo)
2022-09-04 10:25:23.829  INFO 28784 --- [           main] ApacheShardingsphereDemoApplicationTests : No active profile set, falling back to default profiles: default
2022-09-04 10:25:24.608  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'stringToNoneShardingStrategyConfigurationConverter' of type [org.apache.shardingsphere.spring.boot.converter.StringToNoneShardingStrategyConfigurationConverter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:24.624  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.sharding-org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:24.640  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.masterslave-org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:24.640  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.encrypt-org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:24.640  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.shadow-org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:24.640  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere-org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:24.925  INFO 28784 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$a8bf30d8] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:25:25.285  INFO 28784 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2022-09-04 10:25:26.580  INFO 28784 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} inited
2022-09-04 10:25:26.981  INFO 28784 --- [           main] o.a.s.core.log.ConfigurationLogger       : ShardingRuleConfiguration:
tables:
  course:
    actualDataNodes: m$->{1..2}.course_$->{1..2}
    databaseStrategy:
      inline:
        algorithmExpression: m$->{cid%2+1}
        shardingColumn: cid
    keyGenerator:
      column: cid
      props:
        worker.id: '1'
      type: SNOWFLAKE
    logicTable: course
    tableStrategy:
      inline:
        algorithmExpression: course_$->{cid%2+1}
        shardingColumn: cid

2022-09-04 10:25:26.997  INFO 28784 --- [           main] o.a.s.core.log.ConfigurationLogger       : Properties:
sql.show: 'true'

2022-09-04 10:25:27.075  INFO 28784 --- [           main] ShardingSphere-metadata                  : Loading 1 logic tables' meta data.
2022-09-04 10:25:27.275  INFO 28784 --- [           main] ShardingSphere-metadata                  : Meta data load finished, cost 278 milliseconds.
 _ _   |_  _ _|_. ___ _ |    _ 
| | |\/|_)(_| | |_\  |_)||_|_\ 
     /               |         
                        3.3.2 
2022-09-04 10:25:28.868  WARN 28784 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Course".
2022-09-04 10:25:29.006  WARN 28784 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Dict".
2022-09-04 10:25:29.037  WARN 28784 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.User".
2022-09-04 10:25:29.084  INFO 28784 --- [           main] ApacheShardingsphereDemoApplicationTests : Started ApacheShardingsphereDemoApplicationTests in 5.594 seconds (JVM running for 7.034)
2022-09-04 10:25:29.924  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:29.924  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@63f3366a), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@63f3366a, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1000, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130728878641152])])
2022-09-04 10:25:29.924  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1000, 1, 773130728878641152]
2022-09-04 10:25:30.061  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.061  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@76cf91c9), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@76cf91c9, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1001, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130729809776641])])
2022-09-04 10:25:30.061  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1001, 1, 773130729809776641]
2022-09-04 10:25:30.153  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.153  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2ce24a1a), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2ce24a1a, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1002, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130730195652608])])
2022-09-04 10:25:30.153  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1002, 1, 773130730195652608]
2022-09-04 10:25:30.224  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.224  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1e9d7366), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1e9d7366, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1003, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130730493448193])])
2022-09-04 10:25:30.224  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1003, 1, 773130730493448193]
2022-09-04 10:25:30.285  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.285  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1e7d3d87), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1e7d3d87, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1004, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130730749300736])])
2022-09-04 10:25:30.285  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1004, 1, 773130730749300736]
2022-09-04 10:25:30.367  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.367  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4805069b), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4805069b, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1005, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130731093233665])])
2022-09-04 10:25:30.367  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1005, 1, 773130731093233665]
2022-09-04 10:25:30.418  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.418  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@85cd413), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@85cd413, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1006, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130731307143168])])
2022-09-04 10:25:30.418  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1006, 1, 773130731307143168]
2022-09-04 10:25:30.480  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.480  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c62d2ad), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1c62d2ad, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1007, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130731567190017])])
2022-09-04 10:25:30.480  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1007, 1, 773130731567190017]
2022-09-04 10:25:30.531  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.531  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@70840a5a), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@70840a5a, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1008, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130731781099520])])
2022-09-04 10:25:30.531  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1008, 1, 773130731781099520]
2022-09-04 10:25:30.596  INFO 28784 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cname,
user_id,
cstatus )  VALUES  ( ?,
?,
? )
2022-09-04 10:25:30.596  INFO 28784 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@31183ee2, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6e9f8160), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6e9f8160, columnNames=[cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[shardingsphere, 1009, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=true, generatedValues=[773130732053729281])])
2022-09-04 10:25:30.596  INFO 28784 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: INSERT INTO course_2  ( cname,
user_id,
cstatus , cid)  VALUES  (?, ?, ?, ?) ::: [shardingsphere, 1009, 1, 773130732053729281]
2022-09-04 10:25:30.667  INFO 28784 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-09-04 10:25:30.680  INFO 28784 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
2022-09-04 10:25:30.680  INFO 28784 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closing ...
2022-09-04 10:25:30.680  INFO 28784 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closed
Disconnected from the target VM, address: '127.0.0.1:55263', transport: 'socket'

Process finished with exit code 0

m1的course_1表
如下图(示例):
在这里插入图片描述m1的course_2表
如下图(示例):
在这里插入图片描述
m2的course_1表
如下图(示例):
在这里插入图片描述
m2的course_2表
如下图(示例):
在这里插入图片描述

分库分表查询

前面有了分库分表插入,现在我们对插入的数据进行查询

inline分片策略

inline分片策略可以进行简单的查询,比如根据id查询,降序等等,不支持范围查询,比如between and等等。
如下图(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#分表策略:inline分片策略(不支持范围查询比如between and)
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#分库策略
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

配置还是和分库分表的一样

去ApacheShardingsphereDemoApplicationTests的queryCourse方法运行
如下图1(示例):
在这里插入图片描述
配置如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
#分表策略:inline分片策略(不支持范围查询比如between and)
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
#分库策略
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.1.RELEASE)

2022-09-04 10:38:39.494  INFO 3988 --- [           main] ApacheShardingsphereDemoApplicationTests : Starting ApacheShardingsphereDemoApplicationTests on DESKTOP-42NQPIS with PID 3988 (started by Machenike in W:\Personal\java_wxid\demo\apache-shardingsphere-demo)
2022-09-04 10:38:39.503  INFO 3988 --- [           main] ApacheShardingsphereDemoApplicationTests : No active profile set, falling back to default profiles: default
2022-09-04 10:38:40.290  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'stringToNoneShardingStrategyConfigurationConverter' of type [org.apache.shardingsphere.spring.boot.converter.StringToNoneShardingStrategyConfigurationConverter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.290  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.sharding-org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.305  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.masterslave-org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.305  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.encrypt-org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.305  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.shadow-org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.321  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere-org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.590  INFO 3988 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$a8bf30d8] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2022-09-04 10:38:40.944  INFO 3988 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2022-09-04 10:38:42.178  INFO 3988 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} inited
2022-09-04 10:38:42.586  INFO 3988 --- [           main] o.a.s.core.log.ConfigurationLogger       : ShardingRuleConfiguration:
tables:
  course:
    actualDataNodes: m$->{1..2}.course_$->{1..2}
    databaseStrategy:
      inline:
        algorithmExpression: m$->{cid%2+1}
        shardingColumn: cid
    keyGenerator:
      column: cid
      props:
        worker.id: '1'
      type: SNOWFLAKE
    logicTable: course
    tableStrategy:
      inline:
        algorithmExpression: course_$->{cid%2+1}
        shardingColumn: cid

2022-09-04 10:38:42.586  INFO 3988 --- [           main] o.a.s.core.log.ConfigurationLogger       : Properties:
sql.show: 'true'

2022-09-04 10:38:42.648  INFO 3988 --- [           main] ShardingSphere-metadata                  : Loading 1 logic tables' meta data.
2022-09-04 10:38:42.833  INFO 3988 --- [           main] ShardingSphere-metadata                  : Meta data load finished, cost 247 milliseconds.
 _ _   |_  _ _|_. ___ _ |    _ 
| | |\/|_)(_| | |_\  |_)||_|_\ 
     /               |         
                        3.3.2 
2022-09-04 10:38:44.185  WARN 3988 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Course".
2022-09-04 10:38:44.317  WARN 3988 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Dict".
2022-09-04 10:38:44.348  WARN 3988 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.User".
2022-09-04 10:38:44.386  INFO 3988 --- [           main] ApacheShardingsphereDemoApplicationTests : Started ApacheShardingsphereDemoApplicationTests in 5.263 seconds (JVM running for 6.748)
2022-09-04 10:38:45.118  INFO 3988 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  cid,cname,user_id,cstatus  FROM course 
 
  
 
  ORDER BY cid DESC
2022-09-04 10:38:45.118  INFO 3988 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@2047adea, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5af40e45), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5af40e45, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=32, distinctRow=false, projections=[ColumnProjection(owner=null, name=cid, alias=Optional.empty), ColumnProjection(owner=null, name=cname, alias=Optional.empty), ColumnProjection(owner=null, name=user_id, alias=Optional.empty), ColumnProjection(owner=null, name=cstatus, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@1a06602f, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@46591e98, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@5b6cc344, containsSubquery=false)
2022-09-04 10:38:45.118  INFO 3988 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT  cid,cname,user_id,cstatus  FROM course_1 
 
  
 
  ORDER BY cid DESC
2022-09-04 10:38:45.118  INFO 3988 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: SELECT  cid,cname,user_id,cstatus  FROM course_2 
 
  
 
  ORDER BY cid DESC
2022-09-04 10:38:45.118  INFO 3988 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: SELECT  cid,cname,user_id,cstatus  FROM course_1 
 
  
 
  ORDER BY cid DESC
2022-09-04 10:38:45.118  INFO 3988 --- [           main] ShardingSphere-SQL                       : Actual SQL: m2 ::: SELECT  cid,cname,user_id,cstatus  FROM course_2 

  ORDER BY cid DESC
Course{cid=773130732053729281, cname='shardingsphere', userId=1009, cstatus='1'}
Course{cid=773130731781099520, cname='shardingsphere', userId=1008, cstatus='1'}
Course{cid=773130731567190017, cname='shardingsphere', userId=1007, cstatus='1'}
Course{cid=773130731307143168, cname='shardingsphere', userId=1006, cstatus='1'}
Course{cid=773130731093233665, cname='shardingsphere', userId=1005, cstatus='1'}
Course{cid=773130730749300736, cname='shardingsphere', userId=1004, cstatus='1'}
Course{cid=773130730493448193, cname='shardingsphere', userId=1003, cstatus='1'}
Course{cid=773130730195652608, cname='shardingsphere', userId=1002, cstatus='1'}
Course{cid=773130729809776641, cname='shardingsphere', userId=1001, cstatus='1'}
Course{cid=773130728878641152, cname='shardingsphere', userId=1000, cstatus='1'}
2022-09-04 10:38:45.281  INFO 3988 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-09-04 10:38:45.288  INFO 3988 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
2022-09-04 10:38:45.288  INFO 3988 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closing ...
2022-09-04 10:38:45.288  INFO 3988 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closed
Disconnected from the target VM, address: '127.0.0.1:55386', transport: 'socket'

Process finished with exit code 0

如下图2(示例):
在这里插入图片描述

standard标准分片策略

standard标准分片策略 (>,>=, <=,<,=,IN 和 BETWEEN AND支持 只是根据一个字段来查询,也就是一个分片键)
配置如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1

##分表策略:inline分片策略(不支持范围查询比如between and)
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
##分库策略
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

#standard标准分片策略
spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyPreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyRangeTableShardingAlgorithm

spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyPreciseDSShardingAlgorithm
spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyRangeDSShardingAlgorithm

运行ApacheShardingsphereDemoApplicationTests的queryOrderRange方法
如下图(示例):
在这里插入图片描述

complex复杂分片策略

多字段的分库分表 (>,>=, <=,<,=,IN 和 BETWEEN AND 支持 复合分片策略支持对多个分片健操作)
配置如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1

#complex复杂分片策略 多字段的分库分表 (>,>=, <=,<,=,IN 和 BETWEEN AND 支持 复合分片策略支持对多个分片健操作)
spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns= cid, user_id
spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyComplexTableShardingAlgorithm

spring.shardingsphere.sharding.tables.course.database-strategy.complex.sharding-columns=cid, user_id
spring.shardingsphere.sharding.tables.course.database-strategy.complex.algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyComplexDSShardingAlgorithm

运行ApacheShardingsphereDemoApplicationTests的queryCourseComplex方法
如下图(示例):
在这里插入图片描述前面的queryOrderRange使用standard标准分片策略查询了四张表,现在queryCourseComplex我只查询了一张表就可以,提升了效率。

hint强制路由策略

配置如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1
# 表策略
spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyPreciseDSShardingAlgorithm
spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyRangeDSShardingAlgorithm
#hint强制路由策略 指定分片值而非从SQL中提取分片值的方式进行分片的策略
spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyHintTableShardingAlgorithm

运行ApacheShardingsphereDemoApplicationTests的queryCourseComplex方法
如下图(示例):
在这里插入图片描述

广播表配置

广播表是在所有的数据源里面都存在的表
在所有的数据源中执行t_dict.sql文件里面的建表语句,创建三个表t_dict,t_dict_1,t_dict_2
配置如下(示例):

#配置多个数据源
spring.shardingsphere.datasource.names=m1,m2
#m1的mysql配置
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37
#m2的mysql配置
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://139.224.137.74:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=ca0a997ee4770063
#打印sql
spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true
#真实表分布,分库,分表 sharding里面的逻辑表course对应的是m1、m2库中course_1、course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id=1

##分表策略:inline分片策略(不支持范围查询比如between and)
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
##分库策略
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

#standard标准分片策略 (>,>=, <=,<,=,IN 和 BETWEEN AND支持 只是根据一个字段来查询,也就是一个分片键)
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyPreciseTableShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.table-strategy.standard.range-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyRangeTableShardingAlgorithm
#
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyPreciseDSShardingAlgorithm
#spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyRangeDSShardingAlgorithm

#complex复杂分片策略 多字段的分库分表 (>,>=, <=,<,=,IN 和 BETWEEN AND 支持 复合分片策略支持对多个分片健操作)
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns= cid, user_id
#spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyComplexTableShardingAlgorithm
#
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.sharding-columns=cid, user_id
#spring.shardingsphere.sharding.tables.course.database-strategy.complex.algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyComplexDSShardingAlgorithm

#hint强制路由策略 指定分片值而非从SQL中提取分片值的方式进行分片的策略
#spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.example.apacheshardingspheredemo.algorithem.MyHintTableShardingAlgorithm

#广播表配置
spring.shardingsphere.sharding.broadcast-tables=t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

运行ApacheShardingsphereDemoApplicationTests的addDict方法
如下图(示例):
在这里插入图片描述
m1的t_dict表如下图(示例):
在这里插入图片描述

m2的t_dict表如下图(示例):
在这里插入图片描述
二个库的t_dict表都插入了数据

绑定表配置

前面的部分都是对单表进行查询,那如果对多个表进行关联呢
比如:select u.user_id,u.username,d.uvalue ustatus from user u left join t_dict d on u.ustatus = d.ustatus
正常分库分表查询是不是这样的:

m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_1 d on u.ustatus = d.ustatus
m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_2 d on u.ustatus = d.ustatus
m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_1 d on u.ustatus = d.ustatus
m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_2 d on u.ustatus = d.ustatus

然后才把结果进行汇总,这样就会出现本来就十条数据,查询出来有二十条记录了,产生了笛卡尔积

User{userId=773233687314567169, username='user No 1', ustatus='null', uage=0}
User{userId=773233687872409601, username='user No 3', ustatus='null', uage=0}
User{userId=773233688304422913, username='user No 5', ustatus='null', uage=0}
User{userId=773233688866459649, username='user No 7', ustatus='null', uage=0}
User{userId=773233689327833089, username='user No 9', ustatus='null', uage=0}
User{userId=773233687314567169, username='user No 1', ustatus='正常', uage=0}
User{userId=773233687872409601, username='user No 3', ustatus='正常', uage=0}
User{userId=773233688304422913, username='user No 5', ustatus='正常', uage=0}
User{userId=773233688866459649, username='user No 7', ustatus='正常', uage=0}
User{userId=773233689327833089, username='user No 9', ustatus='正常', uage=0}
User{userId=773233687062908928, username='user No 0', ustatus='不正常', uage=0}
User{userId=773233687692054528, username='user No 2', ustatus='不正常', uage=0}
User{userId=773233688086319104, username='user No 4', ustatus='不正常', uage=0}
User{userId=773233688560275456, username='user No 6', ustatus='不正常', uage=0}
User{userId=773233689080369152, username='user No 8', ustatus='不正常', uage=0}
User{userId=773233687062908928, username='user No 0', ustatus='null', uage=0}
User{userId=773233687692054528, username='user No 2', ustatus='null', uage=0}
User{userId=773233688086319104, username='user No 4', ustatus='null', uage=0}
User{userId=773233688560275456, username='user No 6', ustatus='null', uage=0}
User{userId=773233689080369152, username='user No 8', ustatus='null', uage=0}

这个时候就需要绑定表了让t_user_1表和t_dict_1表进行绑定,t_user_2表和t_dict_2表进行绑定
主要的配置是spring.shardingsphere.sharding.binding-tables[0]=user,t_dict
配置如下(示例):

spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://110.42.239.246:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=591e242ca29b9c37


spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m1.t_dict_$->{1..2}

spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}

spring.shardingsphere.sharding.tables.user.actual-data-nodes=m1.t_user_$->{1..2}
spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=1
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}
#绑定表示例
spring.shardingsphere.sharding.binding-tables[0]=user,t_dict

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true

先后执行addDict方法和queryUserStatus方法
代码如下(示例):

    /**
     * 添加广播表
     */
    @Test
    public void addDict(){
        Dict d1 = new Dict();
        d1.setUstatus("1");
        d1.setUvalue("正常");
        dictMapper.insert(d1);

        Dict d2 = new Dict();
        d2.setUstatus("0");
        d2.setUvalue("不正常");
        dictMapper.insert(d2);

        for(int i = 0 ; i < 10 ; i ++){
            User user = new User();
            user.setUsername("user No "+i);
            user.setUstatus("" + (i%2));
            user.setUage(i * 10);
            userMapper.insert(user);
        }
    }

    /**
     * 绑定表查询
     */
    @Test
    public void queryUserStatus(){
        List<User> users = userMapper.queryUserStatus();
        users.forEach(user -> System.out.println(user));
    }

将本地的查询select u.user_id,u.username,d.uvalue ustatus from user u left join t_dict d on u.ustatus = d.ustatus进行分库分表拆分
日志打印如下(示例):

m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_1 u left join t_dict_1 d on u.ustatus = d.ustatus
m1 ::: select u.user_id,u.username,d.uvalue ustatus from t_user_2 u left join t_dict_2 d on u.ustatus = d.ustatus

查询出来的结果如下(示例):

User{userId=773233687062908928, username='user No 0', ustatus='不正常', uage=0}
User{userId=773233687692054528, username='user No 2', ustatus='不正常', uage=0}
User{userId=773233688086319104, username='user No 4', ustatus='不正常', uage=0}
User{userId=773233688560275456, username='user No 6', ustatus='不正常', uage=0}
User{userId=773233689080369152, username='user No 8', ustatus='不正常', uage=0}
User{userId=773233687314567169, username='user No 1', ustatus='正常', uage=0}
User{userId=773233687872409601, username='user No 3', ustatus='正常', uage=0}
User{userId=773233688304422913, username='user No 5', ustatus='正常', uage=0}
User{userId=773233688866459649, username='user No 7', ustatus='正常', uage=0}
User{userId=773233689327833089, username='user No 9', ustatus='正常', uage=0}
读写分离
主库配置修改

我这里用的是宝塔面板安装的mysql所以直接在首页点击mysql就可以直接进到配置页修改配置了
在这里插入图片描述主要需要修改的是以下几个属性:

  • server-id:服务节点的唯一标识。需要给集群中的每个服务分配一个单独的ID。

  • log-bin:打开Binlog日志记录,并指定文件名。

  • log-bin-index:Binlog日志文件

​ 重启MySQL服务, service mysqld restart

然后,我们需要给root用户分配一个replication slave的权限。

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%';
flush privileges;
#查看主节点同步状态:
show master status;

如下图(示例):
在这里插入图片描述

在实际生产环境中,通常不会直接使用root用户,而会创建一个拥有全部权限的用户来负责主从同步。

如下图(示例):
在这里插入图片描述

这个指令结果中的File和Position记录的是当前日志的binlog文件以及文件中的索引。

后面的Binlog_Do_DB和Binlog_Ignore_DB这两个字段是表示需要记录binlog文件的库以及不需要记录binlog文件的库。目前我们没有进行配置,就表示是针对全库记录日志。

开启binlog后,数据库中的所有操作都会被记录到datadir当中,以一组轮询文件的方式循环记录。而指令查到的File和Position就是当前日志的文件和位置。而在后面配置从服务时,就需要通过这个File和Position通知从服务从哪个地方开始记录binLog。
在这里插入图片描述

从库配置修改
#打开从服务二进制日志
log-bin = mysql-slave-bin
binlog_format = mixed
#主库和从库需要不一致
server-id = 2
#打开MySQL中继日志
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
#设置只读
read_only = 1

如下图(示例):
在这里插入图片描述主要需要关注的几个属性:

  • server-id:服务节点的唯一标识,主库和从库需要不一致

  • relay-log:打开从服务的relay-log日志。

  • log-bin:打开从服务的bin-log日志记录。

启动mysqls的服务,并设置他的主节点同步状态

#设置同步主节点:
CHANGE MASTER TO MASTER_HOST='139.224.137.74', MASTER_USER='root', MASTER_PASSWORD='ca0a997ee4770063', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-master-bin.000001', MASTER_LOG_POS= 120, MASTER_CONNECT_RETRY=30;
#开启slave
start slave;
#查看主从同步状态
show slave status;

执行sql如下(示例):
在这里插入图片描述
CHANGE MASTER指令中需要指定的MASTER_LOG_FILE和MASTER_LOG_POS必须与主服务中查到的保持一致。

并且后续如果要检查主从架构是否成功,也可以通过检查主服务与从服务之间的File和Position这两个属性是否一致来确定。
如下(示例):
在这里插入图片描述

主从集群测试

去主库创建syncdemo库,观察从库是否生成syncdemo库
主库如下图(示例):
在这里插入图片描述
从库如下(示例):
在这里插入图片描述
可以发现库同步了

然后在主库创建表
代码如下(示例):

-- 在三个库中创建
CREATE TABLE `t_dict`  (
  `dict_id` BIGINT(0) PRIMARY KEY NOT NULL,
  `ustatus` VARCHAR(100) NOT NULL,
  `uvalue` VARCHAR(100) NOT NULL
);

主库生成表如下(示例):
在这里插入图片描述
从库生成表如下(示例):
在这里插入图片描述
可以发现表同步了

主库的t_dict表插入一条数据如下(示例):
在这里插入图片描述
从库的t_dict表也生成一条数据如下(示例):
在这里插入图片描述
表数据也同步了

写操作存储到m0,读操作从s0读取

配置如下(示例):

#配置主从数据源,要基于MySQL主从架构。
spring.shardingsphere.datasource.names=m0,s0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://139.224.137.74:3306/masterdemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=ca0a997ee4770063

spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://106.14.132.94:3306/slavedemo?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=JHWLXeT56iJiBwDG
#读写分离规则, m0 主库,s0 从库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names[0]=s0
#基于读写分离的表分片
spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=ds0.t_dict

spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_dict.key-generator.props.worker.id=1

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true


依次执行addDictByMS方法和queryDictByMS方法

代码如下(示例):

    /**
     * 主库插入
     */
    @Test
    public void addDictByMS(){
        Dict d1 = new Dict();
        d1.setUstatus("1");
        d1.setUvalue("正常");
        dictMapper.insert(d1);

        Dict d2 = new Dict();
        d2.setUstatus("0");
        d2.setUvalue("不正常");
        dictMapper.insert(d2);
    }

    /**
     * 从库读取
     */
    @Test
    public void queryDictByMS(){
        List<Dict> dicts = dictMapper.selectList(null);
        dicts.forEach(dict -> System.out.println(dict));
    }

控制台打印如下(示例):

 _ _   |_  _ _|_. ___ _ |    _ 
| | |\/|_)(_| | |_\  |_)||_|_\ 
     /               |         
                        3.3.2 
2022-09-04 20:59:03.749  WARN 47380 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Course".
2022-09-04 20:59:03.898  WARN 47380 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Dict".
2022-09-04 20:59:03.950  WARN 47380 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.User".
2022-09-04 20:59:04.002  INFO 47380 --- [           main] ApacheShardingsphereDemoApplicationTests : Started ApacheShardingsphereDemoApplicationTests in 5.339 seconds (JVM running for 6.826)
2022-09-04 20:59:04.861  INFO 47380 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_dict  ( ustatus,
uvalue )  VALUES  ( ?,
? )
2022-09-04 20:59:04.861  INFO 47380 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@14d81f2c, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5dd3727c), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5dd3727c, columnNames=[ustatus, uvalue], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=51, stopIndex=51, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[1, 正常])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=dict_id, generated=true, generatedValues=[773290175286480896])])
2022-09-04 20:59:04.861  INFO 47380 --- [           main] ShardingSphere-SQL                       : Actual SQL: m0 ::: INSERT INTO t_dict  ( ustatus,
uvalue , dict_id)  VALUES  (?, ?, ?) ::: [1, 正常, 773290175286480896]
2022-09-04 20:59:05.000  INFO 47380 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO t_dict  ( ustatus,
uvalue )  VALUES  ( ?,
? )
2022-09-04 20:59:05.000  INFO 47380 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@14d81f2c, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@361f26b4), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@361f26b4, columnNames=[ustatus, uvalue], insertValueContexts=[InsertValueContext(parametersCount=2, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=51, stopIndex=51, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=54, stopIndex=54, parameterMarkerIndex=1), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=2))], parameters=[0, 不正常])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=dict_id, generated=true, generatedValues=[773290176016289793])])
2022-09-04 20:59:05.000  INFO 47380 --- [           main] ShardingSphere-SQL                       : Actual SQL: m0 ::: INSERT INTO t_dict  ( ustatus,
uvalue , dict_id)  VALUES  (?, ?, ?) ::: [0, 不正常, 773290176016289793]
2022-09-04 20:59:05.089  INFO 47380 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-09-04 20:59:05.097  INFO 47380 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
2022-09-04 20:59:05.097  INFO 47380 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closing ...
2022-09-04 20:59:05.097  INFO 47380 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closed
Disconnected from the target VM, address: '127.0.0.1:60227', transport: 'socket'

Process finished with exit code 0

可以发现写操作的二条sql都是对主库进行写入如下(示例):

m0 ::: INSERT INTO t_dict  ( ustatus,uvalue , dict_id)  VALUES  (?, ?, ?) ::: [1, 正常, 773290175286480896]
m0 ::: INSERT INTO t_dict  ( ustatus,uvalue , dict_id)  VALUES  (?, ?, ?) ::: [0, 不正常, 773290176016289793]

主库表有数据生成如下(示例):
在这里插入图片描述但是我们前面配置了主从同步,所以从库的表里面也有数据生成,但是在sql层面我们仅仅对主库进行写的操作,从库的表数据生成是mysql层面,这里需要区分一下。
从库数据生成如下(示例):
在这里插入图片描述
执行queryDictByMS方法查询
控制台打印如下(示例):

 _ _   |_  _ _|_. ___ _ |    _ 
| | |\/|_)(_| | |_\  |_)||_|_\ 
     /               |         
                        3.3.2 
2022-09-04 21:01:20.265  WARN 49292 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Course".
2022-09-04 21:01:20.450  WARN 49292 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.Dict".
2022-09-04 21:01:20.500  WARN 49292 --- [           main] c.b.m.core.metadata.TableInfoHelper      : Can not find table primary key in Class: "com.example.apacheshardingspheredemo.entity.User".
2022-09-04 21:01:20.536  INFO 49292 --- [           main] ApacheShardingsphereDemoApplicationTests : Started ApacheShardingsphereDemoApplicationTests in 5.389 seconds (JVM running for 6.911)
2022-09-04 21:01:21.334  INFO 49292 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  dict_id,ustatus,uvalue  FROM t_dict
2022-09-04 21:01:21.334  INFO 49292 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@53fbb2b4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7434ee13), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7434ee13, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=29, distinctRow=false, projections=[ColumnProjection(owner=null, name=dict_id, alias=Optional.empty), ColumnProjection(owner=null, name=ustatus, alias=Optional.empty), ColumnProjection(owner=null, name=uvalue, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@44a14de0, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@50fd739d, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@5e1d1524, containsSubquery=false)
2022-09-04 21:01:21.334  INFO 49292 --- [           main] ShardingSphere-SQL                       : Actual SQL: s0 ::: SELECT  dict_id,ustatus,uvalue  FROM t_dict
Dict{dictId=1, ustatus='1', uvalue='1'}
Dict{dictId=773290175286480896, ustatus='1', uvalue='正常'}
Dict{dictId=773290176016289793, ustatus='0', uvalue='不正常'}
2022-09-04 21:01:21.477  INFO 49292 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-09-04 21:01:21.479  INFO 49292 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
2022-09-04 21:01:21.479  INFO 49292 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closing ...
2022-09-04 21:01:21.479  INFO 49292 --- [extShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closed
Disconnected from the target VM, address: '127.0.0.1:60301', transport: 'socket'

Process finished with exit code 0

sql查询如下(示例):

s0 ::: SELECT  dict_id,ustatus,uvalue  FROM t_dictDict{dictId=1, ustatus='1', uvalue='1'}

可以发现读操作是在从库上面读取的

到现在读写分离才算正常工作了。

从0到1带大家搭建spring cloud alibaba 微服务大型应用框架(四) (mini-cloud) 集成shardingSphere 读写分离 #1 为什么要用读写分离 ## 如果一个业务的数据读远远大于写的需求,那么创建多个多个读从库还是比较常用的选择,主要为了分离数据库压力,如果并没有太多高压力读需求,则不需要读写分离 #2 读写分离一般性方案 ##2.1 外置层proxy方案 ### 外置层proxy方案主要是指外部搭建一个代理服务,全权代理了数据库本身的连接,类似于nginx对web应用的代理一样,我们应用本身连接数据库的连接直接连接这个代理,而不是连接真是数据库,代理会根据配置的规则自动帮助请求进行读写分离或者分库分表,比如mycat # 阅读详情

相关推荐

燧原科技云燧ESL64-O超节点:国产AI芯片与OEX光互联架构解析

分布式AI训练通过将计算任务拆分到多个节点并行执行来加速模型训练,其核心原理涉及数据并行和模型并行等策略。在技术价值层面,分布式训练能显著提升大规模深度学习任务的效率,但传统GPU集群常因网络通信瓶颈导致性能受限。OEX光互联架构作为解决通信瓶颈的关键创新,采用硅光技术实现高带宽、低延迟的节点内互联,优化了AI训练中的集合操作。这种硬件级优化特别适用于千亿参数大模型训练和科学计算等场景,能有效降低AI集群的总体拥有成本(TCO)。云燧ESL64-O超节点正是基于国产自研AI芯片与OEX架构的集成方案,为大规

weixin_29071809的博客 374

若依框架集成sharding-jdbc

本文基于4.X版本在若依框架上集成sharding-jdbc, 实现了精准查询与范围查询, 通过雪花id反推时间进行分配查询等操作

始末丶Yzx的博客 3067

pandas,构造MultiIndex的四种方法:from_tuples、from_arrays、from_product、from_frame

1、from_tuples:通过 “元组对” 构造MultiIndex """ 一对元组,为一个索引 """ tuples = [(1, 'red'), (1, 'blue'), (2, 'red'), (2, 'blue')] pd.MultiIndex.from_tuples(tuples, names=('number', 'color')) 2、from_arrays:通过 “列表元素的组合” 构造MultiIndex """ 列中表的元素,按位置 对应组合,形成索引 """ arrays

jieru_liu的博客 5390

springboot集成sharding-jdbc

3、水平分片:(1)数据库分片;yml文件会爆红,不影响项目启动。3、更新数据库内容自己测试。yml文件中采用的是。

m0_37999219的博客 1万+

微信小程序云开发订单微信支付与小票和标签打印的完整高效流程

本片文章全文12000字,细致讲述从申请账户到小程序注册到打印机的选型最后到实现微信小程序云开发订单支付更新与小票,标签打印完整的高效流程。客户A在我们的点餐平台上点了一杯咖啡,所以客户要在选好后付款,在付款完成后商家的小票打印机和标签打印机打印出本订单的相关信息。

weixin_52312427的博客 3651

卷积码和Turbo码

通信原理相关博文目录:目录我们常在移动通信中遇到的卷积码就是一种非分组码,卷积码和信号处理中的卷积运算有关系吗?是不是就是信号处理中的卷积运算,先看看编码器的编码原理再说:下面是一个比较实用的卷积码编码器:它有三个移位寄存器D0,D1,D2和三个模2加法器,以及一个旋转开关组成,编码前,先将各级移位寄存器清零:现在假设输入的信息码元是1101:当输入的第一个码元为1时,三个模2加法器计算的结果都为...

Reborn Lee 2万+

JeecgBoot集成Sharding-JDBC

1,环境描述 本次测试基于JeecgBoot 2.4.6,测试代码在Jeecg-boot-module-system中编写。 2,引入坐标 <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version

u013473447的专栏 2023

【飞控】MATLAB中UAV Toolbox Support Package for PX4 Autopilots和Pixhawk Pilot Support Package (PSP) 的关系?

区分UAV Toolbox Support Package for PX4 Autopilots和Pixhawk Pilot Support Package (PSP)

m0_58373406的博客 742

项目集成sharding-jdbc

目录项目集成sharding-jdbc1.业务分析 2.数据库构建3.分库分表策略 项目配置默认数据源一:导入sharding-jdbc依赖 二:在application文件中编写配置三:注释掉主配置文件中配置的数据源注意:这里添加了spring.main.allow-bean-definition-overriding: true四:测试项目配置广播表(公共表)一:application配置二:测试项目使用standard标准模式配置分库算法一.分库算法类 二.application中配置逻辑表对应的数据

luosuss的博客 1552

(OpenCV)图像目标尺寸检测

  本文翻译自pyimagesearch技术博客上的一篇文章,《Measuring size of objects in an image with OpenCV》,原文作者:Adrian Rosebrock 。 https://www.pyimagesearch.com/2016/03/28/measuring-size-of-objects-in-an-image-with-opencv...

明柳梦少的博客 7万+

在QTableWidget中添加支持多选的QComboBox并显示

定义自定义表格项,主要用于显示复选框自定义的多选ComboBox类自定义委托类,用于表格中的多选ComboBox代码展示如下。

YUyang0304的博客 2787

JAVA开发(ShardingSphere提供分库分表功能)

在高性能系统的要求下,复杂的系统当单表数据量增加到几千万甚至上一条记录时,查询延迟无疑是影响高性能系统的瓶颈。业内提供的解决的方案是分库分表,意思是将数据量大的库进行分开,数据量大的表进行分割。通常有横向分割和纵向分割两种模式,其中横向分割较容易。但是当进行数据库分库分表,就产生了另外的问题,就是分布式问题。此时就需要封装一个强大的工具进行屏蔽,使我们在写代码的时候就像只对一个数据库或者一个表进行操作一样。这时ShardingShere就应运而生了。

JAVA领域优质创作者,基于分片网络查询方法专利发明者。 1309

Apifox接口测试+自动化测试

Apifox 接口测试+自动化测试快速入门!

程序员小杨哥的博客 1万+

Java基于ShardingSphere实现mysql分库分表

lPAD()里有三个参数,第一是上边定义的常量,第二是拼接长度,第三是需要补的字段,比如lPAD(y,2,'0'),当y小于10时,其长度是小于2的,所以会在左边补个0,比如01,02等。但是问题来了,既然需要分表,表肯定是多张,所以要用某个特殊关键字来做区分,由于此表为操作记录,我可用日期作区分,在构建前要评估数据量的大小,如果有必要,可按天来区分,我这里是按月来区分的。但是又有个问题,一张一张的去建也没毛病,就是有点麻烦,假如按天去用的话,那可真是太痛苦了,所以可以用存储过程来建一个函数。

weixin_61240867的博客 991

ssm框架搭建

这个是一个在idea上成功跑起来的ssm框架项目,保存在这里方便以后使用直接下载

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

Springboot集成Shardingsphere分表分库-两步快速集成-极简方便亲测可用

定义实际的数据节点范围。datetime-pattern: yyyy-MM-dd HH:mm:ss # 定义时间格式,表示日期和时间的格式为年-月-日 时:分:秒。datetime-lower: 2025-01-01 00:00:00 # 设置分片的开始时间,即时间范围的下限。datetime-upper: 2035-12-31 23:59:59 # 设置分片的结束时间,即时间范围的上限。# 指定分片算法的名称,这里使用之前定义的 `tba-monthly`,表示按月进行分片。

weixin_44556848的博客 2051

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

Java开发ShardingSphere:轻松实现分库分表功能

作为一名Java开发者,你是否在面对大数据量和高并发的场景时感到束手无策?现在,有了ShardingSphere这个强大的工具,你可以轻松实现分库分表功能,让你的应用性能飞速提升!让我来分享一下我的经验吧。

Q2024107的博客 866

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

项目集成ShardingSphere遇到的问题

真实分库分表的细节问题、真实开发中分库分表的问题及解决方案、分库分表后事务失效怎么解决,分库分表后@Transactional失效、,mq多节点下无法保证消息的有序性、分片后的分页、shardingSphere的分页、洗数据时洗不动怎么办、真实项目中,如何使用shardingSphere;shardingSphere的面试题

weixin_42351206的博客 2464

Java基于ShardingSphere实现分库分表的实例

Java基于ShardingSphere实现分库分表的实例

星火染星野的博客 1232

JAVA技术体系之分布式篇(八)——ShardingSphere-jdbc分库分表

0、写在前面   当笔者试图收集ShardingSphere-jdbc相关信息时,发现ShardingSphere官网做了非常完善的内容总结,并且通俗易懂,符合国人阅读习惯。基于此,本文大多数摘录自ShardingSphere官网,提炼出笔者认为较为重要的内容,有兴趣的同学可以前往官网获取更为完善的信息。   https://shardingsphere.apache.org/document/current/cn/overview/ 1、概述 1.1 背景   传统的将数据集中存储至单一数据节点的解决方案

qsmiley10的博客 2654

IntelliJ IDEA 集成 ShardingSphere-JDBC 访问分库分表

对于使用依赖管理的项目,JAR 数量的多少对于开发者来说相对透明,引入依赖改改 POM 文件即可。但如果需要集成 ShardingSphere 的项目没有 Maven 或 Gradle 依赖管理(甚至没有源码),引入 ShardingSphere 可能是一项麻烦的事情。Apache ShardingSphere 是一个模块拆分非常细的项目。

weixin_41978174的博客 486

SpringBoot使用Sharding-JDBC分库分表

本文介绍SpringBoot使用当当Sharding-JDBC进行分库分表。 1.有关Sharding-JDBC 有关Sharding-JDBC介绍这里就不在多说,之前Sharding-JDBC是当当网自研的关系型数据库的水平扩展框架,现在已经捐献给Apache,具体可以查看Github,地址是:shardingsphere.apache.org/document/cu… shardingsp...

weixin_33938733的博客 2149

11. sharding-jdbc集成--基于ssm

阿飞Javaer,转载请注明原创出处,谢谢! 本篇文章讲解如何在ssm(spring、springmvc、mybatis)架构的程序上集成sharding-jdbc(版本为1.5.4.1)进行分库分表; 假设分库分表行为如下: - 将auth_user表分到4个库(user_0~user_3)中; - 其他表不进行分库分表,保留在default_db库中; 1. POM配置 以...

阿飞Javaer的专栏 1643

SpringBoot集成ShardingSphere(手工配置)

在中大型项目开发过程中,如何存储大量的数据是我们不可回避的难题,对这个问题的处理直接关系到系统的稳定性和可用性,我曾经负责过一个公司重点的老项目(交接过来的,哈哈~~),由于开发的时候没能正确预估数据的增长量,两三年之后有若干张表的单表数据量已经达到了五千万以上(未分库分表),我们都知道出于性能考虑mysql的单表数据量不要超过一千万,而且这个系统的数据库还有其他问题,比如和其他系统数据共库等等,...

hyc2zbj的专栏 1万+

Apache ShardingSphere介绍和基本使用及分库分表(水平拆分、垂直拆分)理解和使用

分库从单个数据库拆分成多个数据库的过程,将数据散落在多个数据库中。分表从单张表拆分成多张表的过程,将数据散落在多张表内。

薛之谦 2812

springBoot集成ShardingSphere实现单库分表

我们在开发项目的时候,会遇到有一个或者几个表的数据量特别大的情况。比如商城中的订单表。这个是随着时间的推进越来越多。所以需要进行分表来处理。让单表的数量不要太多。所以这个时候需要进行分表操作。我们使用比较流行的shardingsphere来实现。 ...

wszhm123的博客 2477
上一篇: 【java_wxid项目】【第七章】【Spring Cloud Alibaba Seata集成】
下一篇: 【java_wxid项目】【第九章】【Apache Mybatis集成】
Java程序员廖志伟
Java程序员廖志伟 优质创作者: Java技术领域 优质创作者: Java技术领域 领域专家: 后端开发技术领域 领域专家: 后端开发技术领域
博客等级 码龄8年 2万+粉丝 1902原创
评论 16
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Java程序员廖志伟

赏我包辣条呗

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值