CREATE TABLE `t_order` (
 `order_id` BIGINT ( 20 ) NOT NULL COMMENT "訂單表分布式主健ID",
 `order_number` VARCHAR ( 255 ) NOT NULL COMMENT "訂單號(hào)",
 `customer_id` BIGINT ( 20 ) NOT NULL COMMENT "用戶ID",
 `order_date` date NOT NULL COMMENT "下單時(shí)間",
 `total_amount` DECIMAL ( 10, 2 ) NOT NULL COMMENT "訂單金額",
    PRIMARY KEY ( `order_id` ) USING BTREE 
);

spring:
  shardingsphere:
    datasource:
      # 數(shù)據(jù)源名稱，多數(shù)據(jù)源以逗號(hào)分隔 ,放在第一個(gè)的數(shù)據(jù)源為未配置分片規(guī)則表的默認(rèn)數(shù)據(jù)源
      names: db0 , db1
      # 名稱與上邊 names 保持一致
      db0:
      ....

      db1:
      ....
    # 具體規(guī)則配置
    rules:
      sharding:
        # 分片算法定義
        sharding-algorithms:
          # 自定義分片算法名稱
          t_order_database_algorithms:
            # 分片算法類型
            type: INLINE
            # 自定義參數(shù)
            props:
              algorithm-expression: db$->{order_id % 2}
          t_order_table_algorithms:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id % 1000}
          t_order_mod:
            type: MOD
            props:
              # 指定分片數(shù)量
              sharding-count: 1000
        # 分布式序列算法配置
        key-generators:
          t_order_snowflake:
            type: SNOWFLAKE
            # 分布式序列算法屬性配置
            props:
              worker-id: 1
        tables:
          # 邏輯表名稱
          t_order:
            # 數(shù)據(jù)節(jié)點(diǎn)：數(shù)據(jù)庫.分片表
            actual-data-nodes: db$->{0..1}.t_order_$->{1..1000}
            # 分庫策略
            database-strategy:
              standard:
                # 分片列名稱
                sharding-column: order_id
                # 分片算法名稱
                sharding-algorithm-name: t_order_database_algorithms
            # 分表策略
            table-strategy:
              standard:
                # 分片列名稱
                sharding-column: order_id
                # 分片算法名稱
                sharding-algorithm-name: t_order_table_algorithms
            # 主鍵生成策略
            keyGenerateStrategy:
              column: order_id
              keyGeneratorName: t_order_snowflake
    # 屬性配置
    props:
      # 展示修改以后的sql語句
      sql-show: true

/**
 * 公眾號(hào)：程序員小富
 */
@Configuration
public class ShardingConfiguration {

    /**
     * 配置分片數(shù)據(jù)源
     * 公眾號(hào)：程序員小富
     */
    @Bean
    public DataSource getShardingDataSource() throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("db0", dataSource0());
        dataSourceMap.put("db1", dataSource1());

        // 分片rules規(guī)則配置
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        // 分片算法
        shardingRuleConfig.setShardingAlgorithms(getShardingAlgorithms());
        // 配置 t_order 表分片規(guī)則
        ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("t_order", "db${0..1}.t_order_${1..1000}");
        orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("order_id", "t_order_table_algorithms"));
        orderTableRuleConfig.setDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("order_id", "t_order_database_algorithms"));
        shardingRuleConfig.getTables().add(orderTableRuleConfig);

        // 是否在控制臺(tái)輸出解析改造后真實(shí)執(zhí)行的 SQL
        Properties properties = new Properties();
        properties.setProperty("sql-show", "true");

        // 創(chuàng)建 ShardingSphere 數(shù)據(jù)源
        return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), properties);
    }

    /**
     * 配置數(shù)據(jù)源1
     * 公眾號(hào)：程序員小富
     */
    public DataSource dataSource0() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeznotallow=Asia/Shanghai&allowPublicKeyRetrieval=true");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    /**
     * 配置數(shù)據(jù)源2
     * 公眾號(hào)：程序員小富
     */
    public DataSource dataSource1() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeznotallow=Asia/Shanghai&allowPublicKeyRetrieval=true");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    /**
     * 配置分片算法
     * 公眾號(hào)：程序員小富
     */
    private Map<String, AlgorithmConfiguration> getShardingAlgorithms() {
        Map<String, AlgorithmConfiguration> shardingAlgorithms = new LinkedHashMap<>();

        // 自定義分庫算法
        Properties databaseAlgorithms = new Properties();
        databaseAlgorithms.setProperty("algorithm-expression", "db$->{order_id % 2}");
        shardingAlgorithms.put("t_order_database_algorithms", new AlgorithmConfiguration("INLINE", databaseAlgorithms));

        // 自定義分表算法
        Properties tableAlgorithms = new Properties();
        tableAlgorithms.setProperty("algorithm-expression", "db$->{order_id % 1000}");
        shardingAlgorithms.put("t_order_table_algorithms", new AlgorithmConfiguration("INLINE", tableAlgorithms));

        return shardingAlgorithms;
    }
}

/**
 * @author 公眾號(hào)：程序員小富
 * 自動(dòng)創(chuàng)建分片表
 * @date 2023/12/31 17:25
 */
@SpringBootTest
class AutoCreateTablesTests {
    @Resource
    private JdbcTemplate jdbcTemplate;
    /**
     * 執(zhí)行創(chuàng)建邏輯表的SQL，會(huì)根據(jù)AutoTables的配置自動(dòng)在對(duì)應(yīng)的數(shù)據(jù)源內(nèi)創(chuàng)建分片表
     * @author 公眾號(hào)：程序員小富
     */
    @Test
    public void autoCreateOrderTableTest() {

        jdbcTemplate.execute("CREATE TABLE `t_order` (\n" +
                "  `order_id` bigint(20) NOT NULL,\n" +
                "  `order_number` varchar(255) NOT NULL,\n" +
                "  `customer_id` bigint(20) NOT NULL,\n" +
                "  `order_date` date NOT NULL,\n" +
                "  `total_amount` decimal(10,2) NOT NULL,\n" +
                "  PRIMARY KEY (`order_id`) USING BTREE\n" +
                ");");
    }
}

/**
 * @author 公眾號(hào)：程序員小富
 * 自動(dòng)創(chuàng)建分片表
 * @date 2023/12/31 17:25
 */
@SpringBootTest
class AutoCreateTablesTests {
    @Resource
    private JdbcTemplate jdbcTemplate;
    
    @Test
    public void autoModifyOrderTableTest() {

        jdbcTemplate.execute("ALTER TABLE t_order MODIFY COLUMN order_number varchar(500);");
    }
}

spring:
  shardingsphere:
    # 數(shù)據(jù)源配置
    datasource:
      ......
    # 具體規(guī)則配置
    rules:
      sharding:
        # 邏輯表分片規(guī)則
        tables:
          # 邏輯表名稱
          t_order:
            .....
        # 自動(dòng)分片表規(guī)則配置
        auto-tables:
          t_order: # 邏輯表名稱
            actual-data-sources: db$->{0..1}
            sharding-strategy: # 切分策略
              standard: # 用于單分片鍵的標(biāo)準(zhǔn)分片場(chǎng)景
                sharding-column: order_id # 分片列名稱
                sharding-algorithm-name: t_order_mod # 自動(dòng)分片算法名稱

spring:
  shardingsphere:
    # 數(shù)據(jù)源配置
    datasource:
      .....
    # 具體規(guī)則配置
    rules:
      sharding:
        # 自動(dòng)分片表規(guī)則配置
        auto-tables:
          t_order:
            actual-data-sources: db$->{0..1}
            sharding-strategy:
              standard:
                sharding-column: order_date
                sharding-algorithm-name: t_order_mod
        # 分片算法定義
        sharding-algorithms:
          t_order_mod:
            type: MOD
            props:
              # 指定分片數(shù)量
              sharding-count: 2000