文章目录
显示
? 优质资源分享 ?
学习路线指引(点击解锁) | 知识定位 | 人群定位 |
---|---|---|
? Python实战微信订餐小程序 ? | 进阶级 | 本课程是python flask+微信小程序的完美结合,从项目搭建到腾讯云部署上线,打造一个全栈订餐系统。 |
?Python量化交易实战? | 入门级 | 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 |
一、目的
保证在分库分表中每条数据具有唯一性
二、修改配置文件config-sharding.yaml,并重启服务
#
# 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\_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:
- !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\_MOD
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\_MOD: # 取模名称,可自定义
type: MOD # 取模算法
props:
sharding-count: 2 # 分片数量,因为分了2个表,所以这里是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
三、数据准备
-- 创建表
SET NAMES utf8mb4;
SET FOREIGN\_KEY\_CHECKS = 0;
-- ----------------------------
-- Table structure for t\_product\_0
-- ----------------------------
DROP TABLE IF EXISTS `t\_product`;
CREATE TABLE `t\_product\_0` (
`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,'apple');
INSERT INTO t\_product(product\_id,product\_name) VALUES(2,'banana');
四、查看数据
1、查看shardingsphere中间件t_product表数据,其中id字段会自动生成唯一id
2、查看t_product_0、t_product_1表数据,同时对数据进行了分表存储(因为配置文件中有做分表配置)