MySQL定时任务event，储存过程（定时删除指定时间前90天指定表的数据）

最新推荐文章于 2023-11-10 09:45:36 发布
原创 最新推荐文章于 2023-11-10 09:45:36 发布 · 5.1k 阅读 · 5 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #定时任务


由于一些业务需求，我们可能需要定时清除数据库一些废弃的数据，可以使用mysql的存储过程和事件来完成。
下面例子定时清除日志表tbl_base_count中指定天数前的数据
1.创建日志表tbl_base_count:
[sql]  view plain
  1. CREATE TABLE `tbl_base_count` (  
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `CAPTCHA` varchar(12) COLLATE utf8_bin NOT NULL,  
  4.   `PHONE` varchar(12) COLLATE utf8_bin NOT NULL,  
  5.   `SENDTIME` varchar(32) COLLATE utf8_bin NOT NULL,  
  6.   PRIMARY KEY (`id`)  
  7. ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  

2.创建事件e_del_tbl_base_count:
[sql]  view plain
  1. CREATE EVENT `e_del_tbl_base_count`   
  2. ON SCHEDULE EVERY 1 DAY STARTS '2013-06-23 17:33:43'   
  3. ON COMPLETION NOT PRESERVE ENABLE DO CALL p_del_count (90);  
上面代码表示从2013-07-30 17:33:43起每一天执行一次p_del_count这个存储过程，并带上参数

3.创建存储过程:
[sql]  view plain
  1. p_del_count   
  2. DELIMITER $$  
  3.   
  4. --  
  5. -- 存储过程  
  6. --  
  7. CREATE PROCEDURE `p_del_count`(IN `date_inter` INT)  
  8. BEGIN  
  9.     DELETE FROM LOG WHERE (TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(SENDTIME)))>=date_inter;  
  10. END$$  
  11. DELIMITER ;  
按事件传过来的参数90，删除操作时间90天之前的数据
这样mysql就会定制每天去执行这个任务了。

4.(一)查看当前是否已开启事件计划（调度器）有3种方法：
[sql]  view plain
  1. SHOW VARIABLES LIKE 'event_scheduler';  
  2.  SELECT @@event_scheduler;  
  3.  SHOW PROCESSLIST;  
5.(二)开启事件计划（调度器）开关有4种方法：
[sql]  view plain
  1. SET GLOBAL event_scheduler = 1;  
  2. SET @@global.event_scheduler = 1;  
  3. SET GLOBAL event_scheduler = ON;  
  4. SET @@global.event_scheduler = ON;  

键值1或者ON表示开启；0或者OFF表示关闭；

6.(三)事件开启与关闭：
[sql]  view plain
  1. 开启某事件：ALTER EVENT e_del_logs ON COMPLETION PRESERVE ENABLE;  
  2. 关闭某事件：ALTER EVENT e_del_logs ON COMPLETION PRESERVE DISABLE; 
数据库删除聊天30天之外的历史记录
lzq520210的博客
07-12 574
表示删除满足时间戳小于指定值的记录，第三个参数是一个字符串数组，用于替换WHERE子句中的占位符。方法用于删除符合条件的记录。第一个参数是表名，第二个参数是WHERE子句，其中。另外可以执行以下逻辑。
mysql时间触发器_mysql 定时触发器
weixin_39902107的博客
01-18 1733
1. mysql可以实现定时触发功能，比如说定于某某时间mysql数据库做什么工作，或每隔多长时间做什么工作。第二种情况应用还是比较广的，比如说我希望每天检查一下我的数据信息，超过一个月的无用信息清除以腾出空间供其他存储数据使用；或者相隔一段时间更新一下数据等等。下面讨论下这种情况，给出一个例子供大家参考：1.首先定义一个存储过程取名为e_test，注意竖线(“|”)一定不能丢DELIMITE...
Mysql定时器清空3天以前的数据.zip
05-20
定时清空数据库中表3天以前的数据的快捷操作,Windows脚本
mysql定时删除过期数据记录的简单方法
01-19
1. 连接登录MySQL后，先查看MySQL是否开启了事件功能： 命令：show variables like ‘%sc%’; 发现event_sheduler是OFF关闭的; 2. 打开event_scheuler: 临时开启(mysql服务重启后之后失效) SET GLOBAL event_scheduler = ON; SET GLOBAL event_scheduler = 1; — 0代表关闭 永久开启 在my.cnf中的[mysqld]部分添加如下内容，然后重启mysql(mysql重启命令：service mysqld restart) event_scheduler=ON
复杂数据处理：MySQL定时任务案例研究与流程构建
[复杂数据处理：MySQL定时任务案例研究与流程构建](https://worktile.com/kb/wp-content/uploads/2022/09/43845.jpg) # 1. MySQL定时任务的基础与必要性 ## 1.1 MySQL定时任务简介 MySQL定时任务是数据库管理员...
时序敏感解决方案：MySQL定时任务调度的策略与技巧
MySQL定时任务基础概述 在现代信息技术领域，自动化的任务调度对于系统运行效率至关重要。MySQL作为最广泛使用的开源数据库管理系统之一，提供了强大的定时任务功能。定时任务，也称为作业调度或计划任务，允许...
MySQL定时任务高可用设计：故障转移的策略与实施
![MySQL定时任务高可用设计：故障转移的策略与实施]...为了保证数据的一致性、可靠性和实时性，MySQL定时任务成为了数据库管理不可或缺的一部
在mysql创建一个分区表，自增主键，字段复杂些，再写个储存过程插入海量数据，按分区日期动态插入
最新发布
11-20
根据用户需求，我们需要创建一个具有自增主键和复杂字段的分区表，并编写一个存储过程，该存储过程能按分区日期动态插入海量数据。同时，根据引用内容，分区字段必须包含在主键中，因此我们需要设计复合主键（包含...
Linux实现定时备份MySQL数据库并删除30天前的备份文件
09-14
主要介绍了Linux实现定时备份MySQL数据库并删除30天前的备份文件,本文通过实例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下
mysql定时删除某个表30天前数据
热门推荐
shmily4992871的博客
03-26 1万+
CREATE PROCEDURE `prc_del_taonlinelog_log`(IN date_inter int) COMMENT '自动删除日志'BEGIN delete from user where (TO_DAYS(NOW()) - TO_DAYS(createtime))>=date_inter;  END;CREATE  EVENT `auto_delete_log` ...
php sql 日期 30天内,MySQL删除30天以前的数据（PHP）
weixin_36242131的博客
03-12 477
需求：1.每日定时执行清理30天以前旧数据，如：当前日期2019年5月8日，需清理所有小于2019年4月8日数据2.直接通过sql语句实现/**定时任务,每日0:00执行,删除一个月以前的短链接*00***/usr/local/php/bin/php01-delete_surl.php*/$link=newmysqli('127.0.0.1','root','root',...
MySQL删除30天以前的数据（PHP）
fef647的博客
03-08 1029
需求： 1.每日定时执行清理30天以前旧数据，如：当前日期2019年5月8日，需清理所有小于2019年4月8日数据 2.直接通过sql语句实现 /* *定时任务,每日0:00执行,删除一个月以前的短链接 *00***/usr/local/php/bin/php01-delete_surl.php */ $link=newmysqli('127.0.0.1','root','root','surl',3306); $sql="deletefrominform...
mysql定时删除过期数据记录
浮华瑰梦
10-16 1万+
连接登录MySQL后，先查看MySQL是否开启了事件功能： 命令：show variables like '%sc%'; 发现event_sheduler是OFF关闭的; 2. 打开event_scheuler: 临时开启(mysql服务重启后之后失效)SET GLOBAL event_scheduler = ON; SET GLOBAL event_schedu
msyql设置清除30天以前的数据
oThrowsException的博客
11-10 427
【代码】msyql设置清除30天以前的数据。
删除30天以前的sql语句
weixin_53593865的博客
12-06 2628
Delete From t_order where DATE(orderDate) <= DATE(DATE_SUB(NOW(),INTERVAL 30 day)) ; t_order 表名 orderDate 需要依据的时间字段
mysql数据库定时删除数据
weixin_42184538的博客
12-09 7276
方法有多种，如通过脚本去定期执行SQL、使用mysql自带的删除策略，windows、linux 的定时任务就不记录了，就是通过脚本定时去调用mysql执行sql。本文仅介绍推荐的MYSQL删除策略 自 MySQL5.1.6起，增加了一个非常有特色的功能–事件调度器(Event Scheduler)，可以用做定时执行某些特定任务（例如：删除记录、对数据进行汇总等等），来取代原先只能由操作系统的计划任务来执行的工作。更值得 一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务.
mysql元数据管理如何使用存储过程
09-19
<think> 我们正在讨论MySQL中使用存储过程进行元数据管理。元数据管理包括查询、修改、维护数据库的结构信息（如表、列、索引等）。存储过程可以封装复杂的SQL操作，提高效率并减少代码重复。 参考引用： [^1]：提到存储过程的优点包括执行效率和SQL代码封装。 [^2]：展示了一个简单的存储过程示例，使用SELECT INTO将结果存入变量。 [^3]：提到MySQL的灵活性，支持多种应用场景。 [^4]：展示了使用存储过程插入大量测试数据，其中使用了事务控制（autocommit=0和COMMIT）和循环。 [^5]：展示了存储过程结合事件调度器定时清理数据，并提到了使用索引提高效率。 在MySQL中，我们可以通过存储过程来执行元数据管理任务，例如： 1. 自动生成数据库文档（如表结构报告） 2. 批量修改表结构（如为所有表添加一个公共列） 3. 监控数据库模式变化（如检查索引缺失） 4. 定期维护（如清理历史数据或优化表） 下面我们将重点介绍如何使用存储过程进行元数据管理，包括查询元数据（如列出所有表及其列信息）和修改元数据（如批量添加列）。 注意：在存储过程中执行动态SQL（特别是DDL语句）需要使用预处理语句（PREPARE和EXECUTE）。 步骤1：创建存储过程查询元数据 例如，创建一个存储过程，接收数据库名作为参数，输出该数据库中所有表及其列的信息。 步骤2：创建存储过程修改元数据 例如，创建一个存储过程，为指定数据库中的所有表添加一个相同的列（如果该列不存在）。 步骤3：使用存储过程进行维护 例如，定期清理数据（如引用[^5]所示）或重建索引。 具体示例： 示例1：查询元数据（获取所有表及其列信息） 我们可以利用information_schema中的表（如TABLES和COLUMNS）来获取元数据。 示例2：修改元数据（批量添加列） 注意：修改表结构需要动态SQL，因为表名是变量。 示例3：维护任务（清理数据） 如引用[^5]所示，我们可以创建存储过程来删除过期数据，然后通过事件定时调用。 下面给出两个存储过程示例： 示例1：生成数据库表结构报告的存储过程 该存储过程将遍历指定数据库的所有表，并输出每张表的列信息（列名、数据类型、是否为空等）。 示例2：为所有表添加一个公共列的存储过程 该存储过程将检查每个表是否已存在该列，如果不存在则添加。 注意：在存储过程中执行DDL（ALTER TABLE）需要动态SQL。 我们开始编写存储过程： 示例1：生成表结构报告的存储过程（这里我们选择将结果输出到控制台，实际也可以插入到某个表中） 由于存储过程不能直接返回多结果集，我们可以先创建一个临时表来存储结果，然后在存储过程外查询该表，或者使用SELECT在过程中输出（但注意只能输出一个结果集）。这里我们使用SELECT输出，但注意多个SELECT会形成多个结果集，因此我们使用UNION ALL或者在一个结果集中输出（这里我们采用在一个结果集中输出，所以使用临时表存储，最后再查询临时表）。 然而，为了简单演示，我们可以使用游标逐表查询，并将每张表的列信息插入到临时表中，最后一次性输出。 示例2：为所有表添加公共列的存储过程 另外，注意事务：DDL语句会隐式提交，所以不能在事务中执行多个DDL（除非使用MySQL8.0的原子DDL）。因此，我们为每个表单独执行ALTER，如果出错就在循环中捕获错误继续下一个。 下面给出示例代码： 示例1：生成表结构报告 ```sql DELIMITER // CREATE PROCEDURE GenerateSchemaReport(IN dbName VARCHAR(64)) BEGIN -- 创建临时表存储结果 DROP TEMPORARY TABLE IF EXISTS TableSchemaReport; CREATE TEMPORARY TABLE TableSchemaReport ( TableName VARCHAR(64), ColumnName VARCHAR(64), DataType VARCHAR(64), IsNullable VARCHAR(3), ColumnKey VARCHAR(3) ); -- 使用游标遍历所有表 DECLARE done BOOLEAN DEFAULT FALSE; DECLARE curTable VARCHAR(64); DECLARE tableCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = dbName AND TABLE_TYPE = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN tableCursor; tableLoop: LOOP FETCH tableCursor INTO curTable; IF done THEN LEAVE tableLoop; END IF; -- 将当前表的列信息插入临时表 INSERT INTO TableSchemaReport SELECT curTable, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = dbName AND TABLE_NAME = curTable; END LOOP; CLOSE tableCursor; -- 输出结果 SELECT * FROM TableSchemaReport; -- 可以选择不删除临时表，因为会话结束会自动删除 -- DROP TEMPORARY TABLE TableSchemaReport; END // DELIMITER ; ``` 示例2：为所有表添加一个公共列（例如：添加`created_at`列，类型为TIMESTAMP） ```sql DELIMITER // CREATE PROCEDURE AddColumnToAllTables(IN dbName VARCHAR(64), IN colName VARCHAR(64), IN colType VARCHAR(64)) BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE curTable VARCHAR(64); DECLARE stmt VARCHAR(1024); -- 获取所有表 DECLARE tableCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = dbName AND TABLE_TYPE = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN tableCursor; tableLoop: LOOP FETCH tableCursor INTO curTable; IF done THEN LEAVE tableLoop; END IF; -- 检查该表是否已经存在该列 IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = dbName AND TABLE_NAME = curTable AND COLUMN_NAME = colName ) THEN -- 动态构造SQL语句 SET @sql = CONCAT('ALTER TABLE `', dbName, '`.`', curTable, '` ADD COLUMN `', colName, '` ', colType); -- 预处理并执行 PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END LOOP; CLOSE tableCursor; END // DELIMITER ; ``` 注意：在实际使用中，考虑以下事项： 1. 错误处理：上述存储过程中，如果某个表执行ALTER出错，会中断整个存储过程。为了更健壮，可以单独处理每个表的错误（使用DECLARE CONTINUE HANDLER FOR SQLEXCEPTION），记录错误然后继续。 2. 性能：如果表很多，可能需要考虑性能影响。 3. 权限：执行存储过程需要足够的权限（如SELECT INFORMATION_SCHEMA的权限以及ALTER表的权限）。 此外，存储过程也可以用于其他元数据管理任务，如： - 批量重命名列 - 删除所有表的某个列 - 生成索引报告并建议添加索引 总之，使用存储过程进行元数据管理可以提高效率并减少重复工作。结合事件调度器（如引用[^5]所示），还可以实现定期自动维护。 最后，生成相关问题： </think> ### MySQL使用存储过程进行元数据管理的方法 在MySQL中，存储过程是执行元数据管理的强大工具，它能封装复杂操作、提高执行效率并减少代码冗余。以下是具体方法和示例： #### 1. **元数据查询与报告生成** 通过查询`information_schema`系统视图，可生成结构化元数据报告： ```sql DELIMITER $$ CREATE PROCEDURE GenerateSchemaReport(IN dbName VARCHAR(64)) BEGIN DECLARE done BOOLEAN DEFAULT FALSE; DECLARE tblName VARCHAR(64); DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = dbName; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; CREATE TEMPORARY TABLE IF NOT EXISTS SchemaReport ( TableName VARCHAR(64), ColumnName VARCHAR(64), DataType VARCHAR(64), IsNullable VARCHAR(3) ); OPEN cur; read_loop: LOOP FETCH cur INTO tblName; IF done THEN LEAVE read_loop; END IF; INSERT INTO SchemaReport SELECT tblName, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = dbName AND TABLE_NAME = tblName; END LOOP; CLOSE cur; SELECT * FROM SchemaReport; DROP TEMPORARY TABLE SchemaReport; END$$ DELIMITER ; ``` **调用方式**： ```sql CALL GenerateSchemaReport('your_database'); ``` 此过程生成包含表名、列名、数据类型和可空性的详细报告[^1]。 #### 2. **自动化元数据维护** 存储过程可批量执行DDL操作，如为所有表添加审计列： ```sql DELIMITER $$ CREATE PROCEDURE AddAuditColumns() BEGIN DECLARE tbl VARCHAR(64); DECLARE done BOOLEAN DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; modify_loop: LOOP FETCH cur INTO tbl; IF done THEN LEAVE modify_loop; END IF; -- 动态SQL避免列重复添加 SET @sql = CONCAT( 'ALTER TABLE ', tbl, ' ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,', ' ADD COLUMN updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur; END$$ DELIMITER ; ``` #### 3. **元数据驱动的数据清理** 结合事件调度器实现定时维护（如引用[^5]）： ```sql DELIMITER $$ CREATE PROCEDURE CleanOldData(IN retentionDays INT) BEGIN DECLARE tbl VARCHAR(64); DECLARE done BOOLEAN DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'log_%'; -- 仅处理日志表 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET autocommit = 0; -- 关闭自动提交 START TRANSACTION; OPEN cur; clean_loop: LOOP FETCH cur INTO tbl; IF done THEN LEAVE clean_loop; END IF; SET @sql = CONCAT( 'DELETE FROM ', tbl, ' WHERE created_at < DATE_SUB(NOW(), INTERVAL ', retentionDays, ' DAY)' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur; COMMIT; SET autocommit = 1; END$$ DELIMITER ; ``` **定时调用**（每日清理90天前数据）： ```sql CREATE EVENT DailyDataClean ON SCHEDULE EVERY 1 DAY DO CALL CleanOldData(90); ``` #### 4. **元数据验证与修复** 检查缺失的主键并自动修复： ```sql DELIMITER $$ CREATE PROCEDURE CheckPrimaryKeys() BEGIN DECLARE tbl VARCHAR(64); DECLARE done BOOLEAN DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; CREATE TEMPORARY TABLE MissingPK ( TableName VARCHAR(64) PRIMARY KEY, Status VARCHAR(20) ); OPEN cur; check_loop: LOOP FETCH cur INTO tbl; IF done THEN LEAVE check_loop; END IF; -- 检查是否存在主键 IF NOT EXISTS ( SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND INDEX_NAME = 'PRIMARY' ) THEN INSERT INTO MissingPK VALUES (tbl, 'Missing PK'); SET @sql = CONCAT('ALTER TABLE ', tbl, ' ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END LOOP; CLOSE cur; SELECT * FROM MissingPK; -- 输出修复报告 DROP TEMPORARY TABLE MissingPK; END$$ DELIMITER ; ``` ### 关键优势与注意事项 1. **事务控制** 使用`SET autocommit=0`和显式`COMMIT`确保元数据操作的原子性（如引用[^4]所示）。 2. **动态SQL** 通过`PREPARE/EXECUTE`执行动态生成的DDL语句，解决表名参数化问题。 3. **性能优化** - 游标处理大量表时，限制结果集范围（如`TABLE_NAME LIKE 'log_%'`） - 索引验证：引用`information_schema.STATISTICS`优化索引管理（如引用[^5]） 4. **权限要求** 执行存储过程需`ALTER ROUTINE`权限，修改元数据需`ALTER`权限。 > **提示**：复杂元数据操作建议在低峰期执行，避免锁表影响业务[^3][^5]。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值