【分库分表】ShardingSphere-Proxy：实现分库

最新推荐文章于 2024-05-14 00:14:10 发布

CodeBoyWLC

最新推荐文章于 2024-05-14 00:14:10 发布

阅读量133

点赞数

分类专栏：分库分表

本文链接：https://blog.csdn.net/wlc19981111/article/details/116504011

版权

分库分表专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文介绍了如何使用Apache ShardingSphere配置双节点MySQL数据库，详细展示了t_order表的分片策略，包括实际数据节点的分配和SQL路由规则。通过实例演示了如何插入数据并根据user_id和order_id进行自动路由。

摘要由CSDN通过智能技术生成

schemaName: sharding_db
dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/course_db_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/course_db_2?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - t_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

cd E:\apache-shardingsphere-incubating-4.0.1-sharding-proxy-bin\apache-shardingsphere-incubating-4.0.1-sharding-proxy-bin\bin
start.bat 3308    #用别的端口启动，防止占用

mysql -P3308 -uroot -p


use sharding_db;


CREATE TABLE ds_0.t_order(
  order_id bigint(20) primary key,
  user_id bigint(20) not null,
  status varchar(10) not null
);          #此时course_db_1与course_db_2都含俩表t_order_0、t_order_1

select * from t_order; 

insert into t_order values(11,1,'iniy');    //user_id为奇数，则放course_db_2内，order_id奇数，则放t_order_1内