我将sql操作日志存储在表sync_logger中,并且我想定期将这些日志保存到常规文件中。为此,我尝试创建一个事件调度程序。但我总是在选择语句时出现以下错误。
MariaDB [sync]> DELIMITER !!
MariaDB [sync]> CREATE EVENT IF NOT EXISTS write_to_log_event
-> ON SCHEDULE
-> EVERY 1 DAY
-> DO
-> BEGIN
-> DECLARE log_file VARCHAR(255);
-> SET log_file = CONCAT('/var/log/sync_db_log_' ,DATE_FORMAT(NOW(), '%Y%m%d%H%i%S'));
-> SELECT * INTO OUTFILE log_file from sync_logger;
-> DELETE FROM sync_logger;
-> END;
-> !!
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 'log_file from sync_logger;
DELETE FROM sync_logger;
END' at line 8
MariaDB [sync]> delimiter ;
我不知道是什么问题。有人可以帮忙吗?
输出文件的名称是文字,而不是参数,并且不会替换为变量值。此外,文件名必须加引号。
使用准备好的语句:
CREATE EVENT IF NOT EXISTS write_to_log_event
ON SCHEDULE
EVERY 1 DAY
DO
BEGIN
SET @write_to_log_event_sql := CONCAT('SELECT * INTO OUTFILE ''/var/log/sync_db_log_' ,
DATE_FORMAT(NOW(), '%Y%m%d%H%i%S'),
''' FROM sync_logger');
-- SELECT @write_to_log_event_sql; -- for debugging purposes
PREPARE stmt FROM @write_to_log_event_sql;
EXECUTE stmt;
DROP PREPARE stmt;
TRUNCATE sync_logger;
END
PS。TRUNCATE比DELETE快。
有用。非常感谢你。