MySQL-如何每24小时减少int值? [英] MySQL - How to decrement int value every 24 hours?

问题描述

我正在使用PHP开发系统.在我的MySQL中有一个表borrow.内部借位是borrow_status一个varchar和borrow_remain这是一个int值.当用户单击提交按钮时.查询将运行类似

 INSERT INTO `borrow`( `borrow_status`,`borrow_remain`) VALUES ('Borrowing',3)

如何在达到零时每隔24小时然后将UPDATE borrow_status减小为Finish?

我也有一个book表，也有一个book_quantity，我唯一关心的是，如果borrow_status更新为Finish，则book_quantity必须递增.

解决方案

您可以按照以下三个步骤实施上述方案:

1)首先，您必须启用Mysql事件计划程序.

SET GLOBAL event_scheduler = 1;

2)在mysql中创建一个过程，该过程将执行与借阅状态更新相关的功能.

    delimiter //
    CREATE PROCEDURE UBS()
    BEGIN
        UPDATE borrow set borrow_remain = borrow_remain - 1 
        WHERE borrow_status='borrowing';
        UPDATE borrow SET borrow_status = 'finished'
        WHERE borrow_remain=0;

    END //
delimiter ;

3)创建一个将在24小时间隔内安排的活动.

CREATE
EVENT UBSEvent
ON SCHEDULE EVERY 24 HOUR STARTS '2015-03-04 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
CALL UBS();

注意:

1. UBS是过程名称.[UBS =更新借阅状态(只是一个 相关缩写]
2. 该活动将从明天(2015年3月4日)12:00 AM开始

今天愉快！

编辑:要在借阅表中的状态"更新为完成"时增加书表中的book_count，您需要一个触发器.

假设您有一个书本表，其中书本的计数由book_count表示；然后，以下触发器将执行您想要的操作.

DELIMITER $$
CREATE TRIGGER IncBookCount AFTER UPDATE ON borrow
FOR EACH ROW
BEGIN
     IF NEW.borrow_status = 'finished' THEN
            UPDATE book SET book_count = book_count+1;
     END IF;
END $$
DELIMITER;

建议: :您需要改进数据库设计.否则，将来在设计复杂的体系结构时可能会遇到更多麻烦.保持健康！

I am working a system in PHP. In my MySQL have a table borrow. Inside borrow is borrow_status a varchar and borrow_remain which is an int value. When the user click the submit button. The query will run something like

 INSERT INTO `borrow`( `borrow_status`,`borrow_remain`) VALUES ('Borrowing',3)

How can I decrement the borrow_remain value per 24 hours then UPDATE the borrow_status to Finish when it reaches zero?

EDIT: I also have a book table, that have a book_quantity, my only concern is that the book_quantity must increment if the borrow_status is updated to Finish.

解决方案

You can implement the above scenario following the three steps given below :

1) First you have to enable Mysql event scheduler.

SET GLOBAL event_scheduler = 1;

2) Create a procedure in mysql that will do the borrow status update related functionalities.

    delimiter //
    CREATE PROCEDURE UBS()
    BEGIN
        UPDATE borrow set borrow_remain = borrow_remain - 1 
        WHERE borrow_status='borrowing';
        UPDATE borrow SET borrow_status = 'finished'
        WHERE borrow_remain=0;

    END //
delimiter ;

3) Create an event which will be scheduled in 24 hours interval.

CREATE
EVENT UBSEvent
ON SCHEDULE EVERY 24 HOUR STARTS '2015-03-04 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
CALL UBS();

Note :

1. UBS is the procedure name.[ UBS = Update Borrow Status (just a relevant abbreviation ]
2. The event will start from tomorrow (4 March 2015) at 12:00 AM

Have a nice day!

EDIT: To increment book_count in book table whenever a borrow_status in borrow table gets updated to 'finished' you need a trigger.

Suppose you have a book table where the count of the book is denoted by book_count; Then the following trigger will do what you want.

DELIMITER $$
CREATE TRIGGER IncBookCount AFTER UPDATE ON borrow
FOR EACH ROW
BEGIN
     IF NEW.borrow_status = 'finished' THEN
            UPDATE book SET book_count = book_count+1;
     END IF;
END $$
DELIMITER;

SUGGESTION : You need to improve your database design. Otherwise you might get more troubles in designing a complex architecture in future. Stay well!

这篇关于MySQL-如何每24小时减少int值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！