如何将数据从mysql
导出到具有日期和时间的文件csv
。我尝试了这个代码,但我得到了一个错误
SELECT *
FROM order
INTO OUTFILE '/tmp/',NOW(),'_orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n';
这是我的错误
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' ,NOW(), '"'_orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TER' at line 3
这是我的活动工作很棒,但我希望每个月底都有不同的简历
CREATE DEFINER = `admin`@`localhost` EVENT `Order`
ON SCHEDULE EVERY1 MONTH STARTS '2018-12-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Order save'
DO SELECT *
FROM Order
INTO OUTFILE '/tmp/_orders.csv'
这里的查询得到了带有日期的名称,但我不能在事件中添加这个
SET @`outfull` := CONCAT('/tmp/', NOW(), 'orders.csv');
SET @`qry` := CONCAT('SELECT *
INTO OUTFILE '', @`outfull`, ''
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
FROM `order`');
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
尝试准备好的语句:
set @sql = concat('SELECT *
FROM tradooITLangues
INTO OUTFILE ', concat(''', '/tmp/', NOW(), '_orders.csv', '''),
' FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '#n';');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
在一个事件中(我的MySQL接受了语法(:
delimiter $$
CREATE DEFINER = `admin`@`localhost` EVENT `Order`
ON SCHEDULE EVERY 1 MONTH STARTS '2018-12-01 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Order save'
DO
BEGIN
SET @`outfull` := CONCAT('/tmp/', NOW(), 'orders.csv');
SET @`qry` := CONCAT('SELECT *
INTO OUTFILE '', @`outfull`, ''
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
FROM `order`');
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
END;
$$