创建存储过程，从数据库中的所有表中删除30天以上的日期列中的行

Question

可能重复：

procedure in mysql

请帮助我创建一个存储过程，从mysql数据库中的所有表中删除30天以上的日期列中的行。

Answer 1

你可以这样做：

DELETE FROM MyTable
WHERE MyTable.MyDate < SUBDATE(@NOW, 30)

Answer 2

delimiter //
create procedure delete_data(schemata varchar(25))
Begin
DECLARE  tab_name varchar(64);
DECLARE  done TINYINT DEFAULT 0;

DECLARE table_name_cur  cursor for select table_name from information_schema.TABLES where table_schema=schemata;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


  OPEN  table_name_cur;
  read_loop: LOOP
    FETCH  table_name_cur INTO tab_name;

    IF done THEN LEAVE read_loop; END IF;
   --     delete from tab_name where  PRS_DATE <  now() - interval 1 month;

set @x =concat('delete from ', tab_name,' where ','PRS_DATE \< ', 'now() - interval 1 month'); 
   select @x;    
    prepare stmt2 from @x;
        execute stmt2;
    deallocate prepare stmt2; 

    END LOOP;
  CLOSE table_name_cur;
END //
delimiter ;