mysql存储过程样例

样例一：老表有三百万数据，需要将老表加上一个字段，但是因为，数据太大，新加字段失败，退而求其次，先建一个新表，使用select insert 导数据：

delimiter $$
drop procedure if exists wk;
create procedure wk()
begin
declare i int;
set i = 1;
while i < 11650 do
insert ignore into site_result_new (auto_id,site_id,redirct_url,host,title,keywords,description,site_type_l1,site_type_l2,site_type,update_time,crawler_flag,short_link,middle_link,long_link,pic_link_num,main_text,main_text_length,protpcol,crawler_status,other_site_link_num,crawler_exception,iframe_url,img_link,ip,ip_country,ip_province,shot_name,mark_add,mark_minus,mark_user) select auto_id,site_id,redirct_url,host,title,keywords,description,site_type_l1,site_type_l2,site_type,update_time,crawler_flag,short_link,middle_link,long_link,pic_link_num,main_text, main_text_length,protpcol,crawler_status,other_site_link_num,crawler_exception,iframe_url,img_link,ip,ip_country,ip_province,shot_name,mark_add,mark_minus,mark_user from site_result where auto_id between (i-1)*1000 and i*1000;
set i = i +1;
end while;
end $$

delimiter;
call wk();

某段数据导出

先查询出起止位置，min_id和max_id

循环次数=(max_id-min_id)/size

存储过程

begin
declare i int;
set i = 1;
while i < 1465 do
insert IGNORE into test.info_data_test  SELECT * from info_data where id between  257962863+(i-1)*5000 and 257962863+i*5000 and site_group=119;
set i = i +1;
end while;
end $$
 
delimiter;
call wk();

按照时间滚动存储

DELETE from test.data_sum ;
DELIMITER //
DROP PROCEDURE IF EXISTS daily_data_summary;
CREATE PROCEDURE daily_data_summary()
BEGIN
    DECLARE cur_date DATE;
    SET cur_date = '2023-11-19';

    WHILE cur_date < '2023-11-22' DO
        INSERT INTO test.data_sum (num, daytime)
        SELECT COUNT(1) AS num, IFNULL(DATE(pubtime), cur_date) AS daytime
        FROM news
        WHERE pubtime >= cur_date AND pubtime < DATE_ADD(cur_date, INTERVAL 1 DAY);

        SET cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY);
    END WHILE;
END //

DELIMITER ;

CALL daily_data_summary();
select * from test.data_sum ;