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安全错误
数据库的锁怎样保障安全
js 同步服务器时间
金山区管理软件开发厂家报价
天津租赁管理软件开发公司
山东pdu服务器专用电源排行榜
临沂毋宁网络技术有限公司
确保数据库安全的人员
总结无线传感器网络技术
led屏网络安全责任
宁波海曙大熊网络技术信息
cpu读取位数据库
软件开发类的论文可以发表吗
北京搜狗网络技术是什么
近三年网络安全宣传周主题
襄州 软件开发技术班
联想刀片服务器cmm管理工具
数据库关系图设计工具
5S核糖体RNA数据库
新兴网络技术特点
苹果网络安全地址检测怎么关闭
开讲啦网络安全教程
100平科技互联网公司设计
萧山区手机软件开发
移动端开发用什么软件开发
诺基亚全球手机数据库
中国电信软件开发岗位
网易版创建服务器过程
浙江丽城互联网科技有限公司
mysql数据库驱动设置
珲春软件开发招聘
基于安卓软件开发的论文