MySQL stored Procedure Delete large table

1. Permissions issues

2. Some commands related to stored procedures

Show procedure status\g see what stored procedures are in the database Show procedure statuswheredb ='db_name'\g view which stored procedures are in the specified database (db_name)SelectName fromMysql.procwheredb ='db_name'; To view which stored procedures are in the specified database (db_name)SelectRoutine_name fromInformation_schema.routineswhereRoutine_schema ='db_name'see what stored procedures are in the specified database (db_name) Show CREATE PROCEDURE dbname.sp_delete_bakhistory_new \g View the details of the stored procedure

3. MySQL stored procedure Delete big table

Delimiter//CREATE PROCEDURE ' sp_delete_bigtable_60 ' () begindeclare v_idint;d eclare Stopflagint;d eclare v_numint;d eclare cursor_id cursor for SelectId fromBigTablewhereGmt_created<now ()-interval -Day;declareContinueHandler forNot foundSetstopflag=1; open cursor_id; repeat fetch cursor_id into v_id; begin Delete fromBigTablewhereID =v_id; end;Setv_num=v_num+1; ifMoD (V_num, -)=0then commit; Endif; until Stopflag=1end repeat; close cursor_id; END;//delimiter;

It should be noted that delimiter//and delimiter; delimiter is the meaning of the separator, because MySQL defaults to ";" As a delimiter, if we do not declare the separator, then the compiler will treat the stored procedure as an SQL statement, the process of compiling the stored procedure will be error-free, so you have to use the delimiter keyword in advance to declare the current segment delimiter, so that MySQL will ";" As the code in the stored procedure, the code is not executed and the delimiter is restored after it is exhausted. If you are using MySQL's Administrator management tool, you can create it directly and no longer need to declare it.