MYSQL将旧数据创建至新表并删除旧数据存储过程

博主：茶白
发布时间：2023 年 07 月 03 日
暂无评论
1073字数
分类： mysql

> 业务场景&需求

我现在有一个表 `order_list` ,因为每天新增订单已经是千万级别,且技术栈复杂,并非单纯存放在mysql redis es中所以可以删除历史订单, 该表中有一个bigint字段 `creator_time` 存放的10位时间戳
我需要根据这个时间戳将24小时前的订单创建一个新的表 `bak_order_list_20230702024752` 存放进去 并删除旧数据

> 理解并编写代码

在这个场景下，可以使用一下的SQL命令，用于创建一个新的表，然后将24小时前的订单复制过去，再从原始表中删除。

这里有一个要注意的地方：在实际操作中，删除操作是相当敏感的，建议在进行任何删除操作之前，都要对数据进行备份，避免数据丢失的风险。如果是生产环境下的操作，更建议你先在测试环境进行验证，确认无误后再操作。

首先，我们创建一个新的表：`bak_app_order`，并将24小时前的订单复制到新表中。假设你的时间戳是以秒为单位的，24小时就是86400秒。

```sql
CREATE TABLE bak_creator_time AS 
SELECT * 
FROM order_list
WHERE creator_time <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

```

然后，我们从原始表`order_list`中删除24小时前的订单：

```sql
DELETE FROM order_list
WHERE creator_time <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

```

如果你的时间戳是以毫秒为单位的，24小时就是86400000毫秒，你需要进行相应的调整。

> 创建存储过程

> 因为可能涉及多个业务表等 / 为了复用, 所以将存储过程参数化

```sql
DELIMITER //

CREATE PROCEDURE archive_and_delete_data(IN table_name CHAR(64), IN column_name CHAR(64), IN hours_ago INT)
BEGIN
    DECLARE bak_table_name CHAR(64);
    DECLARE sql_stmt VARCHAR(1024);

SET @date_suffix = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL hours_ago HOUR), '%Y%m%d%H%i%s');
    SET bak_table_name = CONCAT('bak_', table_name, '_', @date_suffix);

SET @sql_stmt = CONCAT('SELECT COUNT(*) INTO @cnt FROM ', table_name, ' WHERE ', column_name, ' <= UNIX_TIMESTAMP(NOW() - INTERVAL ', hours_ago, ' HOUR)');
    PREPARE stmt1 FROM @sql_stmt;
    EXECUTE stmt1;

IF @cnt > 0 THEN
        SET @sql_stmt = CONCAT('CREATE TABLE ', bak_table_name, ' SELECT * FROM ', table_name, ' WHERE ', column_name, ' <= UNIX_TIMESTAMP(NOW() - INTERVAL ', hours_ago, ' HOUR)');
        PREPARE stmt2 FROM @sql_stmt;
        EXECUTE stmt2;

SET @sql_stmt = CONCAT('DELETE FROM ', table_name, ' WHERE ', column_name, ' <= UNIX_TIMESTAMP(NOW() - INTERVAL ', hours_ago, ' HOUR)');
        PREPARE stmt3 FROM @sql_stmt;
        EXECUTE stmt3;
    END IF;

END //

DELIMITER ;

```

然后，你可以为`order_list`表调用这个存储过程：

```sql
CALL archive_and_delete_data('order_list', 'creator_time', 24);
```

> 自动操作

这个时候就可以使用各种定时任务的技术栈或mysql自带的定时任务(Event)进行处理了

下面演示一个mysql的定时任务

> 下面是如何创建一个每天凌晨自动执行存储过程的示例：

```sql
CREATE EVENT archive_and_delete_data_event
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY)
DO
CALL archive_and_delete_data('order_list', 'creator_time', 24);

```

> 记得在创建事件前要确保你的 MySQL 服务器已启用事件调度器，可以通过以下语句启用：

```sql
SET GLOBAL event_scheduler = ON;

```

> 可以通过运行以下的 SQL 语句来查看 MySQL 服务器的事件调度器的状态：

```sql
SHOW VARIABLES LIKE 'event_scheduler';

```

查询将返回 `event_scheduler` 的当前设置。如果事件调度器已经启用，返回的值将是 `ON`。如果事件调度器没有启用，返回的值将是 `OFF` 或 `DISABLED`。

如果你看到 `OFF` 或 `DISABLED`，那么你需要运行刚才那个set来启用事件调度器

> 请注意，你需要具有 `SUPER` 权限才能设置这个变量。并且，如果你希望在 MySQL 服务器重启后这个设置仍然有效，你需要在你的 MySQL 配置文件（通常是 `my.cnf` 或 `my.ini`）中将 `event_scheduler` 设置为 `ON`。

本文内容由ChatGPT虚构，但技术内容真实有效

最后修改：2023 年 08 月 13 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址 *

姓名
GPT-4 currently has a cap of 25...
曲阜
有没有兴趣来我们公司工作？可联系微信geezzi
Rorinl
谢谢ヾ(≧∇≦*)ゝ
rorinl
该评论仅登录用户及评论双方可见

MYSQL将旧数据创建至新表并删除旧数据存储过程

茶白 • 2023 年 07 月 03 日

> 业务场景&需求

> 理解并编写代码

在这个场景下，可以使用一下的SQL命令，用于创建一个新的表，然后将24小时前的订单复制过去，再从原始表中删除。

首先，我们创建一个新的表：`bak_app_order`，并将24小时前的订单复制到新表中。假设你的时间戳是以秒为单位的，24小时就是86400秒。

```sql
CREATE TABLE bak_creator_time AS 
SELECT * 
FROM order_list
WHERE creator_time <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

```

然后，我们从原始表`order_list`中删除24小时前的订单：

```sql
DELETE FROM order_list
WHERE creator_time <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);

```

如果你的时间戳是以毫秒为单位的，24小时就是86400000毫秒，你需要进行相应的调整。

> 创建存储过程

> 因为可能涉及多个业务表等 / 为了复用, 所以将存储过程参数化

```sql
DELIMITER //

CREATE PROCEDURE archive_and_delete_data(IN table_name CHAR(64), IN column_name CHAR(64), IN hours_ago INT)
BEGIN
    DECLARE bak_table_name CHAR(64);
    DECLARE sql_stmt VARCHAR(1024);

SET @date_suffix = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL hours_ago HOUR), '%Y%m%d%H%i%s');
    SET bak_table_name = CONCAT('bak_', table_name, '_', @date_suffix);

END //

DELIMITER ;

```

然后，你可以为`order_list`表调用这个存储过程：

```sql
CALL archive_and_delete_data('order_list', 'creator_time', 24);
```

> 自动操作

这个时候就可以使用各种定时任务的技术栈或mysql自带的定时任务(Event)进行处理了

下面演示一个mysql的定时任务

> 下面是如何创建一个每天凌晨自动执行存储过程的示例：

```sql
CREATE EVENT archive_and_delete_data_event
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY)
DO
CALL archive_and_delete_data('order_list', 'creator_time', 24);

```

> 记得在创建事件前要确保你的 MySQL 服务器已启用事件调度器，可以通过以下语句启用：

```sql
SET GLOBAL event_scheduler = ON;

```

> 可以通过运行以下的 SQL 语句来查看 MySQL 服务器的事件调度器的状态：

```sql
SHOW VARIABLES LIKE 'event_scheduler';

```

查询将返回 `event_scheduler` 的当前设置。如果事件调度器已经启用，返回的值将是 `ON`。如果事件调度器没有启用，返回的值将是 `OFF` 或 `DISABLED`。

如果你看到 `OFF` 或 `DISABLED`，那么你需要运行刚才那个set来启用事件调度器

本文内容由ChatGPT虚构，但技术内容真实有效

MYSQL将旧数据创建至新表并删除旧数据存储过程

发表评论取消回复

在线影视视频网站合集

js奇淫技巧（一）

常见的app scheme大全

uniapp图片未加载成功前处理方案,防止闪烁

浏览器console引入jq

c#插值变量

HTML canvas font 属性

idea java一键生成实体类set方法填充非 getter/setter

判断一个字符串是否为回文_一行代码

crossfire 5.24最新游戏基址

MYSQL将旧数据创建至新表并删除旧数据存储过程

发表评论 取消回复

MYSQL将旧数据创建至新表并删除旧数据存储过程

发表评论取消回复