MySql模板创建、批量删除表

MySql模板创建、批量删除表

8 2019-04-09 18:37:03

## 背景
每日需要在MySql中记录一些日志数据，由于数据量较大，单表查询效率肯定受影响，且明确可以按照日期拆分，因此每日建表。
同时由于数据量过大，又忘了定期清理，后期发现磁盘空间不足，需要批量删除表。

## 模板创建表
**思路**
创建一张空表作为模板，之后每天根据这个模板克隆一个新表，后缀为日期。
避免定期任务出现重复建表，使用`CREATE IF NOT EXISTS`。

**语句**
使用Mybatis注解的形式：
```sql
CREATE TABLE IF NOT EXISTS prefix_${day} LIKE prefix

# 也可以使用 'CREATE TABLE AS' 克隆表和数据，但是我们这里不需要数据
# CREATE TABLE prefix_${day} AS SELECT * FROM prefix
```

## 批量删除表
**思路**
删除单表：
```sql
DROP TABLE xxx
```
期望使用`like`语句的形式删除，即"~~DROP TABLE LIKE 'prefix_%'~~"，但是MySql不支持。
查资料发现可以从MySql表信息入手，生成删除语句，即：
- 先通过`LIKE`查到相应的表名
- 通过CONCAT拼接删除语句
- 手动执行删除语句

**语句**
直接在MySql客户端执行：
```sql
# 拼接
SELECT CONCAT( 'DROP TABLE ', table_name, ';' )
# 查找表信息，都存在information_schema.tables中
# SqlServer中的表信息也有保存，具体在哪忘了
FROM information_schema.tables
# 使用LIKE语句
WHERE table_name LIKE 'prefix_%';
```
输出结果：
```
DROP TABLE prefix_20190301;
DROP TABLE prefix_20190302;
DROP TABLE prefix_20190303;
DROP TABLE prefix_20190304;
......
```

## 参考资料
[MySQL：按前缀批量删除表格 - 陈小峰（iefreer）的专栏 - CSDN博客](https://blog.csdn.net/iefreer/article/details/12627389)