-
shadingjdbc实战分表分库
一、问题汇总
1. 水平与垂直拆分之间的区别？
垂直拆分：根据业务实现拆分
微服务架构模式中，会员团队、支付团队、交易团队。有自己独立的数据库，会员db、支付db、交易db，带来的分布式事务问题。
水平拆分：将一张大表的数量拆分n多张不同子表。
- 第1种：同一库分表
原表：mayikt-member–1500万条
分表：
mayikt-member0–0~500万条
mayikt-member1–500~1000万条
mayikt-member2–1000~1500万条
- 第2种：多库分表
原表：mayikt-member–1500万条
mayikt-member：
分库分表：
mayikt-memberdb01
mayikt-member --0~500万条
mayikt-memberdb02
mayikt-member --500~1000万条
mayikt-memberdb03
mayikt-member --1000~1500万条
2. 单表达到多大量开始进行分库分表？
单表行数超过500万行或者单标容量超过2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根据达不到这个级别，请不要在创建表时就分库分表。
3. 基于客户端与服务端实现分表分库区别？
-
1.基于服务端mycat实现数据库代理
优点：能够保证数据库的安全性
缺点：效率比较低 -
2.基于客户端SHardingjdbc实现数据库代理
有点：效率比较高
缺点：不能够保证数据库的安全性，内存溢出
4. 数据库分表分库策略有哪些？
区域/取模（不推荐使用，后期无法进行扩容）
按照范围分片（推荐使用）
按照日期进行分片
按照枚举进行分片
一致性hash分片
按照目标字段前缀制定进行分片5. 自定义范围分表算法实现分表?
package com.mayikt.api.impl.config; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import lombok.extern.slf4j.Slf4j; import java.util.Collection; @Slf4j public class MayiktPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { /** * 真实建议是为500万测试为5条 */ private Long tableSize = 5l; /** * 插入数据 改写表的名称 * 查询 改写表的名称 * @param collection * @param preciseShardingValue * @return */ @Override public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) { Double userId = Double.valueOf(preciseShardingValue.getValue()); Double temp = userId / tableSize; String tableName = "meite_user" + (int) Math.ceil(temp); // 分表分库 userid====mysql自带的自增 序列 雪花算法 log.info("- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
二、整合ShardingSphere实现分表
2.1. 依赖
<!--分表库分表中间件--> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>3.1.0</version> </dependency>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
2.2. 配置
spring: jackson: date-format: yyyy-MM-dd HH:mm:ss profiles: active: dev application: ###服务的名称 name: mayikt-member cloud: nacos: discovery: ###nacos注册地址 server-addr: 127.0.0.1:8848 config: server-addr: 127.0.0.1:8848 file-extension: yml # datasource: # driver-class-name: com.mysql.cj.jdbc.Driver # url: jdbc:mysql://localhost:3306/mayikt-member?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 # username: root # password: 123456 redis: host: 127.0.0.1 port: 6379 password: 123456 logging: level: ###打印mybatis日志 com.mayikt.api.impl.mapper: debug main: allow-bean-definition-overriding: true server: port: 7000 mayikt: userName: mayikt thread: corePoolSize: 10 maxPoolSize: 10 queueCapacity: 20 keepAlive: 60 # 数据源 mayiktdb #按照范围分片 sharding: jdbc: datasource: names: mayikt-member # 第一个数据库 mayikt-member: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://127.0.0.1:3306/mayikt-member?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 username: root password: 123456 # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略 config: sharding: tables: meite_user: actual-data-nodes: mayikt-member.meite_user$->{1..3} table-strategy: standard: ### where userId precise-algorithm-class-name: com.mayikt.api.impl.config.MayiktPreciseShardingAlgorithm sharding-column: user_id # 打印执行的数据库 props: sql: show: true # 取模配置 ## 数据源 mayiktdb #sharding: # jdbc: # datasource: # names: mayikt-member # # 第一个数据库 # mayikt-member: # type: com.zaxxer.hikari.HikariDataSource # driver-class-name: com.mysql.cj.jdbc.Driver # jdbc-url: jdbc:mysql://127.0.0.1:3306/mayikt-member?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8 # username: root # password: 123456 # # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略 # config: # sharding: # tables: # meite_user: # # mayikt-member.meite_user0 mayikt-member.meite_user1 mayikt-member.meite_user2 # actual-data-nodes: mayikt-member.meite_user$->{0..2} # table-strategy: # inline: # # 根据user_id分表 # sharding-column: user_id # # 分片算法表达式 meite_user——1%3 meite_user_2% 3 meite_user_3% 3 # algorithm-expression: meite_user$->{user_id % 3} # # 打印执行的数据库 # props: # sql: # show: true- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
2.3. 表结构
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for meite_user -- ---------------------------- DROP TABLE IF EXISTS `meite_user`; CREATE TABLE `meite_user` ( `USER_ID` int NOT NULL AUTO_INCREMENT COMMENT 'USER_ID', `MOBILE` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号', `PASSWORD` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码', `USER_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名', `SEX` tinyint(1) NULL DEFAULT 0 COMMENT '性别 1-男 2-女', `AGE` tinyint NULL DEFAULT 0 COMMENT '年龄', `CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '注册时间', `IS_AVALIBLE` tinyint(1) NULL DEFAULT 1 COMMENT '是否可用 1-正常 2-冻结 ', `PIC_IMG` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像', `QQ_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'QQ联合登陆id', `WX_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '微信公众号关注id', `VERSION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `IS_DELETE` int NULL DEFAULT NULL, PRIMARY KEY (`USER_ID`) USING BTREE, UNIQUE INDEX `MOBILE_UNIQUE`(`MOBILE`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 87 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户会员表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of meite_user -- ---------------------------- INSERT INTO `meite_user` VALUES (21, '17610155217', '15A013BCAC0C50049356B322E955035E', '90架构师', 0, 0, NULL, 1, NULL, NULL, 'oD8nF5jpNF9KXU_j49uvqOPVdiEU', NULL, 0); -- ---------------------------- -- Table structure for meite_user0 -- ---------------------------- DROP TABLE IF EXISTS `meite_user0`; CREATE TABLE `meite_user0` ( `USER_ID` int NOT NULL AUTO_INCREMENT COMMENT 'USER_ID', `MOBILE` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号', `PASSWORD` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码', `USER_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名', `SEX` tinyint(1) NULL DEFAULT 0 COMMENT '性别 1-男 2-女', `AGE` tinyint NULL DEFAULT 0 COMMENT '年龄', `CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '注册时间', `IS_AVALIBLE` tinyint(1) NULL DEFAULT 1 COMMENT '是否可用 1-正常 2-冻结 ', `PIC_IMG` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像', `QQ_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'QQ联合登陆id', `WX_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '微信公众号关注id', `VERSION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `IS_DELETE` int NULL DEFAULT NULL, PRIMARY KEY (`USER_ID`) USING BTREE, UNIQUE INDEX `MOBILE_UNIQUE`(`MOBILE`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 87 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户会员表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of meite_user0 -- ---------------------------- INSERT INTO `meite_user0` VALUES (12, '17610155217', '15A013BCAC0C50049356B322E955035E', '90架构师12', 0, 0, NULL, 1, NULL, NULL, 'oD8nF5jpNF9KXU_j49uvqOPVdiEU', NULL, 0); -- ---------------------------- -- Table structure for meite_user1 -- ---------------------------- DROP TABLE IF EXISTS `meite_user1`; CREATE TABLE `meite_user1` ( `USER_ID` int NOT NULL AUTO_INCREMENT COMMENT 'USER_ID', `MOBILE` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号', `PASSWORD` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码', `USER_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名', `SEX` tinyint(1) NULL DEFAULT 0 COMMENT '性别 1-男 2-女', `AGE` tinyint NULL DEFAULT 0 COMMENT '年龄', `CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '注册时间', `IS_AVALIBLE` tinyint(1) NULL DEFAULT 1 COMMENT '是否可用 1-正常 2-冻结 ', `PIC_IMG` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像', `QQ_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'QQ联合登陆id', `WX_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '微信公众号关注id', `VERSION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `IS_DELETE` int NULL DEFAULT NULL, PRIMARY KEY (`USER_ID`) USING BTREE, UNIQUE INDEX `MOBILE_UNIQUE`(`MOBILE`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 87 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户会员表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of meite_user1 -- ---------------------------- -- ---------------------------- -- Table structure for meite_user2 -- ---------------------------- DROP TABLE IF EXISTS `meite_user2`; CREATE TABLE `meite_user2` ( `USER_ID` int NOT NULL COMMENT 'USER_ID', `MOBILE` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号', `PASSWORD` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码', `USER_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名', `SEX` tinyint(1) NULL DEFAULT 0 COMMENT '性别 1-男 2-女', `AGE` tinyint NULL DEFAULT 0 COMMENT '年龄', `CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '注册时间', `IS_AVALIBLE` tinyint(1) NULL DEFAULT 1 COMMENT '是否可用 1-正常 2-冻结 ', `PIC_IMG` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像', `QQ_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'QQ联合登陆id', `WX_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '微信公众号关注id', `VERSION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `IS_DELETE` int NULL DEFAULT NULL, PRIMARY KEY (`USER_ID`) USING BTREE, UNIQUE INDEX `MOBILE_UNIQUE`(`MOBILE`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 87 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户会员表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of meite_user2 -- ---------------------------- INSERT INTO `meite_user2` VALUES (6, '17610135255', NULL, '90架构师update', 1, 2, '2014-09-05 18:54:16', 1, 'test_45904720d2ea', NULL, NULL, NULL, 0); -- ---------------------------- -- Table structure for meite_user3 -- ---------------------------- DROP TABLE IF EXISTS `meite_user3`; CREATE TABLE `meite_user3` ( `USER_ID` int NOT NULL AUTO_INCREMENT COMMENT 'USER_ID', `MOBILE` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号', `PASSWORD` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码', `USER_NAME` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名', `SEX` tinyint(1) NULL DEFAULT 0 COMMENT '性别 1-男 2-女', `AGE` tinyint NULL DEFAULT 0 COMMENT '年龄', `CREATE_TIME` timestamp NULL DEFAULT NULL COMMENT '注册时间', `IS_AVALIBLE` tinyint(1) NULL DEFAULT 1 COMMENT '是否可用 1-正常 2-冻结 ', `PIC_IMG` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户头像', `QQ_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'QQ联合登陆id', `WX_OPEN_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '微信公众号关注id', `VERSION` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `IS_DELETE` int NULL DEFAULT NULL, PRIMARY KEY (`USER_ID`) USING BTREE, UNIQUE INDEX `MOBILE_UNIQUE`(`MOBILE`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 87 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户会员表' ROW_FORMAT = Dynamic;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
2.4. 测试接口
package com.mayikt.api.member; import com.mayikt.api.base.BaseResponse; import com.mayikt.api.member.dto.resp.UserInfoDto; import org.springframework.web.bind.annotation.*; public interface UserInfoService { /** * 根据token获取用户的信息 * * @param token * @return */ @GetMapping("getUserInfo") BaseResponse<UserInfoDto> getUserInfo(@RequestParam("token") String token); // 分库分表案例接口 /** * 测试根据用户userId查询分表用户数据 * * @param userId * @return */ @GetMapping("testGetSubTableUser") BaseResponse<UserInfoDto> testGetSubTableUser(Long userId); /** * 测试根据用户userId删除分表用户数据 * * @param userId * @return */ @DeleteMapping("testDelSubTableUser") BaseResponse<String> testDelSubTableUser(Long userId); /** * 测试增加用户数据到分表中 * * @param userReqDto * @return */ @PostMapping("testInsertSubTableUser") BaseResponse<String> testInsertSubTableUser(@RequestBody UserInfoDto userReqDto); /** * 测试根据用户userId更新分表用户数据 * * @param userReqDto * @return */ @PutMapping("testUpdateSubTableUser") BaseResponse<String> testUpdateSubTableUser(@RequestBody UserInfoDto userReqDto); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
2.5. 实现类
package com.mayikt.api.impl.member; import com.mayikt.api.base.BaseApiService; import com.mayikt.api.base.BaseResponse; import com.mayikt.api.impl.entity.UserInfoDo; import com.mayikt.api.impl.mapper.UserInfoMapper; import com.mayikt.api.member.UserInfoService; import com.mayikt.api.member.dto.resp.UserInfoDto; import com.mayikt.api.utils.DesensitizationUtil; import com.mayikt.api.utils.TokenUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RestController; @RestController public class UserInfoServiceImpl extends BaseApiService implements UserInfoService { @Autowired private UserInfoMapper userInfoMapper; @Autowired private TokenUtils tokenUtils; @Override public BaseResponse<UserInfoDto> getUserInfo(String token) { if (StringUtils.isEmpty(token)) { return setResultError("token 不能为空!"); } // 2.根据token查询 Redis 获取用户的userid String tokenValue = tokenUtils.getTokenValue(token); if (StringUtils.isEmpty(tokenValue)) { return setResultError("token 可能失效!"); } Long userId = Long.parseLong(tokenValue); // 3.根据用户的userid 查询用户信息 UserInfoDo userInfoDo = userInfoMapper.selectById(userId); if (userInfoDo == null) { return setResultError("token错误！"); } UserInfoDto userInfoDto = doToDto(userInfoDo, UserInfoDto.class); userInfoDto.setMobile(DesensitizationUtil.mobileEncrypt(userInfoDto.getMobile())); return setResultSuccess(userInfoDto); } /** * 测试根据用户userId查询分表用户数据 * * @param userId * @return */ @Override public BaseResponse<UserInfoDto> testGetSubTableUser(Long userId) { UserInfoDo userInfoDo = userInfoMapper.selectById(userId); UserInfoDto userInfoDto = doToDto(userInfoDo, UserInfoDto.class); return setResultSuccess(userInfoDto); } /** * 测试根据用户userId删除分表用户数据 * * @param userId * @return */ @Override public BaseResponse<String> testDelSubTableUser(Long userId) { int result = userInfoMapper.deleteById(userId); if (result <= 0) { return setResultError("删除userId->" + userId + "失败"); } return setResultSuccess("删除userId->" + userId + "成功"); } /** * 测试增加用户数据到分表中 * * @param userInfoDto * @return */ @Override public BaseResponse<String> testInsertSubTableUser(UserInfoDto userInfoDto) { UserInfoDo userInfoDo = new UserInfoDo(); BeanUtils.copyProperties(userInfoDto, userInfoDo); int result = userInfoMapper.insert(userInfoDo); if (result <= 0) { return setResultError("新增数据失败"); } return setResultSuccess("新增数据成功"); } /** * 测试根据用户userId更新分表用户数据 * * @param userInfoDto * @return */ @Override public BaseResponse<String> testUpdateSubTableUser(UserInfoDto userInfoDto) { UserInfoDo userInfoDo = new UserInfoDo(); BeanUtils.copyProperties(userInfoDto, userInfoDo); int result = userInfoMapper.updateById(userInfoDo); if (result <= 0) { return setResultError("更新数据失败"); } return setResultSuccess("更新数据成功"); } }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
四、测试验证
4.1. 测试增加用户数据到分表中
http://localhost:7000/testInsertSubTableUser- 1
{ "userId": 6, "mobile": "17610135255", "userName": "90架构师", "sex": "0", "age": 5, "createTime": "2014-09-05 18:54:16", "isAvalible": "1", "picImg": "test_45904720d2ea" }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
4.2. 测试根据用户userId查询分表用户数据
http://localhost:7000/testGetSubTableUser?userId=6- 1
4.3. 测试根据用户userId更新分表用户数据
http://localhost:7000/testUpdateSubTableUser- 1
{ "userId": 6, "mobile": "17610135255", "userName": "90架构师update", "sex": "1", "age": 2, "createTime": "2014-09-05 18:54:16", "isAvalible": "1", "picImg": "test_45904720d2ea" }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
4.4. 测试根据用户userId删除分表用户数据
http://localhost:7000/testDelSubTableUser?userId=6- 1
4.5. 总结归纳
shadingjdbc 原理：通过增删改查sql进行aop拦截，将表名根据算法策略进行替换。
-
相关阅读:
使用Jest测试Cesium源码
第七章第二节：B树和B+树
AI应用新时代的起点，亚马逊云科技加速大模型应用
Linux第一个小程序进度条
华为od德科面试数据算法解析 2022-6-27 字符串片段反转
基于Expression Lambda表达式树的通用复杂动态查询构建器——《构思篇一》[已开源]
JavaScript算法33- 兼具大小写的最好英文字母（leetCode:2309简单）
Mac 安装TA-Lib错误及其解决办法
在k8s中部署高可用程序实践和资源治理
MySQL进阶实战6，缓存表、视图、计数器表
- 原文地址：https://blog.csdn.net/weixin_40816738/article/details/126334072
