如何在DBeaver中自动更新MySQL表以保留最近24个月数据

1. 先写好核心的SQL逻辑 #

先把「判断当月1日、新增数据、删除最早行」的SQL代码写对，这是基础。

（1）判断当前是否为当月1日 #

不同数据库的日期函数略有差异，举几个常用的：

• MySQL：DAY(CURDATE()) = 1
• PostgreSQL：EXTRACT(DAY FROM CURRENT_DATE) = 1
• SQL Server：DAY(GETDATE()) = 1

（2）插入当月1日的数据 #

替换成你的表名和日期字段名：

• MySQL：INSERT INTO 你的表名(日期字段名) VALUES(DATE_FORMAT(CURDATE(), '%Y-%m-01'));
• PostgreSQL：INSERT INTO 你的表名(日期字段名) VALUES(DATE_TRUNC('month', CURRENT_DATE));
• SQL Server：INSERT INTO 你的表名(日期字段名) VALUES(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0));

（3）删除最早的一行数据 #

确保删除后表中始终保留最近24个月的数据：

• MySQL：DELETE FROM 你的表名 ORDER BY 日期字段名 ASC LIMIT 1;
• PostgreSQL：DELETE FROM 你的表名 WHERE 日期字段名 = (SELECT MIN(日期字段名) FROM 你的表名);
• SQL Server：DELETE TOP(1) FROM 你的表名 ORDER BY 日期字段名 ASC;

（4）整合条件判断 #

把上面的逻辑用条件语句包起来，只有当月1日才执行：
以MySQL为例，写出来是这样：

IF DAY(CURDATE()) = 1 THEN
    INSERT INTO 你的表名(日期字段名) VALUES(DATE_FORMAT(CURDATE(), '%Y-%m-01'));
    DELETE FROM 你的表名 ORDER BY 日期字段名 ASC LIMIT 1;
END IF;

2. 实现每日自动执行 #

DBeaver本身没有内置定时调度功能，推荐两种方案：

方案一：用数据库自带的定时事件（推荐） #

大部分数据库都支持定时事件，直接在数据库层面配置，不需要额外工具。以MySQL为例：

1. 开启事件调度器
   执行这条SQL，确保调度器处于开启状态：

   SET GLOBAL event_scheduler = ON;
   

   可以用SHOW VARIABLES LIKE 'event_scheduler';查看状态，确认结果是ON。

2. 创建定时事件
   在DBeaver的SQL编辑器里执行以下代码（替换你的表名和字段名）：

   CREATE EVENT monthly_table_update
   ON SCHEDULE EVERY 1 DAY
   STARTS '2023-01-01 00:00:00' -- 设置每天凌晨0点开始执行
   DO
   BEGIN
       IF DAY(CURDATE()) = 1 THEN
           INSERT INTO 你的表名(日期字段名) VALUES(DATE_FORMAT(CURDATE(), '%Y-%m-01'));
           DELETE FROM 你的表名 ORDER BY 日期字段名 ASC LIMIT 1;
       END IF;
   END;
   

   这个事件会每天自动运行一次，只有当月1日时才执行增删操作。

3. 管理事件
   在DBeaver的数据库导航栏里，展开对应数据库的Events节点，就能看到你创建的事件，右键可以编辑、禁用或删除。

方案二：DBeaver脚本+系统定时任务（适合无数据库权限的情况） #

如果没有权限创建数据库事件，可以用系统定时任务触发DBeaver运行脚本：

1. 编写DBeaver脚本
   打开DBeaver，点击File->New->Script，把之前写好的条件SQL粘贴进去，保存为update_table.sql。

2. 配置系统定时任务

   • Windows：打开「任务计划程序」，创建基本任务，设置每天凌晨0点触发，操作选择启动DBeaver的dbeaver.exe，添加参数：-con "你的数据库连接名" -sql "C:\你的脚本路径\update_table.sql"
   • Linux/macOS：打开终端执行crontab -e，添加一行：0 0 * * * /你的DBeaver路径/dbeaver -con "你的数据库连接名" -sql "/你的脚本路径/update_table.sql"，保存后生效。

3. 测试验证 #

• 方案一：在DBeaver里右键创建的事件，选择Execute Event，手动触发一次，检查表数据是否正确增删。
• 方案二：可以临时把SQL里的日期判断改成DAY(CURDATE()) = DAY(CURDATE())（强制执行），手动运行脚本测试，没问题后改回原判断。

注意事项 #

• 一定要替换代码中的你的表名和日期字段名为实际名称。
• 根据你使用的数据库（PostgreSQL/SQL Server等），调整对应的日期函数和删除语句语法。
• 确保操作数据库的账号有插入、删除数据的权限，方案一还需要创建事件的权限。
• 如果表中现有数据超过24条，第一次执行前建议手动清理到24条，之后自动维护即可。

内容的提问来源于stack exchange，提问作者KazimiR