MySQL stored Procedure Delete large table

Source: Internet
Author: User

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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.