—?大表數據遷移,每天凌晨1點到5點執行,執行間隔時間10分鐘，遷移舊數據到歷史表。
DELIMITER?$$ ? USE?`dbx`$$ ? DROP?PROCEDURE?IF?EXISTS?`pro_xx`$$ ? CREATE??PROCEDURE?`pro_xx`() BEGIN?? ???DECLARE?p_oalid?INT?default?0; ???DECLARE?STOP?INT?DEFAULT?0;? ??? ???DECLARE?cur_oalid?CURSOR?FOR ?????????SELECToal.id?FROM?oal_xxx?oal?WHERE?oal.`ymd`?<concat><p>??????</p> <p><span style="color:#0070c0">??? -- </span><span style="color:#0070c0">讀取一行數據到變量</span><span style="color:#0070c0">??</span></p> <pre class="brush:php;toolbar:false">FETCH?cur_oalid?INTO?p_oalid;
?
???? — 這個就是判斷是否游標已經到達了最后??
?WHILE?STOP??1?DO? ?????????--?select?p_id; ?????????START?TRANSACTION;
? ? ?— ??進行數據遷移
???REPLACE?INTO?oal_xxx_history?SELECT?oal.*FROM?oal_xxx?oal?WHERE?oal.id=p_oalid?; ?????????????DELETE?FROM?oal_xxx?WHERE?id=p_oalid; ?????????????????????????? ?????????????--?INSERT?INTO?t?(tid)?VALUES?(p_tid); ?????????COMMIT;
???????? ???
???????????
??????? — 讀取下一行的數據???
?FETCH?cur_oalid?INTO?p_oalid; ??????END?WHILE;????????? ???CLOSE?cur_oalid;?--?關閉游標?? ?END$$ ? DELIMITER?;
?以上就是MySQL存儲過程–>通過游標遍歷和異常處理遷移數據到歷史表?的內容，更多相關內容請關注PHP中文網（www.php.cn）！?
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
