SQL MariaDB-为每个数据库中的每个表循环此查询，但有例外

Question

我有大型数据库，我使用下面的查询每月对其进行修剪。然而，我有数百个表，并希望将其作为数据库中每个表的foreach循环。

我知道如何在PHP中实现这个循环，但如果能在纯SQL中直接作为数据库上的查询运行，那就太好了。

前缀为noprune的表(如noprune_master_tableZ(需要排除在循环之外。

我从哪里开始？感谢

START TRANSACTION;
set @N := (now());
CREATE TABLE master_tableA_snapshot AS SELECT * FROM master_tableA where insertDATE < date_sub(@N,INTERVAL 32 DAY);
SELECT * from master_tableA_snapshot INTO OUTFILE '/tmp/master_tableA_snapshot_TODAYSDATE.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';
DELETE from master_tableA where insertDATE < date_sub(@N,INTERVAL 32 DAY);
DROP TABLE master_tableA_snapshot;
COMMIT;

Answer 1

实际上只有两个语句的辅助变量简化为：

set @N := date_sub(NOW(),INTERVAL 32 DAY);
SELECT *
FROM master_tableA
WHERE insertDATE < @N
INTO OUTFILE '/tmp/master_tableA_snapshot_TODAYSDATE.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';
DELETE from master_tableA where insertDATE < @N;

PHP通过查询选择表名：

SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE SCHEMA='mydatabase'
AND TABLE_NAME NOT LIKE 'noprune%';