mysql可以设置数据过期时间吗 mysql数据过期自动删除

转载

小咪咪 2023-08-28 10:51:35

文章标签 mysql可以设置数据过期时间吗 mysql 数据存储过程 文章分类 MySQL 数据库

Mysql数据库定时删除三天以上过期数据

简述
定时器删除过期数据

创建存储过程

查看存储过程
mysql查看存储过程函数

写Event事件
打开事件
事件开启与关闭：

修改事件开启与关闭
修改定时器时间间隔

效果测试

查询三天以上过期数据
执行定时器后查询结果

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

事件&计划设置
事件&定义
函数&定义

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

原来删除操作实现
修改后删除数据实现
关于mysql now() sysdate() curdate()区别
MySQL 查询大于“时间字段”15分钟、1小时、1天的数据

参考：

简述

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

定时器删除过期数据

创建存储过程

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

注意：
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

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;     //函数

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

show create procedure proc_name;
　show create function func_name;

查看视图

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

查看触发器

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

写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();

Mysql数据库显示

mysql可以设置数据过期时间吗 mysql数据过期自动删除_mysql可以设置数据过期时间吗

打开事件

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

SHOW VARIABLES LIKE ‘event_scheduler’;

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

mysql可以设置数据过期时间吗 mysql数据过期自动删除_数据_02

如果是OFF，就先打开

方法一：执行mysql语句
set global event_scheduler = on;
方法二：永久开启方法：
my.cnf中[mysqld]添加event_scheduler=on #重启服务

事件开启与关闭：

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

ALTER EVENT del_event ON COMPLETION PRESERVE ENABLE;

修改事件开启与关闭

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

mysql可以设置数据过期时间吗 mysql数据过期自动删除_mysql_03

修改定时器时间间隔

使用navicat连接的数据库->事件->找到对应的事件修改

如果想要调整定时器执行时间间隔，可以直接在事件中修改

mysql可以设置数据过期时间吗 mysql数据过期自动删除_存储过程_04

效果测试

查询三天以上过期数据

方式一

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

TO_DAYS函数

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

mysql> SELECT TO_DAYS('2019-10-08 17:33:43'); 
+--------------------------------+
| TO_DAYS('1997-10-07 17:33:43') |
+--------------------------------+
|                         729669 |
+--------------------------------+

就是从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))

方式二

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

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

语法
DATE_SUB(date,INTERVAL expr type)

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

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

执行定时器后查询结果

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

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

事件&计划设置

mysql可以设置数据过期时间吗 mysql数据过期自动删除_存储过程_05

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

事件&定义

call del_data()

mysql可以设置数据过期时间吗 mysql数据过期自动删除_存储过程_06

函数&定义

![在这里插入图片描述](

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

原来删除操作实现

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

修改后删除数据实现

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

关于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

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

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

查询大于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);

查询大于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);

查询大于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);

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