ShardingSphere-Proxy：从实际场景出发，快速上手（三）

四、场景实践
本章节从实战场景的前提出发，通过 ShardingSphere-Proxy 完成上述需求。

 
1. 初始化数据库表

# CREATE DATABASE
CREATE DATABASE user_sharding_0;

CREATE DATABASE user_sharding_1;

# CREATE TABLE
use user_sharding_0;

CREATE TABLE `t_user_0` (
 `id` bigint (20) NOT NULL,
 `user_id` bigint (20) NOT NULL,
 `create_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = latin1;

CREATE TABLE `t_user_1` (
 `id` bigint (20) NOT NULL,
 `user_id` bigint (20) NOT NULL,
 `create_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = latin1;


use user_sharding_1;

CREATE TABLE `t_user_0` (
 `id` bigint (20) NOT NULL,
 `user_id` bigint (20) NOT NULL,
 `create_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = latin1;


CREATE TABLE `t_user_1` (
 `id` bigint (20) NOT NULL,
 `user_id` bigint (20) NOT NULL,
 `create_date` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = latin1;

2. 初始化 Proxy 分片配置

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/user_sharding_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/user_sharding_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_user:
      actualDataNodes: ds_${0..1}.t_user_${0..1}
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: t_user_inline
      keyGenerateStrategy:
        column: user_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_user
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_user_inline:
      type: INLINE
      props:
        algorithm-expression: t_user_${user_id % 2}

  keyGenerators:
    snowflake:
      type: SNOWFLAKE

文章转自公众号：龙台的技术笔记