Mysql数据库定时删除三天以上过期数据_myql查询即将过期的订单

简述

由于霍尼韦尔门禁项目要求，需要把设备信息存到数据库当中，并需要实现数据三天有效期，过期数据会定时检查删除掉。本文章主要讲关于实现数据三天有效期的业务实现。

定时器删除过期数据

创建存储过程

DELIMITER // 
create procedure del_data()
BEGIN
     delete  from  t_device_info where f_time < date_sub(curdate(), interval 3 day);
END//
DELIMITER ;
1
2
3
4
5
6

注意：
delimiter // 是定义结束符。mysql 默认的结束符是;号，这里重新定义了结束符为//,否则中间的语句中遇到 ;号，会认为语句已经结束了。所以需要重新定义结束符，否则会报错。END 后面加//，就是代表已经结束。感觉好坑

查看存储过程

mysql> show procedure status; 
+------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db   | Name     | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | del_data | PROCEDURE | root@localhost | 2019-10-18 17:47:09 | 2019-10-18 17:47:09 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+----------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set
1
2
3
4
5
6
7

mysql查看存储过程函数

• 查询数据库中的存储过程和函数

      select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE'   //存储过程
      select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION'   //函数

      show procedure status; //存储过程
      show function status;     //函数
1
2
3
4
5

• 查看存储过程或函数的创建代码

　show create procedure proc_name;
　show create function func_name;
1
2

• 查看视图

　　SELECT * from information_schema.VIEWS   //视图
　　SELECT * from information_schema.TABLES   //表
1
2

• 查看触发器

　　SHOW TRIGGERS [FROM db_name] [LIKE expr]
　　SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G
1
2

写Event事件

create event del_event  
on schedule 
EVERY 10 second  
STARTS '2019-10-06 16:37:00'  
ON COMPLETION  PRESERVE ENABLE  
do  call del_data();
1
2
3
4
5
6

Mysql数据库显示

打开事件

首先查看mysql是否开启定时任务开关

SHOW VARIABLES LIKE ‘event_scheduler’;
1

Value为ON则已打开，OFF则关闭

如果是OFF，就先打开

• 方法一：执行mysql语句
  set global event_scheduler = on;

• 方法二：永久开启方法：
  my.cnf中[mysqld]添加event_scheduler=on #重启服务

事件开启与关闭：

这个是最简单但是也是最重要的，我们要手动的启动这个定时器，要不然是没法工作的。

ALTER EVENT del_event ON COMPLETION PRESERVE ENABLE;
1

修改事件开启与关闭

如果不想用该定时器了的话，可以直接在数据库事件中将状态更改为ENable即可。

修改定时器时间间隔

使用navicat连接的数据库->事件->找到对应的事件修改
如果想要调整定时器执行时间间隔，可以直接在事件中修改

效果测试

查询三天以上过期数据

1. 方式一

mysql> select * from t_device_info where to_days(now())-to_days(f_time) >= 3;
+------+---------------------+-----------------+-------------+----------+------------+
| f_id | f_time              | f_ADVDeviceName | f_ADVStatus | f_status | f_describe |
+------+---------------------+-----------------+-------------+----------+------------+
|    8 | 2019-10-14 14:04:38 | MJ-F46-A02      | 903         |          | 面板正常   |
| 3187 | 2019-10-13 17:37:00 |                 |             | 有效卡   |            |
| 3563 | 2019-10-14 06:39:00 |                 |             | 有效卡   |            |
| 3950 | 2019-10-10 11:55:00 |                 |             | 有效卡   |            |
| 4350 | 2019-10-11 10:12:00 |                 |             | 有效卡   |            |
| 4361 | 2019-10-14 16:37:00 |                 |             | 有效卡   |            |
+------+---------------------+-----------------+-------------+----------+------------+
6 rows in set
1
2
3
4
5
6
7
8
9
10
11
12

TO_DAYS函数

TO_DAYS函数 返回一个天数! 啊哈？什么天数? 从年份0开始的天数

mysql> SELECT TO_DAYS('2019-10-08 17:33:43'); 
+--------------------------------+
| TO_DAYS('1997-10-07 17:33:43') |
+--------------------------------+
|                         729669 |
+--------------------------------+
1
2
3
4
5
6

就是从0年开始 到2019年10月8号之间的天数。

mysql to_days函数的使用

//1，查询当天（今天）的数据
SELECT * FROM `order` WHERE TO_DAYS(order_time) = TO_DAYS(NOW())

 
//2，查询昨天的数据
SELECT * FROM `order` WHERE TO_DAYS(NOW()) - TO_DAYS(order_time) = 1

 
//3，查询最近7天的数据（包括今天一共7天）
SELECT * FROM `order` where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(order_time)

 
//4，查询最近30天的数据（包括今天一共30天）
SELECT * FROM `order` where DATE_SUB(CURDATE(), INTERVAL 30 DAY) < date(order_time)

 
//5，查询当月（本月）的数据
SELECT * FROM `order` WHERE DATE_FORMAT(order_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

 
//6，查询上个月的数据
SELECT * FROM `order` WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(order_time,'%Y%m')) =1

 
//7，查询本季度的数据
SELECT * FROM `order` WHERE QUARTER(order_time)=QUARTER(NOW())

//8，查询上季度的数据
SELECT * FROM `order` WHERE QUARTER(order_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER))

//9，查询当年（今年）的数据
SELECT * FROM `order` WHERE YEAR(order_time)=YEAR(NOW())

//10，查询去年的数据
SELECT * FROM `order` WHERE YEAR(order_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2. 方式二

mysql> select *  from  t_device_info where f_time < date_sub(curdate(), interval 3 day);
+------+---------------------+-----------------+-------------+----------+------------+
| f_id | f_time              | f_ADVDeviceName | f_ADVStatus | f_status | f_describe |
+------+---------------------+-----------------+-------------+----------+------------+
|   16 | 2019-10-08 14:04:38 | MJ-F45-A02      | 903         |          | 面板正常   |
+------+---------------------+-----------------+-------------+----------+------------+
1 row in set
1
2
3
4
5
6
7

定义和用法
DATE_SUB() 函数从日期减去指定的时间间隔。

语法
DATE_SUB(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

执行定时器后查询结果

mysql> select * from t_device_info where to_days(now())-to_days(f_time) >= 3;
Empty set
1
2

Navicat数据库管理工具定时器设置

事件&计划设置

• EVERY :每隔多长事件执行一次；
• STARTS:开始时间设置
• ENDS：运行结束时间

事件&定义

call del_data()
1

函数&定义

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200114144642569.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NDYyNDM2,size_16,color_FFFFFF,t_70

关于定时删除三天以上数据不生效的问题

原来删除操作实现

CREATE DEFINER=`root`@`localhost` PROCEDURE `del_data`()
BEGIN
	delete  from  t_device_info where f_time < data_sub(curdata ,interval 3 day);
END
1
2
3
4

修改后删除数据实现

CREATE DEFINER=`root`@`localhost` PROCEDURE `del_data`()
BEGIN
 delete  from  t_device_info where now() > date_add(f_time,interval 3 day);
END
1
2
3
4

关于mysql now() sysdate() curdate()区别

-- now() 返回当前日期和时间，格式如下：2013-01-17 10:57:13
mysql> select now(), sleep(5), now();
+---------------------+----------+---------------------+
| now()               | sleep(5) | now()               |
+---------------------+----------+---------------------+
| 2013-01-17 10:57:13 |        0 | 2013-01-17 10:57:13 |
+---------------------+----------+---------------------+
两个值相同，表示语句开始执行的时间。
 
-- curdate() 返回当前日期，格式如下：2013-01-17
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2013-01-17 |
+------------+
 
-- curtime(), 返回当前时间，格式如下：12:49:26
mysql> select curtime(), sleep(5), curtime();
+-----------+----------+-----------+
| curtime() | sleep(5) | curtime() |
+-----------+----------+-----------+
| 12:49:26  |        0 | 12:49:26  |
+-----------+----------+-----------+
两个值相同，都表示语句开始执行的时间。
 
-- sysdate() 返回当前日期和时间，格式：2013-01-17 13:02:40
mysql> select sysdate(), sleep(5), sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(5) | sysdate()           |
+---------------------+----------+---------------------+
| 2013-01-17 13:02:40 |        0 | 2013-01-17 13:02:45 |
+---------------------+----------+---------------------+
两个值不同，sysdate表示实时的系统时间。
sysdate() 和 now()的区别，一般在执行SQL语句时，都是用now()；
因为使用sysdate()时，有可能导致主库和从库执行时返回值不一样，导致主从数据库不一致。
 
其它函数格式显示：
mysql> select now(), curdate(), sysdate(), curtime() \G;
*************************** 1. row ***********************
    now(): 2013-01-17 13:07:53
curdate(): 2013-01-17
sysdate(): 2013-01-17 13:07:53
curtime(): 13:07:53
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

MySQL 查询大于“时间字段”15分钟、1小时、1天的数据

以下代码中times为时间字段，类型为datetime

1. 查询大于times十五分钟的数据

//大于号后面都是获取times十五分钟后的时间
select*from table where now() >SUBDATE(times,interval -15 minute);
select*from table where now() > SUBDATE(times,interval -900 second);
select*from table where now() > date_add(times,interval 15 minute);
select*from table where now() >ADDDATE(times,interval 15 minute);
1
2
3
4
5

2. 查询大于times一小时的数据

//大于号后面都是获取times一小时后的时间
select*from table where now() >SUBDATE(times,interval -1 hour);
select*from table where now() > SUBDATE(times,interval -60*60 second);
select*from table where now() > date_add(times,interval -1 hour);
select*from table where now() >ADDDATE(times,interval 15 hour);
1
2
3
4
5

3. 查询大于times一天的数据

//大于号后面都是获取times一天后的时间
select*from table where now() >SUBDATE(times,interval -1 day);
select*from table where now() > SUBDATE(times,interval -60*60*60 second);
select*from table where now() > date_add(times,interval -1 day);
select*from table where now() >ADDDATE(times,interval 15 day);
1
2
3
4
5

相应的如果想查询前一天的数据，加一个“-”号即可

参考：

MySQL 查询大于“时间字段”15分钟、1小时、1天的数据

mysql中 now() sysdate() curdate() 的区别