本文共 7881 字，大约阅读时间需要 26 分钟。

1.1 按照水平分库的方式创建数据库和数据表

1.1.1 创建分库1

CREATE SCHEMA `edudb1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

1.1.2 创建分库2

CREATE SCHEMA `edudb2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

1.1.3 创建分库1中的表1

CREATE TABLE `edudb1`.`course1` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.1.4 创建分库1中的表2

CREATE TABLE `edudb1`.`course2` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.1.5 创建分库2中的表1

CREATE TABLE `edudb2`.`course1` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.1.6 创建分库2中的表2

CREATE TABLE `edudb2`.`course2` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.2 创建SpringBoot工程

1.2.1 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>    <parent>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-starter-parent</artifactId>        <version>2.3.1.RELEASE</version>        <relativePath/> <!-- lookup parent from repository -->    </parent>    <groupId>com.ccb</groupId>    <artifactId>shardingsphere</artifactId>    <version>0.0.1-SNAPSHOT</version>    <name>shardingsphere</name>    <description>Sharding sphere project for Spring Boot</description>    <properties>        <java.version>1.8</java.version>    </properties>    <dependencies>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter</artifactId>        </dependency>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-test</artifactId>        </dependency>        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid-spring-boot-starter</artifactId>            <version>1.1.20</version>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.1.6</version>        </dependency>        <dependency>            <groupId>org.apache.shardingsphere</groupId>            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>            <version>4.0.0-RC1</version>        </dependency>        <dependency>            <groupId>com.baomidou</groupId>            <artifactId>mybatis-plus-boot-starter</artifactId>            <version>3.0.5</version>        </dependency>    </dependencies>    <build>        <plugins>            <plugin>                <groupId>org.springframework.boot</groupId>                <artifactId>spring-boot-maven-plugin</artifactId>            </plugin>        </plugins>    </build></project>

1.2.2 创建po实体类

package com.ccb.sharding.po;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;    }}

1.2.3 创建mapper 接口

package com.ccb.sharding.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.ccb.sharding.po.Course;import org.springframework.stereotype.Repository;@Repositorypublic interface CourseMapper extends BaseMapper<Course> {   }

1.2.4 添加到MapperScan扫描

package com.ccb.sharding;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication@MapperScan("com.ccb.sharding.mapper")public class ShardingApplication {       public static void main(String[] args) {           SpringApplication.run(ShardingApplication.class, args);    }}

1.3 配置水平分库策略

# sharding-JDBC分片策略# 配置数据源，给数据源命名# 水平分库，配置两个数据源spring.shardingsphere.datasource.names=ds1,ds2# 配置ds1数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/edudb1?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=chengwen# 配置ds2数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/edudb2?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds2.username=rootspring.shardingsphere.datasource.ds2.password=chengwen# 一个实体类对应两张表，覆盖spring.main.allow-bean-definition-overriding=true# 指定数据库分布情况，数据库里面表分布情况# ds1 ds2 course1 course2spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{1..2}.course$->{1..2}# 指定course 表里面主键cid 生成策略 SNOWFLAKE 雪花算法spring.shardingsphere.sharding.tables.course.key-generator.column=cidspring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE# 指定表分片策略 约定cid的值偶数添加到course1表，奇数添加到course2表spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cidspring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course$->{cid % 2 + 1}# 指定数据库分片策略 约定user_id 的值是偶数添加到ds1库，奇数添加到ds2库#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column==user_id#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}# 打印sql输出日志spring.shardingsphere.props.sql.show=true

1.4 测试水平分库

package com.ccb.sharding;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.ccb.sharding.mapper.CourseMapper;import com.ccb.sharding.po.Course;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;@SpringBootTestclass ShardingApplicationTests {       @Autowired    CourseMapper courseMapper;    // =============== 测试水平分库 ===============    @Test    public void addCourseDB() {           Course course = new Course();        course.setCname("Mysql");        course.setCstatus("Normal");        course.setUserId(100L);        courseMapper.insert(course);    }    @Test    public void addCourseDBS() {           for (int i = 1; i <= 10; i ++){               Course course = new Course();            course.setCname("Mysql" + i);            course.setCstatus("Normal" + i);            course.setUserId(100L + i);            courseMapper.insert(course);        }    }    @Test    public void getCourseDB() {           QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.eq("cid",482547343917318145L);        queryWrapper.eq("user_id",100L);        Course course = courseMapper.selectOne(queryWrapper);        System.out.println(course);    }}

1.5 Test Result

1.5.1 分库1表1结果

1.5.2 分库2表2结果

1.5.3 根据cid和user_id从分库分表中查询结果