SpringCloud下结合shardingSphere进行分库分表(inline表达式简单实现)

本例主要参看官方的配置进行作业，实现简单的mod算法分库分表，对于分库分表的理解比较合适。

生产环境上建议自定义分库分表用算法（下一篇随笔介绍）。

 

1）关键部分的pom依赖：

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

 

2）关键yml配置:

      2.1）数据源：6个数据源，两个集群，分别为1主2从；

                主1：master0；从1：master0salve0；从2：master0slave1

                主2：master1；从1：master1slave0；从2：master1slave1

     2.2）作为demo演示，会员表进行分库和分表；每个库里有两张表：mc_member0和mc_member1;

     2.3）分库规则：gender=1（男）时   gender%2 -> master1库； gender=2（女）时   gender%2 -> master0库；

     2.4）分表规则：id偶数时，id%2->mc_member0表；id为奇数时，id%2->mc_member1表；

     2.5）注意事项1：对逻辑表配置分片策略时，数据库分片策略设置逻辑数据源，不使用物理库名；表分片策略内，使用物理表名；

     2.6）配置读写分离：master-slave-rules下的rule逻辑名可以自定义如 ms1和ds1等。     

sharding:
  jdbc:
    datasource:
      names: master0,master0salve0,master0slave1,master1,master1slave0,master1slave1
      master0:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/mcspcsharding0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
      master0salve0:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/mcspcsharding0s0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
      master0slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/mcspcsharding0s1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
      master1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/mcspcsharding1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
      master1slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/mcspcsharding1s0?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
      master1slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        url: jdbc:mysql://localhost:3306/mcspcsharding1s1?useUnicode=true&character_set_server=utf8mb4&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: root
    config:
      sharding:
        tables:
          mc_member:
            actual-nodes: mcspcsharding$->{0..1}.mc_member$->{0..1}
            database-strategy:
              inline:
                sharding-column: gender
                algorithm-expression: master$->{gender % 2}
            table-strategy:
              inline:
                sharding-column: id
                algorithm-expression: mc_member$->{id % 2}
        binding-tables: mc_member  # 多个时逗号隔开
        broadcast-tables: mc_master
        master-slave-rules:
          ms0:
            master-data-source-name: master0
            slave-data-source-names: master0salve0,master0slave1
          ms1:
            master-data-source-name: master1
            slave-data-source-names: master1slave0,master1slave1
      props:
        sql:
          show: true

 

3）启动类。因为数据源的重复注入问题，需要进行如下配置：

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})

package com.chong.mcspcshardingdbtable;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
@EnableDiscoveryClient
@EnableTransactionManagement
@ComponentScan(basePackages = {"com.chong.common","com.chong.mcspcshardingdbtable"})
public class McSpcShardingDbTableApplication {

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

其他的sever和repository等不在贴码，自己试验下应该很容易实现。