Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what MySQL stored procedures are, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

What are the MySQL stored procedures?

Suppose it is divided into 64 pieces according to the user_id sub-table.

Insertintotable_new_0000select*fromtable_oldwheremod (user_id,64) = 0% insert Tableys newborns 0001 selectplaces from tableboards oldwaters emod (user_id,64) = 1 *.

A total of 64 sql,OK are done. However, this table has been scanned 64 times, which does more useless work and leads to longer downtime.

Although the MySQL stored procedure is not very familiar, I learned a little bit to write two scripts, a full plus an incremental script to split the table.

The online library has also been put into practice, with 8 sub-tables, each with 1000W records split into 64 sub-tables.

With a total time of 150 minutes, several sub-meters can run together. I run 3 sub-meters at the same time.

Incremental time each sub-watch 4 minutes to run together, a total of 8 minutes to finish. In this way, the downtime plus the release of the application can be done in 20 minutes.

Attached script:

#

Delimeter//

-

-full script:

CREATEPROCEDUREsp_xf_move_item () begindeclarev_exitintdefault0;declarev_spidbigint;declarev_idbigint;declareiintdefault0;declarec_tableint

-- define cursors (tables to be split, define a quantity of deadlines)

Declarec_idscursorforselectid,user_idfromitem_records_0000wheregmt_modified='2010-8-2500 the handlers for notched setters and exituals 1 intoc_table opencyclidsrepeatfetchchands idsintov exitorials intoc_table

-- determine whether the data already exists

SET@SQL_C=CONCAT ('selectcount (*) into@v_rowfromitem_record_',LPAD,' whereid='); PREPARESTMT_CFROM@SQL_C;EXECUTESTMT_Cusing@vv_id;DEALLOCATEPREPARESTMT_C;SET@SQL_INSERT=CONCAT ('insertintobbc_item_record_',LPAD,' select*fromitem_records_0000whereid='); PREPARESTMT_IFROM@SQL_INSERT;SET@SQL_DELETE=CONCAT ('DELETEFROMbbc_item_record_',LPAD,' whereid=') PREPARESTMT_DFROM@SQL_DELETE

-- if the data already exists, delete in insert first.

If@v_row > 0thenEXECTESTMTS loaded Dusingbroken vaulted boxes = 0 thenEXECTEPREPARESTMTORESTMTONETESTMTORESTMTS of DEALLOCATEPREPARESTMTONETITY of DEALLOCATEPREPARESTMTS of DEALLOCATEPREPARESTMTOID = 0thenEXECTESTMTS

If the time between the full volume and the increment is longer, then you can set the time and do a few more increments to shorten the final downtime, you know.

Callsp_xf_add_item () / /

After reading the above, do you have any further understanding of MySQL stored procedures? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.