MySQL表名带日期的自动建表

有时候我们需要按日期批量建表，下面列出两种方法实现

方法一、使用存储过程，稍微改动可粘贴到sql查询器中直接执行

DROP PROCEDURE IF EXISTS `create_tables`;
DELIMITER $

CREATE PROCEDURE create_tables (IN `bg` DATE, IN `ed` DATE)
BEGIN

SET @cur= bg;

WHILE @cur <= ed DO   
   
SET @createSql = CONCAT(
"CREATE TABLE IF NOT EXISTS log_", DATE_FORMAT(@cur, '%Y%m%d'),
    "(
          `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `content` VARCHAR(1024) NULL DEFAULT '',
          `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
   ");

PREPARE stmt FROM @createSql;
EXECUTE stmt;
SET @cur= DATE_ADD(@cur, INTERVAL 1 DAY);
END WHILE;

END $
DELIMITER ;  
CALL `create_tables`('2022-01-01', '2022-01-02');
DROP PROCEDURE IF EXISTS `create_tables`;

方法二、使用script脚本批量生成建表语句，然后存成sql脚本再执行

将下面脚本保存成.htm页面文件，双击启动就是一个脚本生成工具

<textarea id="data1">

CREATE TABLE `log_{date}` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `title` VARCHAR(64) NULL DEFAULT '' COMMENT '标题' COLLATE 'utf8_general_ci',
    `content` VARCHAR(4000) NULL DEFAULT '' COMMENT '内容' COLLATE 'utf8_general_ci',
    `atime` DATETIME NULL DEFAULT NULL COMMENT '时间',
    PRIMARY KEY (`id`) USING BTREE
)
COMMENT='日志表{date}'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

</textarea>
<div style="padding:10px 0;">
<input id="date1" value="2023-01-01"> -
<input id="date2" value="2023-01-03">
<input type="button" value="批量建表" onclick="Page.run()">
</div>
<textarea id="data2"></textarea>

<style>
*{margin:0;padding:0;font-size:12px;}
body{margin:10px;}
textarea{width:90%;height:200px;padding:5px;}
</style>

<script>
Page = {
    run: function () {
        var tpl=data1.value.trim(),s='';
        var t1=date1.value;
        var t2=date2.value;
        while(t1<=t2){
            s+=tpl.replaceAll('{date}',t1.replaceAll('-',''))+'\n\n';
            t1=this.formatTime(t1,10,3600*24);
        }
        data2.value=s;
    },
    formatTime: function (time, len, seconds) {
        if (!time) time = new Date().getTime() / 1000;
        if (typeof time == 'string') time = new Date(time.replace(/-/g, '/')).getTime() / 1000;
        var t = new Date((parseFloat(time) + (seconds || 0)) * 1000);
        var m = t.getMonth() + 1, d = t.getDate(), H = t.getHours(), M = t.getMinutes(), S = t.getSeconds();
        if (m < 10) m = '0' + m;
        if (d < 10) d = '0' + d;
        if (H < 10) H = '0' + H;
        if (M < 10) M = '0' + M;
        if (S < 10) S = '0' + S;
        t = [t.getFullYear(), '-', m, '-', d, ' ', H, ':', M, ':', S].join('');
        if (len) t = t.substr(0, len);
        return t;
    }
}
Page.run();
</script>

附带数据导入方法

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl

-> FIELDS TERMINATED BY ':'

-> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt'

-> INTO TABLE mytbl (b, c, a);

详细请参考https://www.runoob.com/mysql/mysql-database-import.html