同库分表，自动创建衍生表，无框架，手写功能-优快云博客

本文链接：https://blog.youkuaiyun.com/devotemyself/article/details/130411761

因为数据量庞大，某个沙雕同事写的代码，所有增删改”查“全都加了日志，导致服务器不够用，领导又不敢得罪人家，只能让我填坑，写了此功能，主要是接收数据然后保存起来，一周创建一个新表

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fy.log.mapper.BusinessFyLogMapper">

    <resultMap id="BaseResultMap" type="com.fy.log.domain.BusinessFyLog">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="type" jdbcType="CHAR" property="type"/>
        <result column="business_info" jdbcType="VARCHAR" property="businessInfo"/>
        <result column="create_by" jdbcType="VARCHAR" property="createBy"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_by" jdbcType="VARCHAR" property="updateBy"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="remark" jdbcType="VARCHAR" property="remark"/>
    </resultMap>
    <insert id="insert" parameterType="com.fy.log.domain.BusinessFyLog">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Tue Apr 25 09:04:10 UTC 2023.
        -->
        insert into business_fy_log (`id`, `type`, `create_by`,
        `create_time`, `update_by`, `update_time`,
        `remark`, `business_info`)
        values (#{id,jdbcType=INTEGER}, #{type,jdbcType=CHAR}, #{createBy,jdbcType=VARCHAR},
        #{createTime,jdbcType=TIMESTAMP}, #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP},
        #{remark,jdbcType=VARCHAR}, #{businessInfo,jdbcType=LONGVARCHAR})
    </insert>
    <insert id="insertSelective" parameterType="com.fy.log.domain.BusinessFyLog">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Tue Apr 25 09:04:10 UTC 2023.
        -->
        insert into `${searchValue}`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                `id`,
            </if>
            <if test="type != null">
                `type`,
            </if>
            <if test="createBy != null">
                `create_by`,
            </if>
            <if test="createTime != null">
                `create_time`,
            </if>
            <if test="updateBy != null">
                `update_by`,
            </if>
            <if test="updateTime != null">
                `update_time`,
            </if>
            <if test="remark != null">
                `remark`,
            </if>
            <if test="businessInfo != null">
                `business_info`,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="type != null">
                #{type,jdbcType=CHAR},
            </if>
            <if test="createBy != null">
                #{createBy,jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                #{createTime,jdbcType=TIMESTAMP},
            </if>
            <if test="updateBy != null">
                #{updateBy,jdbcType=VARCHAR},
            </if>
            <if test="updateTime != null">
                #{updateTime,jdbcType=TIMESTAMP},
            </if>
            <if test="remark != null">
                #{remark,jdbcType=VARCHAR},
            </if>
            <if test="businessInfo != null">
                #{businessInfo,jdbcType=LONGVARCHAR},
            </if>
        </trim>
    </insert>
    <select id="selectList" resultMap="BaseResultMap">
        <foreach collection="params.tableNames" close=")" item="tableName" open="(" separator="union all">
            select * from ${tableName}
            <where>
                <if test="type != null and type != ''">
                    and type like concat(#{type},'%')
                </if>
                <if test="businessInfo != null and businessInfo != ''">
                    and businessInfo like concat('%',#{businessInfo},'%')
                </if>
                <if test="params != null and params.size >0">
                    <if test="params.beginTime != null">
                        and #{params.beginTime} &lt;= create_time
                    </if>
                    <if test="params.endTime != null">
                        and create_time &lt;= #{params.endTime}
                    </if>
                </if>
            </where>
        </foreach>
        order by create_time
    </select>
    <select id="getTableName" resultType="java.lang.String">
        SELECT table_name
        FROM information_schema.TABLES
        WHERE table_name LIKE concat('%', #{1}, '%')
        ORDER BY create_time desc
    </select>
    <insert id="createTable">
        CREATE TABLE IF NOT EXISTS `${tableName}` (
            `id` INT ( 0 ) NOT NULL AUTO_INCREMENT,
            `type` CHAR ( 10 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
            `business_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
            `create_by` VARCHAR ( 20 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
            `create_time` datetime ( 0 ) NULL DEFAULT NULL,
            `update_by` VARCHAR ( 20 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
            `update_time` datetime ( 0 ) NULL DEFAULT NULL,
            `remark` VARCHAR ( 255 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
            PRIMARY KEY ( `id` ) USING BTREE
            ) ENGINE = INNODB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
    </insert>
</mapper>

package com.fy.log.mapper;

import com.fy.log.domain.BusinessFyLog;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @ClassName BusinessFyLogMapper
 * @author: maliang
 * @Description TODO
 * @date 2023/4/25 16:51
 * @Version 1.0版本
 */
public interface BusinessFyLogMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table business_fy_log
     *
     * @mbg.generated Tue Apr 25 09:04:10 UTC 2023
     */
    int insert(BusinessFyLog record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table business_fy_log
     *
     * @mbg.generated Tue Apr 25 09:04:10 UTC 2023
     */
    int insertSelective(BusinessFyLog record);

    List<BusinessFyLog> selectList(BusinessFyLog businessFyLog);

    void createTable(@Param("tableName") String tableName);

    List<String> getTableName(String s);
}

public interface BusinessFyLogService {

    void add(BusinessFyLog businessFyLog);

    List<BusinessFyLog> page(Integer pageNum, Integer pageSize, BusinessFyLog businessFyLog);

    List<String> getTableName(String table);

    void createTable(String table);
}

@Service
public class BusinessFyLogServiceImpl implements BusinessFyLogService {

    @Autowired
    private BusinessFyLogMapper businessFyLogMapper;

    @Override
    public void add(BusinessFyLog businessFyLog) {
        if (Objects.isNull(businessFyLog) || StringUtils.isBlank(businessFyLog.getBusinessInfo())) {
            return;
        }
        String businessSql = businessFyLog.getBusinessInfo();
        String type;
        if (StringUtils.containsIgnoreCase(businessSql, "insert")) {
            type = "新增";
        } else if (StringUtils.containsIgnoreCase(businessSql, "update")) {
            type = "更新";
        } else if (StringUtils.containsIgnoreCase(businessSql, "delete")) {
            type = "删除";
        } else if (StringUtils.containsIgnoreCase(businessSql, "select")) {
            type = "查询";
        } else {
            type = "其他";
        }
        businessFyLog.setCreateBy("channel");
        businessFyLog.setCreateTime(new Date());
        businessFyLog.setType(type);
        if (StrUtil.isBlank(StringUtils.lastTableName)) {
            List<String> tableNamesList = getTableName("business_fy_log");
            StringUtils.lastTableName = tableNamesList.get(0);
        }
        businessFyLog.setSearchValue(StringUtils.lastTableName);
        businessFyLogMapper.insertSelective(businessFyLog);
    }

    @Override
    public List<BusinessFyLog> page(Integer pageNum, Integer pageSize, BusinessFyLog businessFyLog) {
        if (CollUtil.isEmpty(StringUtils.tableNameList)) {
            StringUtils.tableNameList.addAll(getTableName("business_fy_log"));
        }
        businessFyLog.getParams().put("tableNames", StringUtils.tableNameList);
        PageHelper.startPage(pageNum, pageSize);
        return businessFyLogMapper.selectList(businessFyLog);
    }

    /**
     * 查询表名所有衍生表
     *
     * @param table 表名
     * @return 衍生表
     */
    public List<String> getTableName(String table) {
        // 查询表
        List<String> tableNameList = businessFyLogMapper.getTableName(table);
        // 如果不存在表，就建一个
        if (CollUtil.isEmpty(tableNameList)) {
            table = table + "_1";
            businessFyLogMapper.createTable(table);
            return Collections.singletonList(table);
        } else {
            // 有的话就用现成的
            return tableNameList;
        }
    }

    @Override
    public void createTable(String table) {
        businessFyLogMapper.createTable(table);
    }

}

package com.fy.log.controller;

import com.fy.log.domain.BusinessFyLog;
import com.fy.log.domain.vo.TableDataInfo;
import com.fy.log.service.BusinessFyLogService;
import com.fy.log.utils.AjaxResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * @ClassName BusinessFyLogController
 * @author: maliang
 * @Description 日志控制器
 * @date 2023/4/25 16:40
 * @Version 1.0版本
 */
@RestController
@RequestMapping("fyLog")
public class BusinessFyLogController {

    @Autowired
    private BusinessFyLogService businessFyLogService;

    @PostMapping("add")
    public AjaxResult add(@RequestBody BusinessFyLog businessFyLog) {
        businessFyLogService.add(businessFyLog);
        return AjaxResult.success();
    }

    @GetMapping("page")
    public TableDataInfo page(BusinessFyLog businessFyLog, @RequestParam(value = "page", defaultValue = "1") Integer pageNum, @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize) {
        return AjaxResult.getDataTable(businessFyLogService.page(pageNum, pageSize, businessFyLog));
    }

}

@Component
public class AutoTask {

    @Autowired
    private BusinessFyLogService businessFyLogService;

    /**
     * 自动创建衍生表
     */
    @Scheduled(cron = "0 0 1 * * ?")
//    @Scheduled(cron = "*/30 * * * * *")
    public void createTable() {
        int dayOfWeek = LocalDateTime.now().getDayOfWeek().getValue();
        // 每周一生成新的表
        if (dayOfWeek == 1) {
            // 已经使用的表名
            String table = "business_fy_log";
            List<String> tableNameList = businessFyLogService.getTableName(table);
            String nowTableName = tableNameList.get(0);
            String sub = StrUtil.sub(nowTableName, nowTableName.lastIndexOf("_") + 1, nowTableName.length());
            // 如果最新一张表是数字结尾，就加一，不然就生成数字结尾的表
            if (StrUtil.isNumeric(sub)) {
                Long num = NumberUtil.parseLong(sub) + 1;
                table = table + "_" + num;
            } else {
                table = table + "_1";
            }
            businessFyLogService.createTable(table);
            try {
                TimeUnit.SECONDS.sleep(2);
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
            StringUtils.lastTableName = table;
            StringUtils.tableNameList.addAll(tableNameList);
            StringUtils.tableNameList.add(table);
        }
    }

}

@SpringBootApplication
@MapperScan("com.fy.log.mapper")
@EnableScheduling
public class FyLogApiApplication {
    public static void main(String[] args) {
        SpringApplication.run(FyLogApiApplication.class, args);
    }
}

public class BusinessFyLog extends BaseEntity {

    private Long id;

    private String type;

    private String businessInfo;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getBusinessInfo() {
        return businessInfo;
    }

    public void setBusinessInfo(String businessInfo) {
        this.businessInfo = businessInfo;
    }
}

@Data
public class BaseEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 搜索值
     */
    @JsonIgnore
    private String searchValue;
    /**
     * 创建者
     */
    private String createBy;

    /**
     * 创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    /**
     * 更新者
     */
    private String updateBy;

    /**
     * 更新时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updateTime;

    /**
     * 备注
     */
    private String remark;

    /**
     * 请求参数
     */
    @JsonInclude(JsonInclude.Include.NON_EMPTY)
    private Map<String, Object> params = new HashMap<>();
}