mysql - 从所有以 raw_% 开头的表中删除超过 2 个月的旧日志

Question

我得到了 mysql 数据库，其中包含带有来自其他系统的日志的表。每个系统都有自己的表，我想自动删除超过2个月的日志。我几乎明白了，但在准备语句时出现错误。

这就是我得到的:

-- Set up variable to delete ALL tables starting with 'raw_'
SET GROUP_CONCAT_MAX_LEN=100000;

SET @delStmt = (SELECT GROUP_CONCAT(CONCAT('DELETE FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` WHERE time < DATE(NOW() - INTERVAL 2 MONTH);') SEPARATOR ' ')
       FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = 'log_cmd'
        AND TABLE_NAME LIKE 'raw_%');

-- SELECT @delStmt;

PREPARE stmt FROM @delStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我得到的错误是:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE FROM `log_cmd`.`raw_10203040` WHERE time < DATE(NOW() - INTERVAL 2 MONTH)' at line 1

不知道我做错了什么。提前致谢。

Answer 1

我找到了问题的解决方案。最简单的方法是将命令放入文件中并执行它。

SET GROUP_CONCAT_MAX_LEN=100000;

SET @delStmt = (SELECT GROUP_CONCAT(CONCAT('DELETE FROM `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` WHERE time < DATE(NOW() - INTERVAL 2 MONTH);') SEPARATOR ' ')
   FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = 'log_cmd'
    AND TABLE_NAME LIKE 'raw_%');

SELECT @delStmt INTO OUTFILE '/tmp/temp.sql';
SOURCE /tmp/temp.sql;

感谢帮助