How to realize automatic timing mysql add table data-CodePudding

question background:
1, equipment list table, table 1
Equipment checking table 2, table 2 (day, week, month, quarter, half yearly check, annual inspection, the custom check)
3, table 2 foreign key link table 1, a device can have a variety of inspection way,
4, table 3 equipment inspection record, recording equipment inspection list and inspection status (not inspection, inspection)
Problem description:
1, 1, 0 a month point automatically generated this month and equipment inspection data in table 3
2, write inspection status not inspection, the default
Please feel free to comment, appreciate,

CodePudding user response:

The mysql event

CodePudding user response:

Can use the event timer, set to perform every month, to generate a table of equipment check performance,
Depending on the type of inspection, set the loop body, generation schedule of different types of inspection performed during the month,

 delimiter $$
The drop event if the exists addplan; - create a timer 
The create event addplan 
On the schedule 
EVERY 1 MONTH - EVERY MONTH to perform a 
Timer STARTS '2019-07-28 22:02:00 - execution start time 
ON the COMPLETION PRESERVE the ENABLE 
Do 
The begin 
DECLARE pdate TIMESTAMP; - define variables 
 the implementation dateDECLARE sdate TIMESTAMP; - define variables 
The SET sdate=CURRENT_TIMESTAMP; - variables in the current time 
The SET pdate=CURRENT_TIMESTAMP; 
WHILE pdate & lt; ADDDATE (sdate, INTERVAL + 1 MONTH) DO - judgment, when the implementation date MONTH cycle 
INSERT INTO the result (devid, devname, checksta checktime) (SELECT a. d. evid, a. d. evname, 'not inspection, pdate FROM devinfo A, devcheck B WHERE a. d. evid=B.d evchid AND biggest heck=' daycheck '); - insert the daily inspection plan detail 
The set pdate=ADDDATE (pdate, INTERVAL + 1 DAY); - depending on the type of insert in front of the check, the execution date + 1 day 
END WHILE; - the end of the cycle 
End $$
Delimiter.

Insert SQL place can be inserted, depending on the type of different inspection INTERVAL should be set depending on the type of inspection, the implementation date, such as Month ADDDATE (pdate, INTERVAL + 1 Month), and so on,
Finally, we look at the results:

 SELECT * FROM the result;

I hope it can help you

CodePudding user response:

Thank you for your higher-ups know

CodePudding user response:

refer to the second floor zhenzhenup response:

, can use the event timer set to perform every month, to generate a table of equipment check performance,
Depending on the type of inspection, set the loop body, generation schedule of different types of inspection performed during the month,
 delimiter $$
The drop event if the exists addplan; - create a timer 
The create event addplan 
On the schedule 
EVERY 1 MONTH - EVERY MONTH to perform a 
Timer STARTS '2019-07-28 22:02:00 - execution start time 
ON the COMPLETION PRESERVE the ENABLE 
Do 
The begin 
DECLARE pdate TIMESTAMP; - define variables 
 the implementation dateDECLARE sdate TIMESTAMP; - define variables 
The SET sdate=CURRENT_TIMESTAMP; - variables in the current time 
The SET pdate=CURRENT_TIMESTAMP; 
WHILE pdate & lt; ADDDATE (sdate, INTERVAL + 1 MONTH) DO - judgment, when the implementation date MONTH cycle 
INSERT INTO the result (devid, devname, checksta checktime) (SELECT a. d. evid, a. d. evname, 'not inspection, pdate FROM devinfo A, devcheck B WHERE a. d. evid=B.d evchid AND biggest heck=' daycheck '); - insert the daily inspection plan detail 
The set pdate=ADDDATE (pdate, INTERVAL + 1 DAY); - depending on the type of insert in front of the check, the execution date + 1 day 
END WHILE; - the end of the cycle 
End $$
Delimiter. 
Insert SQL place can be inserted, depending on the type of different inspection INTERVAL should be set depending on the type of inspection, the implementation date, such as Month ADDDATE (pdate, INTERVAL + 1 Month), and so on,
Finally, we look at the results:
 SELECT * FROM the result; 
I hope it can help you

thank higher-ups guidance