mysql 垂直分表实战 | sharding-jdbc | 详细步骤

垂直分表

• 项目源码
• ShardingSphere 5.1.1 读写分离实战 | sharding-jdbc | 详细步骤
• mysql 垂直分表实战 | sharding-jdbc | 详细步骤
• mysql 垂直分库 | sharding-jdbc 实战详细步骤
• mysql 水平分表（不分库）| shardingsphere 实战详细步骤
• mysql 水平分表（分库）| sharding-jdbc实战详细步骤
• 多表关联 shardingsphere 实战详细步骤
• 广播表 shardingsphere实战详细步骤

1 垂直分表

垂直分表是将表的一部分列作为单独的一张表，另一部分列作为另一张表，两张表都要id，且一一对应。因为拆分后的表要join才能获取到全部数据，最好放在同一个数据库里。若放在两个数据库，跨库join操作数据库不支持，就只能由应用程序来完成，效率要低很多。因此此示例为同库下的垂直分表。其实垂直分表后的两张表我们完全可以当作普通的两张不同的表来对待。

1.1User表垂直拆分

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

 

-- ----------------------------

-- Table structure for user_0

-- ----------------------------

DROP TABLE IF EXISTS `user_0`;

CREATE TABLE `user_0` (

 `id` int NOT NULL AUTO_INCREMENT,

 `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 `sex` tinyint NULL DEFAULT NULL,

 `nick_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

 

-- ----------------------------

-- Table structure for user_1

-- ----------------------------

DROP TABLE IF EXISTS `user_1`;

CREATE TABLE `user_1` (

 `id` int NOT NULL,

 `province` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 `school` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

 

SET FOREIGN_KEY_CHECKS = 1;

## 

1.2创建实体类

@Data
 public class User {
   private Integer id;
   private String username;
   private String nickName;
   private Integer sex;
   private String school;
   private String province;
 }

@TableName("user_0")
 @Data
 public class User0 {
   @TableId(type = IdType.*AUTO*)
   private Integer id;
   private String username;
   private String nickName;
   private Integer sex;
 }

@TableName("user_1")
 @Data
 public class User1 {
   @TableId(type = IdType.*INPUT*)
   private Integer id;
   private String school;
   private String province;
 }

1.3创建mapper

public interface User0Mapper extends BaseMapper<User0> {
 }

 

public interface User1Mapper extends BaseMapper<User1> {
 }

1.4配置文件

# 运行模式
 spring.shardingsphere.mode.type=Memory
 
 # 配置真实数据源
 spring.shardingsphere.datasource.names=master
 
 # 配置第 1 个数据源
 spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
 spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
 spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://mysql3:3306/mytest
 spring.shardingsphere.datasource.master.username=root
 spring.shardingsphere.datasource.master.password=密码
 
 # 打印SQl
 spring.shardingsphere.props.sql-show=true

1.5测试

@SpringBootTest
 @Slf4j
 public class UserMapperTest {

   @Autowired
   private User0Mapper user0Mapper;
   @Autowired
   private User1Mapper user1Mapper;

   @Test
   public void testInert() {
     User user = new User();
     user.setUsername("zhangsan");
     user.setSex(1);
     user.setNickName("大牛");
     user.setSchool("自学大学");
     user.setProvince("广府省");

     User0 user0 = new User0();
     BeanUtils.*copyProperties*(user, user0);
     user0Mapper.insert(user0);

     User1 user1 = new User1();
     BeanUtils.*copyProperties*(user, user1);
     if (user0.getId() == null) {
       *log*.error("user0.getId() == null");
       return;
     }
     user1.setId(user0.getId());
     user1Mapper.insert(user1);

     user.setId(user1.getId());

     User0 user0S = user0Mapper.selectById(user0.getId());
     User1 user1S = user1Mapper.selectById(user1.getId());
     User userS = new User();
     BeanUtils.*copyProperties*(user0S, userS);
     BeanUtils.*copyProperties*(user1S, userS);
     Assertions.*assertEquals*(user, userS);
   }


 }