Sharding中怎么使用Sphere实现数据分库分表操作)
发表于:2024-10-27 作者:千家信息网编辑
千家信息网最后更新 2024年10月27日,本篇文章给大家分享的是有关Sharding中怎么使用Sphere实现数据分库分表操作),小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。引入
千家信息网最后更新 2024年10月27日Sharding中怎么使用Sphere实现数据分库分表操作)
本篇文章给大家分享的是有关Sharding中怎么使用Sphere实现数据分库分表操作),小编觉得挺实用的,因此分享给大家学习,希望大家阅读完这篇文章后可以有所收获,话不多说,跟着小编一起来看看吧。
引入依赖的jar
org.apache.shardingsphere sharding-jdbc-core ${sharding-sphere.version} org.apache.shardingsphere sharding-jdbc-spring-boot-starter ${sharding-sphere.version}
我这里用的是最新的版本
4.0.0-RC3-SNAPSHOT
库表脚本
当前测试例子是2库各10表,外加默认库的t_user表。结果如下:
创建数据库稍微注意一点,命名不能带下划线。 sharding0库脚本如下:
/* Navicat Premium Data Transfer Source Server : Source Server Type : MySQL Source Server Version : 50727 Source Host : Source Schema : sharding0 Target Server Type : MySQL Target Server Version : 50727 File Encoding : 65001 Date: 17/09/2019 10:43:09*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_order0-- ----------------------------DROP TABLE IF EXISTS `t_order0`;CREATE TABLE `t_order0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order1-- ----------------------------DROP TABLE IF EXISTS `t_order1`;CREATE TABLE `t_order1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order2-- ----------------------------DROP TABLE IF EXISTS `t_order2`;CREATE TABLE `t_order2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order3-- ----------------------------DROP TABLE IF EXISTS `t_order3`;CREATE TABLE `t_order3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order4-- ----------------------------DROP TABLE IF EXISTS `t_order4`;CREATE TABLE `t_order4` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order5-- ----------------------------DROP TABLE IF EXISTS `t_order5`;CREATE TABLE `t_order5` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order6-- ----------------------------DROP TABLE IF EXISTS `t_order6`;CREATE TABLE `t_order6` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order7-- ----------------------------DROP TABLE IF EXISTS `t_order7`;CREATE TABLE `t_order7` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order8-- ----------------------------DROP TABLE IF EXISTS `t_order8`;CREATE TABLE `t_order8` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order9-- ----------------------------DROP TABLE IF EXISTS `t_order9`;CREATE TABLE `t_order9` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_user-- ----------------------------DROP TABLE IF EXISTS `t_user`;CREATE TABLE `t_user` ( `user_id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `sex` int(4) NULL DEFAULT NULL, `phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`user_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
sharding1数据库脚本如下:
/* Navicat Premium Data Transfer Source Server : 139.196.229.195 Source Server Type : MySQL Source Server Version : 50727 Source Host : 139.196.229.195:3306 Source Schema : sharding1 Target Server Type : MySQL Target Server Version : 50727 File Encoding : 65001 Date: 17/09/2019 10:43:20*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_order0-- ----------------------------DROP TABLE IF EXISTS `t_order0`;CREATE TABLE `t_order0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order1-- ----------------------------DROP TABLE IF EXISTS `t_order1`;CREATE TABLE `t_order1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order2-- ----------------------------DROP TABLE IF EXISTS `t_order2`;CREATE TABLE `t_order2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order3-- ----------------------------DROP TABLE IF EXISTS `t_order3`;CREATE TABLE `t_order3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order4-- ----------------------------DROP TABLE IF EXISTS `t_order4`;CREATE TABLE `t_order4` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order5-- ----------------------------DROP TABLE IF EXISTS `t_order5`;CREATE TABLE `t_order5` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order6-- ----------------------------DROP TABLE IF EXISTS `t_order6`;CREATE TABLE `t_order6` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order7-- ----------------------------DROP TABLE IF EXISTS `t_order7`;CREATE TABLE `t_order7` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order8-- ----------------------------DROP TABLE IF EXISTS `t_order8`;CREATE TABLE `t_order8` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Table structure for t_order9-- ----------------------------DROP TABLE IF EXISTS `t_order9`;CREATE TABLE `t_order9` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` bigint(32) NULL DEFAULT NULL, `order_id` bigint(32) NULL DEFAULT NULL, `title` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `content` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;
默认分库分表策略
Springboot配置
#数据源名称,多数据源以逗号分隔spring.shardingsphere.datasource.names=sharding0,sharding1#sharding0是数据源名spring.shardingsphere.datasource.sharding0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.sharding0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.sharding0.url=jdbc:mysql://***:3306/sharding0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULLspring.shardingsphere.datasource.sharding0.username=rootspring.shardingsphere.datasource.sharding0.password=***# spring.shardingsphere.datasource..xxx= #数据库连接池的其它属性# sharding1 是数据源名称spring.shardingsphere.datasource.sharding1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.sharding1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.sharding1.url=jdbc:mysql://***:3306/sharding1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULLspring.shardingsphere.datasource.sharding1.username=rootspring.shardingsphere.datasource.sharding1.password=***# 默认数据源,没有分片的走这个数据源spring.shardingsphere.sharding.default-data-source-name=sharding0spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=sharding$->{user_id % 2}# t_order是表明spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=sharding$->{0..1}.t_order$->{0..9}spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_idspring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 10}# 分布式主键 内置的支持这三种 SNOWFLAKE/UUID/LEAF_SEGMENTspring.shardingsphere.sharding.tables.t_order.key-generator.column=idspring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
读写操作
因为上面是根据 user_id、order_id 两个字段分库分表的,所以必须插入查询都得带上user_id、order_id两个字段。
单表插入(支持)
@Test public void testSharding(){ Order order = new Order(); order.setUserId(1l); order.setOrderId(1l); order.setTitle("测试,userId:"+order.getUserId() + " orderId:" + order.getOrderId()); order.setContent(order.getTitle()); Assert.assertEquals(1,orderMapper.insert(order)); }
查看数据库,确实插入了
单表查询(支持)
@Test public void testShardingRead(){ OrderExample ex = new OrderExample(); ex.createCriteria().andUserIdEqualTo(1l).andOrderIdEqualTo(1l); Listorders = orderMapper.selectByExample(ex); Assert.assertNotNull(orders); Assert.assertEquals(1, orders.size()); orders.stream().forEach(o->{ System.out.println("userId:"+o.getUserId() + " orderId:" + o.getOrderId()); }); }
默认库表插入(支持)
@Test public void testUserWrite(){ User user = new User(); user.setAge(10); user.setName("张三"); user.setPhone("15157181986"); user.setSex(1); user.setUpdateTime(new Date()); user.setCreateTime(new Date()); userMapper.insert(user); System.out.println("userId:"+user.getUserId()); }
单表聚合查询
count(支持)
@Test public void testShardingReadCount(){ OrderExample ex = new OrderExample(); long count = orderMapper.countByExample(ex); System.out.println("count:"+count); }
order by,limit(支持)
@Test public void testShardingReadLimit(){ OrderExample ex = new OrderExample(); ex.setLimit(2); ex.setOffset(2l); ex.setOrderByClause(" user_id desc "); Listorders = orderMapper.selectByExample(ex); orders.stream().forEach(o->{ System.out.println("userId:"+o.getUserId() + " orderId:" + o.getOrderId()); }); }
group by(支持)
@Getter@Setterpublic class GroupResult implements Serializable { private Long userId; private Integer cnt;}@Test public void testShardingReadGroupBy(){ List userOrderCounts = orderMapper.getUserOrderCount(); userOrderCounts.forEach(u->System.out.println("userId:"+u.getUserId() + " count:" + u.getCnt())); }
单表与分表关联查询(仅支持广播表跟分表的关联查询)
单表:不做分库分表的逻辑表 (如本示例中的t_user) 分表:做分库分表的逻辑表(如本示例中的 t_order) 广播表:指所有的分片数据源中都存在的表,表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景,例如:字典表。(这个概念参考:广播表)
这里 单表 跟 分表关联查询,必须每一个库中有一份单表(也就是广播表),否则不能关联查询。如下面的语句,本示例中是不能执行的。因为本示例 t_order 逻辑表分散在两个库中,t_user表必须要在两个库中都有一份才能关联查询。
以上就是Sharding中怎么使用Sphere实现数据分库分表操作),小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。
数据
分表
查询
支持
数据源
分库
关联
数据库
两个
示例
广播
脚本
逻辑
名称
字段
更多
知识
篇文章
测试
实用
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
手动服务器密码在哪里
国产数据库 安全性
网络技术和数字媒体哪个好就业
内蒙古通信软件开发服务价钱
数据库设计实现应用
网络安全的内涵四个安全
网络安全的预防PPT图片
青少年守护网络安全视频
中国制造服务器型号
软件开发论文提纲
dell服务器电源处闪绿灯
院前创伤急救中心软件开发商
数据库技术在电气专业中的应用
数据库如何求区间的和
云一体软件开发
鞍山app软件开发费用
苏州戴尔服务器阵列卡驱动
网络安全板报盾牌
竹溪互联网软件开发口碑推荐
宝山区营销软件开发包括什么
天下3服务器势力满了怎么办
网络安全最大的命门
服务器建立邮箱
网络安全法哪年颁布实行
互联网 教育科技
学生公寓网络技术
魔兽改图软件开发
服务器反复调取数据库很卡
软件开发个人介绍
mdb数据库浏览器