MyBatis 分表实践

迈不过友情╰ 2023-10-07 09:56 23阅读 0赞

#### 文章目录 ####

*  1. 前言
 *  2. 动态创建表
 *   *   *  2.1 查询数据库是否存在目标表
         *  2.2 动态创建表
         *  2.3 定时创建表
 *  3. 数据插入
 *   *   *  3.1 单条数据插入
         *  3.2 批量插入
         *   *  注意
         *  3.3 数据查询

## 1. 前言 ##

项目开发接到需求，要求将业务数据按月归档，也就是每个月的数据单独保留在一张表中，每个月都要生成新表。以前从没有遇到过这样的场景，快速思考实现方案，主要的难点如下：

> 1.  项目使用 `MyBatis` 框架，`ORM` 的思想是一个 bean 映射一张表，如何实现一个 bean 对象映射多张结构相同而名称不同的表？
> 2.  每月生成新表，如何知道数据库是否已经存在当月表，不存在时如何创建新表？

幸亏 `MyBatis` 这个轻量级的 `ORM` 框架为手写 SQL 语句留下了余地，否则只能把活交给 `DBA` 去琢磨了。我们知道 `MyBatis` 通过 `Mapper` 去操作数据库，并且可以自行手写灵活的 `SQL` 语句，这就给了我们极大的便利

## 2. 动态创建表 ##

#### 2.1 查询数据库是否存在目标表 ####

基于 `Mapper` 定义接口方法，在方法上添加注解`@Select`自行写好 SQL 语句。这条语句将`数据库名称`和`表名称`作为入参，从数据库本身保存的表信息中统计目标数据库中目标表的数量，通过其返回值可判断目标表是否存在

@Repository
    public interface ActionEventMapper extends BaseMapper<ActionEvent> {
        
    
        @Select("SELECT count(*) FROM information_schema.`tables` WHERE TABLE_SCHEMA = #{dbName} " +
                "AND TABLE_NAME = #{tableName}")
        int countTable(@Param("tableName") String tableName, @Param("dbName") String dbName);
    }

#### 2.2 动态创建表 ####

同样的，将手写的 SQL 语句映射到接口方法上，将表名作为参数传入，完成目标表的动态创建。需注意 MySql 中对表名的格式有要求，连接符必须使用下划线`_`，否则会有语法错误。另外SQL 语句中表名使用 $\{\} 直接拼接，而不是使用 \#\{\} 占位符

@Repository
    public interface ActionEventMapper extends BaseMapper<ActionEvent> {
        
    
        @Update("CREATE TABLE IF NOT EXISTS ${tableName}(" +
                " `FuiId` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键'," +
                " `FuiEventType` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '事件类型'," +
                " `FuiMicroSeconds` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '微秒'," +
                " `FuiCreateTime` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间'," +
                " `FuiUpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'," +
                " `FuiCasVersion` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'cas'," +
                " PRIMARY KEY (`FuiId`)" +
                ")ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8 COMMENT='按月归档表';")
        @CacheEvict(value = "action_event_cache", key = "1")
        void createNewTable(@Param("tableName") String tableName);

#### 2.3 定时创建表 ####

使用 Spring 框架自带的定时任务注解`@Scheduled`创建 `cron` 任务，定时创建表

@Slf4j
    @Component
    public class CreateTableJob {
        
    
        @Autowired
        private ActionEventRepository actionEventRepository;
    
        /**
         * 每月 28 号 00：00：00 创建下月的表
         */
        @Scheduled(cron = "0 0 0 28 1-12 ?")
        public void createTable() {
        
            String tableName = actionEventTableUtil.getNextMonthTableName();
            log.warn("It is time to create next month table:{}", tableName);
            try {
        
                actionEventRepository.createTable(tableName);
            } catch (Exception e) {
        
                log.warn("Cron job create next month table:" + tableName + "fail!", e);
            }
        }
    
    }

## 3. 数据插入 ##

#### 3.1 单条数据插入 ####

单条数据的插入非常简单，只需要注意将表名入参，并使用 $\{tableName\} 拼接表名

@Repository
    public interface ActionEventMapper extends BaseMapper<ActionEvent> {
        
    
        @Insert("INSERT INTO ${tableName}(FuiEventType, FuiMicroSeconds, FuiCreateTime, FuiCasVersion) VALUES "
                + "(#{actionEvent.eventType}, #{actionEvent.microSeconds}, #{actionEvent.createTime}, #{actionEvent.casVersion})")
        // @Options 注解将插入表时主键字段 FuiId 生成的值回填到 bean 对象 actionEvent 的 id 属性
        @Options(useGeneratedKeys = true, keyProperty = "actionEvent.id", keyColumn = "FuiId")
        int save(@Param(value = "actionEvent") ActionEvent actionEvent, @Param("tableName") String tableName);

#### 3.2 批量插入 ####

多条数据的批量插入相对复杂，SQL 语句为类似脚本的形式，注解`@Insert`中不再是一个很长的字符串，而是一个字符串数组

@Repository
    public interface ActionEventMapper extends BaseMapper<ActionEvent> {
        
    
        @Insert({
        "<script>",
                "INSERT INTO ${tableName}(FuiEventType, FuiMicroSeconds, FuiCreateTime, FuiCasVersion) VALUES ",
                "<foreach collection='actionEvents' item='actionEvent' index='index' separator=','>",
                "(#{actionEvent.eventType}, #{actionEvent.microSeconds}, #{actionEvent.createTime}, #{actionEvent.casVersion})",
                "</foreach>",
                "</script>"})
         @Options(useGeneratedKeys = true, keyProperty = "param1.id", keyColumn = "FuiId")
        int saveBatch(@Param(value = "actionEvents") List<ActionEvent> actionEvents,
                      @Param("tableName") String tableName);

##### 注意 #####

使用`@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "FuiId")` 将表生成的主键回填到 bean 对象对应属性的时候需注意，`keyProperty`需要指定为对应入参的对应属性，形式为`参数名.id`。一般报错会有如下信息，表明了可用参数，对于批量插入一般是使用`param1.id`

> `Specified key properties are [id] and available parameters are [actionEvent, param1, tableName, param2]`

#### 3.3 数据查询 ####

查询数据时需要注意，实体 bean 对象映射的表名与实际查询的表会不一致，需要手动使用注解 `@Results` 完成表字段与 bean 属性的映射

/**
         * 实际查询的表名与 {@link ActionEvent} bean 映射的表名不一致，需手动映射
         * */
        @Select("select * from ${tableName} ${ew.customSqlSegment}")
        @Results(value ={
        
                @Result(column = "FuiEventType", property = "eventType"),
                @Result(column = "FuiSeconds", property = "seconds"),
        })
        List<ActionEvent> selectFrom(@Param("tableName") String tableName, @Param(Constants.WRAPPER) LambdaQueryWrapper<ActionEvent> wrapper);