更新查询以每月重置表字段 [英] Update query to reset table field per month basis

问题描述

我有一个名为 service_tracker 的表.该表包含 6 个字段，但与我下面的问题最相关的是 cycle_start 和 last_used.目前，我能够确定关于 service 的以下内容:user 在 1 个月内使用它的次数(usage_count)(last_used,cycle_start).第一个查询在 usage_count 上加 1，只要它不超过 1 个月.如果超过 1 个月，第二个查询将清除并设置 usage_count = 1.查询效果很好，但是，是否可以在一年中的十二个月的基础上执行此操作?一旦四月、五月、六月等结束，将 usage_count 清除并设置为 1?

I have a table named service_tracker. The table contains 6 fields but the most relevant to my question below is cycle_start and last_used. Currently I am able to determine the following things about a service: the times it has been used (usage_count) by user within 1 month (last_used,cycle_start). First query adds 1 to the usage_count as long as it is not older then 1 month. Second query clears and sets the usage_count = 1 if older than 1 month. Queries work great, however, would it possible to perform this on the basis of the twelve months of the year? Clear and set to 1 the usage_count once April, May, June etc. is over?

查询:

UPDATE service_tracker
  SET usage_count = usage_count + 1,
  last_used = CURDATE()
WHERE service_tracker = 'cloudstorage' AND `user` = 'test2' AND last_used >= date_sub(CURDATE(), INTERVAL 1 month);
UPDATE service_tracker
  SET usage_count = 1,
  last_used = '0000-00-00',
  cycle_start = CURDATE()
WHERE service_tracker = 'cloudstorage' AND `user` = 'test2' AND cycle_start < date_sub(CURDATE(), interval 1 month);

表架构

CREATE TABLE IF NOT EXISTS `service_tracker` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service` varchar(50) NOT NULL,
  `cycle_start` date NOT NULL,
  `user` varchar(200) NOT NULL,
  `usage_count` int(6) NOT NULL,
  `last_used` date NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `service_tracker` (`id`, `service`, `cycle_start`, `user`, `usage_count`, `last_used`) VALUES
    (1, 'webserver', '2015-04-24', 'test1', 13, '2015-04-24'),
    (2, 'cloudstorage', '2015-04-16', 'test2', 390, '2015-04-30'),
    (3, 'web-traffic-tracker', '2015-04-16', 'test3', 1916, '2015-04-30'),
    (4, 'remote-it-help', '2015-04-16', 'test4', 91, '2015-04-16');

推荐答案

您可以使用 mysql 调度程序在特定(和重复)时间运行查询.

You can use the mysql scheduler to run queries at specific (and repeating) times.

DELIMITER $$
CREATE EVENT reset_usage_count
ON SCHEDULE EVERY '1' MONTH
STARTS '2015-05-01 00:00:00'
DO 
BEGIN
 -- your query to reset the usage_count
END$$    
DELIMITER ;

这个查询会在每个月的第一天午夜开始查询.您需要确保全局变量 event_scheduler 设置为 on.您可以通过执行 show processlist 并查找 User: event_scheduler

This one would start the query at midnight, on the first of every month. You will need to make sure the global variable event_scheduler is set to on. You can check that it is running by executing show proceslist, and looking for User: event_scheduler

这篇关于更新查询以每月重置表字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！