使用当前mysql创建csv文件

byqmnocz 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(410)

如何从中导出数据 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`;

mysql csv into-outfile

来源：https://stackoverflow.com/questions/53229433/creating-csv-file-with-current-day-mysql

1条答案

按热度按时间

dvtswwa31#

试一试准备好的语句：

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;
$$

赞(0）回复(0）举报 2021-06-18

我来回答

使用当前mysql创建csv文件

1条答案

相关问题

热门标签

最新问答