Creating a csv file in mysql with current date

Question

Creating a csv file in mysql with current date

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

How can data from mysql be exported to a file cvs with date and time of day? I try this code but I get an error. I'm trying to do a evento

My Event

CREATE DEFINER = 'admin'@'localhost' EVENT 'Respaldo Ordenes' 
ON SCHEDULE EVERY1 MONTH STARTS '2018-12-01 00:00:00' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Respaldo de Ordenes cada fin de mes' 
DO SELECT *
FROM Order
INTO OUTFILE '/tmp/_orders.csv'

But I want to add the date so that it does not duplicate with the same name

SELECT *
FROM order
INTO OUTFILE '/tmp/',NOW(),'_orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

error

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

Investigating I found this code

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

But if I add it to the event it throws me this error

mysql

asked by MoteCL 09.11.2018 в 18:54

source

3 answers

0

try this way:

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');

SET @FOLDER = 'c:/tmp/';
SET @PREFIX = 'order';
SET @EXT    = '.csv';

SET @CMD = CONCAT("SELECT * FROM order INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
"' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
"  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;

To get the file out, you must have created the tmp folder in the root of the local disk C

answered by 09.11.2018 в 19:35

0

You can achieve this by using BEGIN and END in the following way:

CREATE EVENT 'Respaldo Ordenes' ON SCHEDULE EVERY 1 MONTH 
STARTS '2018-12-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Respaldo de Ordenes cada fin de mes' 
DO BEGIN

    SET @stmt := CONCAT("SELECT * FROM Order INTO OUTFILE '/tmp/", NOW(), "_orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'");
    PREPARE qry FROM @sql_stmt;
    EXECUTE qry;
    DEALLOCATE PREPARE qry;

END

answered by 12.11.2018 в 17:04

Problems trimming bakcground image Problem with requiered tag

score 1 · Accepted Answer

You can do it by executing dynamic SQL within the event, like this:

DELIMITER $$
CREATE DEFINER = 'admin'@'localhost' EVENT 'Respaldo Ordenes'
ON SCHEDULE
EVERY 1 MONTH
STARTS '2018-12-01 00:00:00' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Respaldo de Ordenes cada fin de mes' 
DO
BEGIN
    SET @sql_stmt := concat("SELECT * FROM Order INTO OUTFILE '/tmp/", DATE_FORMAT(now(),'%Y-%m-%d %H%i%s'), "_orders.csv'");
        PREPARE extrct FROM @sql_stmt;
        EXECUTE extrct;
        DEALLOCATE PREPARE extrct;
END $$
DELIMITER ;