SpringBoot+Mycat+MySQL实现分表

SpringBoot项目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 http://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.1.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.yunji.study</groupId>
    <artifactId>study-boot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>study-boot</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.github.ben-manes.caffeine/guava -->
        <dependency>
            <groupId>com.github.ben-manes.caffeine</groupId>
            <artifactId>guava</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-elasticsearch</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.elasticsearch.client/transport -->
        <!--<dependency>-->
            <!--<groupId>org.elasticsearch.client</groupId>-->
            <!--<artifactId>transport</artifactId>-->
            <!--<version>7.0.0</version>-->
        <!--</dependency>-->
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-elasticsearch</artifactId>
            <version>3.1.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

1.配置切分规则

将如下配置复制粘贴覆盖mycat/conf/schema.xml的内容

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />  
    </schema>
    
    <!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->  
    <dataNode name="dn01" dataHost="dh01" database="db01" />  
    <dataNode name="dn02" dataHost="dh01" database="db02" />   
    
    <!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->  
    <dataHost name="dh01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">  
            <heartbeat>select user()</heartbeat>  
            <writeHost host="server1" url="127.0.0.1:3306" user="root" password=""/>  
    </dataHost> 
</mycat:schema>

<schema>:表示的是在mycat中的逻辑库配置，逻辑库名称为:TESTDB

<table>:表示在mycat中的逻辑表配置，逻辑表名称为:user,映射到两个数据库节点dataNode中,切分规则为:rule1(在rule.xml配置)

<dataNode>:表示数据库节点,这个节点不一定是单节点，可以配置成读写分离.

<dataHost>:真实的数据库的地址配置

<heartbeat>:用户心跳检测

<writeHost>:写库的配置

将如下配置复制粘贴覆盖mycat/conf/rule.xml的内容。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function>
</mycat:rule>

这里定义的是切分规则，是按照id列进行切分，切分规则是采取取模的方式,
<property name="count">2</property>:这里配置了我们有拆分了多个库(表)，需要和前面配置
<table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />
中的dataNode个数一致，否则会出错.

2.启动mycat

3.连接mycat，用户名在mycat的配置文件server.xml中

4.在数据库中创建两个数据库db01,db02.

每个库中执行如下建表语句:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

同时在mycat数据库TESTDB中也要执行建表语句去创建表user

 

springboot项目的

application.properties配置:

注意：端口是8066

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.url=jdbc:mysql://localhost:8066/TESTDB?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=123456

UserMapper.java代码如下：

@Mapper
public interface UserMapper {
    @Insert("insert into user(id,name) value (#{id},#{name})")
    int insert(User user);
    @Select("select * from user")
    List<User> selectAll();
}

UserController.java代码如下:

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserMapper userMapper;
    @RequestMapping("/save")
    public String save(User user){
        userMapper.insert(user);
        return "保存成功";
    }
    @RequestMapping("/list")
    public List<User> list(){
        return userMapper.selectAll();
    }
}

测试:
在地址栏输入:

http://localhost:8080/user/save?id=1&name=tom

http://localhost:8080/user/save?id=2&name=Lucy

http://localhost:8080/user/save?id=3&name=Lily

http://localhost:8080/user/save?id=4&name=Lina

查看数据库发现：

id为1,3的数据插入到数据库db02中的user表。
id为2,4的数据插入到数据库db01中的user表。

在地址栏输入:
http://localhost:8080/user/list
是可以看到刚刚插入的四条记录.

 

参考：https://www.jianshu.com/p/f81422b1c915