MySQL学习笔记：生成时间维度表2

# 测试 加一秒
SELECT DATE_SUB(DATE_ADD(CURRENT_DATE(), INTERVAL 1 SECOND), INTERVAL 1 SECOND);
SELECT DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 SECOND),‘%H%i%s‘);# 第一秒
SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 SECOND),‘%H%i%s‘);# 最后一秒
SELECT CURRENT_TIME();

# ------------ 完整代码 ----------
SET @d0 = DATE_SUB(DATE_ADD(CURRENT_DATE(), INTERVAL 1 SECOND), INTERVAL 1 SECOND); # 第一秒
SET @d1 = DATE_ADD(DATE_SUB(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 1 SECOND),
    INTERVAL 1 SECOND);# 最后一秒
SET @time = DATE_SUB(@d0, INTERVAL 1 SECOND);

SELECT @d0, @d1, @time;

USE test;

# 建表
CREATE TABLE aa_time_test(
    time2 TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00‘,
    id VARCHAR(11) NOT NULL
)ENGINE = INNODB DEFAULT CHARSET = utf8;

# 插入数据
INSERT INTO aa_time_test
SELECT
    @time := DATE_ADD(@time, INTERVAL 1 SECOND) AS time2,
    DATE_FORMAT(@time, ‘%H%i%s‘) AS id
FROM 
    table_xxx # 只需要xxx表数据量够多即可
WHERE DATE_ADD(@time, INTERVAL 1 SECOND ) <= @d1
ORDER BY time2;

# 查询
SELECT * FROM test.aa_time_test;
SELECT COUNT(*) FROM aa_time_test; # 86400
DELETE FROM test.`aa_time_test` WHERE id_5 IS NOT NULL;

# 新增辅助列
CREATE TABLE table_xxx AS 
SELECT time2, id, LPAD((FLOOR(id/5)+1)*5,6,0) AS id_5 FROM test.`aa_time_test`;