求助：如何实现每周日23:59自动重置SQL数据库？

这里提供两种可靠的落地方案，涵盖系统定时任务+PHP脚本、数据库原生事件调度两种方式，你可以根据自身环境选择：

方案一：系统定时任务（Crontab）+ PHP脚本 #

这是跨数据库环境通用的方案，依赖系统定时任务触发PHP脚本执行清理操作。

1. 编写PHP清理脚本 #

新建reset_db.php文件，包含数据库连接和数据删除逻辑，示例代码如下：

<?php
// 替换为你的数据库连接参数
$host = 'localhost';
$dbname = 'your_database';
$username = 'db_user';
$password = 'db_password';

try {
    // 建立PDO连接（推荐用PDO而非mysql扩展）
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 删除本周数据：WEEK函数第二个参数1表示周一为周起始，0则是周日起始，可按需调整
    $sql = "DELETE FROM your_table WHERE WEEK(created_at, 1) = WEEK(CURDATE(), 1)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    // 可选：记录执行日志，方便排查问题
    file_put_contents('/var/log/db_reset.log', date('Y-m-d H:i:s') . " 成功删除 " . $stmt->rowCount() . " 条本周数据\n", FILE_APPEND);
} catch(PDOException $e) {
    // 错误日志记录
    file_put_contents('/var/log/db_reset_error.log', date('Y-m-d H:i:s') . " 错误：" . $e->getMessage() . "\n", FILE_APPEND);
    exit("数据库操作失败：" . $e->getMessage());
}
?>

注意替换代码中的数据库名、账号密码、表名，以及根据你的周起始定义调整WEEK函数的参数。

2. 设置Crontab定时任务 #

在Linux系统中执行crontab -e打开定时任务编辑器，添加以下规则：

59 23 * * 0 /usr/bin/php /path/to/reset_db.php

• 规则解释：59 23表示23点59分，* * 0表示每周日（0代表周日），/usr/bin/php是PHP可执行路径（可通过which php查询），/path/to/reset_db.php是脚本的绝对路径。
• 保存后执行crontab -l可查看已添加的任务。
• 确保脚本有执行权限：chmod +x /path/to/reset_db.php

方案二：MySQL原生事件调度（无需外部脚本） #

如果你的数据库是MySQL，可以直接用自带的事件调度器实现，无需依赖外部脚本或系统定时任务。

1. 开启事件调度器 #

登录MySQL执行命令临时开启：

SET GLOBAL event_scheduler = ON;

为确保MySQL重启后调度器依然生效，在my.cnf（或my.ini）中添加：

event_scheduler=ON

重启MySQL服务后生效。

2. 创建每周清理事件 #

执行以下SQL创建定时事件（替换表名和起始时间）：

CREATE EVENT weekly_reset_data
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-05-26 23:59:00' -- 设置第一个执行的周日23:59时间，替换为最近的周日日期
ON COMPLETION PRESERVE
DO
BEGIN
    -- 删除单表本周数据
    DELETE FROM your_table WHERE WEEK(created_at, 1) = WEEK(CURDATE(), 1);
    -- 多表清理可添加额外DELETE语句
    -- DELETE FROM another_table WHERE WEEK(created_at, 1) = WEEK(CURDATE(), 1);
END;

注意：创建事件需要EVENT权限，可通过GRANT EVENT ON your_database.* TO 'db_user'@'localhost';赋予权限。

关键注意事项 #

• 测试验证：正式部署前，务必先在测试环境验证删除逻辑，避免误删历史数据。
• 数据备份：建议定期备份数据库，防止意外数据丢失。
• 时区一致性：确保系统时区和数据库时区一致，避免时间判断偏差。
• 权限检查：无论是PHP脚本还是数据库事件，都要确保执行账号拥有足够的数据库操作权限。

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