MySQL stored procedures and events

about the stored procedures under MySQL and the creation of events

The following is the main function of the stored procedure is to query the table half a year ago data, if there is a file, and then delete the data.

CREATE definer = ' root ' @ ' localhost ' PROCEDURE ' newproc ' () beginselect COUNT (*) into the @count from S_account  where date_a DD (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 ', ' inter-field separator
Optionally enclosed by ' "' enclosing field is invalid for numeric type
LINES TERMINATED by ' \ n ' line break

The following is the code for creating the event, which starts with ' 2014-11-05 09:00:00 ' and executes 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 PR Eserveenabledocall pro_test ();

When we create an event, it is possible to prompt the event to be turned off, which requires a manual turn-on event.

First, query the status of the scheduled event in sql: SHOW VARIABLES like ' Event_scheduler '
If the return is off, the current is off, and if it is on, the scheduled task is currently turned on.
Locate the My.ini file in the MySQL program directory and add an entry: Event_scheduler = 1
After saving, restart the MySQL service, restart the service can be found in the service management.

can also be implemented with a script:
MySQL Event_scheduler
To open 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, close the Event_scheduler command:
SET GLOBAL event_scheduler = OFF;
SET @ @global. Event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @ @global. Event_scheduler = 0;

MySQL stored procedures and events