## Licensed to the Apache Software Foundation (ASF) under one or more# contributor license agreements. See the NOTICE file distributed with# this work for additional information regarding copyright ownership.# The ASF licenses this file to You under the Apache License, Version 2.0# (the "License"); you may not use this file except in compliance with# the License. You may obtain a copy of the License at## http://www.apache.org/licenses/LICENSE-2.0## Unless required by applicable law or agreed to in writing, software# distributed under the License is distributed on an "AS IS" BASIS,# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.# See the License for the specific language governing permissions and# limitations under the License.######################################################################################################## # Here you can configure the rules for the proxy.# This example is configuration of sharding rule.# ########################################################################################################schemaName: sharding_db##dataSources:# ds_0:# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_0# username: postgres# password: postgres# connectionTimeoutMilliseconds: 30000# idleTimeoutMilliseconds: 60000# maxLifetimeMilliseconds: 1800000# maxPoolSize: 50# minPoolSize: 1# ds_1:# url: jdbc:postgresql://127.0.0.1:5432/demo_ds_1# username: postgres# password: postgres# connectionTimeoutMilliseconds: 30000# idleTimeoutMilliseconds: 60000# maxLifetimeMilliseconds: 1800000# maxPoolSize: 50# minPoolSize: 1##rules:#- !SHARDING# tables:# t_order:# actualDataNodes: ds_${0..1}.t_order_${0..1}# tableStrategy:# standard:# shardingColumn: order_id# shardingAlgorithmName: t_order_inline# keyGenerateStrategy:# column: order_id# keyGeneratorName: snowflake# t_order_item:# actualDataNodes: ds_${0..1}.t_order_item_${0..1}# tableStrategy:# standard:# shardingColumn: order_id# shardingAlgorithmName: t_order_item_inline# keyGenerateStrategy:# column: order_item_id# keyGeneratorName: snowflake# bindingTables:# - t_order,t_order_item# defaultDatabaseStrategy:# standard:# shardingColumn: user_id# shardingAlgorithmName: database_inline# defaultTableStrategy:# none:# # shardingAlgorithms:# database_inline:# type: INLINE# props:# algorithm-expression: ds_${user_id % 2}# t_order_inline:# type: INLINE# props:# algorithm-expression: t_order_${order_id % 2}# t_order_item_inline:# type: INLINE# props:# algorithm-expression: t_order_item_${order_id % 2}# # keyGenerators:# snowflake:# type: SNOWFLAKE# props:# worker-id: 123######################################################################################################## If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.######################################################################################################## 连接mysql所使用的数据库名 schemaName: MyDb dataSources: ds_0: # 主库 url: jdbc:mysql://127.0.0.1:3306/MyDb?serverTimezone=UTC&useSSL=false username: root # 数据库用户名 password: mysql123 # 登录密码 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 ds_0_read0: # 从库 url: jdbc:mysql://192.168.140.132:3306/MyDb?serverTimezone=UTC&useSSL=false username: root # 数据库用户名 password: Xiaohemiao_123 # 登录密码 connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1 # ds_1:# url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false# username: root# password:# connectionTimeoutMilliseconds: 30000# idleTimeoutMilliseconds: 60000# maxLifetimeMilliseconds: 1800000# maxPoolSize: 50# minPoolSize: 1## 规则 rules: - !READWRITE_SPLITTING #读写分离规则 dataSources: pr_ds: writeDataSourceName: ds_0 readDataSourceNames: - ds_0_read0 - !SHARDING tables: t_product: #需要进行分表的表名 actualDataNodes: ds_0.t_product_${0..1} # 表达式，将表分为t_product_0 , t_product_1 tableStrategy: standard: shardingColumn: product_id # 字段名 shardingAlgorithmName: t_product_VOLUME_RANGE keyGenerateStrategy: column: id keyGeneratorName: snowflake #雪花算法# t_order_item:# actualDataNodes: ds_${0..1}.t_order_item_${0..1}# tableStrategy:# standard:# shardingColumn: order_id# shardingAlgorithmName: t_order_item_inline# keyGenerateStrategy:# column: order_item_id# keyGeneratorName: snowflake# bindingTables:# - t_order,t_order_item# defaultDatabaseStrategy:# standard:# shardingColumn: user_id# shardingAlgorithmName: database_inline# defaultTableStrategy:# none:# shardingAlgorithms: t_product_VOLUME_RANGE: # 取模名称，可自定义 type: VOLUME_RANGE # 取模算法 props: range-lower: '5' # 最小容量为5条数据，仅方便测试 range-upper: '10' #最大容量为10条数据，仅方便测试 sharding-volume: '5' #分片的区间的数据的间隔# t_order_inline:# type: INLINE# props:# algorithm-expression: t_order_${order_id % 2}# t_order_item_inline:# type: INLINE# props:# algorithm-expression: t_order_item_${order_id % 2}# keyGenerators: snowflake: # 雪花算法名称，自定义名称 type: SNOWFLAKE props: worker-id: 123
上述配置是同时有做容量范围分片
在中间件中ShardingSphere中创建MyDb数据库，并创建相关表和插入数据
-- 创建表SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_product-- ----------------------------DROP TABLE IF EXISTS `t_product`;CREATE TABLE `t_product` ( `id` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `product_id` int(11) NOT NULL, `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`id`, `product_id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;-- 插入表数据INSERT INTO t_product(product_id,product_name) VALUES(1,'one');INSERT INTO t_product(product_id,product_name) VALUES(2,'two');INSERT INTO t_product(product_id,product_name) VALUES(3,'three');INSERT INTO t_product(product_id,product_name) VALUES(4,'four');INSERT INTO t_product(product_id,product_name) VALUES(5,'five');INSERT INTO t_product(product_id,product_name) VALUES(6,'six');INSERT INTO t_product(product_id,product_name) VALUES(7,'seven');
