I'm trying to execute a query and export its results to a csv file with a formatted current_timestamp
in its file name.
INTO OUTFILE
and onwards) executes just fine.INTO OUTFILE
it exports to csv just fine i.e. making INTO OUTFILE 'C:\path\to\file.csv'
.SELECT CONCAT(..)
function and its parameters in another query window, I get a positive looking string returned so I know the concat function is fine, it produces C:\ProgramData\MySQL\MySQL Server 5.6\Uploads\2020-10-20_043918.csv
.I feel like INTO OUTFILE
annoyingly doesn't expect anything other than a simple string, hopefully I'm wrong though. Does anyone have any insight to this?
SELECT
COUNT(*) AS AgentCount,
clients.ClientId,
clients.Name AS ClientName,
computers.RouterAddress
FROM
computers
LEFT JOIN
clients
ON
computers.ClientId = clients.ClientId
GROUP BY
computers.RouterAddress
ORDER BY
AgentCount DESC
INTO OUTFILE
CONCAT('C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Uploads\\', DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d_%H%i%s'), '.csv')
FIELDS TERMINATED BY
','
ENCLOSED BY
'"'
LINES TERMINATED BY
'\r\n'
select ... into outfile
does not support variables, as you are finding out. What you ask for requires dynamic SQL:
SET @sql = CONCAT_WS('\r\n',
'SELECT',
' COUNT(*) AS AgentCount,',
' clients.ClientId,',
' clients.Name AS ClientName,',
' computers.RouterAddress',
'FROM',
' computers',
'LEFT JOIN',
' clients',
'ON',
' computers.ClientId = clients.ClientId',
'GROUP BY',
' computers.RouterAddress',
'ORDER BY',
' AgentCount DESC',
CONCAT('INTO OUTFILE \'', TRIM(BOTH '\'' FROM QUOTE(@@secure_file_priv)), DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d_%H%i%s'), '.csv\''),
'FIELDS TERMINATED BY \',\'',
'ENCLOSED BY \'\"\'',
'LINES TERMINATED BY \'\\r\\n\'');
PREPARE statement FROM @sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;