MYSQL stored procedures and events

MYSQL stored procedures and events

About the creation of stored procedures and events in mysql

The following stored procedure mainly implements the function of querying the data in the table half a year ago. If yes, It stores the data in the file and then deletes the data.

CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`()BEGINselect COUNT(*) INTO @count from S_ACCOUNT  where date_add(registerTime, interval 6 month)<=NOW(); IF @count>0THEN set @today=TIME_TO_SEC(now()); set @select_sql=concat("select * from S_ACCOUNT  where date_add(registerTime, interval 6 month)<=NOW()  INTO OUTFILE 'C:/\\",@today,".txt'  FIELDS TERMINATED BY ',';");         PREPARE charu FROM @select_sql;          EXECUTE charu; delete  from S_ACCOUNT  where date_add(registerTime, interval 6 month)<=NOW() ; commit;END IF;END;

Fields terminated by ',' delimiter between FIELDS
Optionally enclosed by '"' does not apply a numeric value when fields are surrounded.
Lines terminated by '\ n' line break

The following is the code for creating an event. The logic starts from '2017-11-05 09:00:00 'and runs the pro_test () process every day.

CREATE DEFINER=`root`@`localhost` EVENT `NewEvent`ON SCHEDULE EVERY 1 DAY STARTS '2014-11-05 09:00:00'ON COMPLETION NOT PRESERVEENABLEDOcall pro_test();

When creating an event, we may prompt that the event is closed. In this case, you need to manually enable the event.

First, query the status of the scheduled event in SQL: SHOW VARIABLES LIKE 'event _ schedning'
If the returned result is off, it indicates that the task is disabled. If it is on, it indicates that the scheduled task has been enabled.
Find the my. ini file in the mysql program directory and add an item: event_scheduler = 1
After saving, restart the mysql service. You can find the Restart service in service management.

You can also use the script to implement:
Mysql event_scheduler
Enable the event_scheduler SQL command:
Set global event_scheduler = ON;
SET @ global. event_scheduler = ON;
Set global event_scheduler = 1;
SET @ global. event_scheduler = 1;
Instead, disable the event_scheduler command:
Set global event_scheduler = OFF;
SET @ global. event_scheduler = OFF;
Set global event_scheduler = 0;
SET @ global. event_scheduler = 0;