我一直试图用可变文件名将csv正确导出到我的测试文件夹中——我已经接近了,但我认为在它工作之前,我已经解决了一个问题。这是迄今为止的代码:
SET @q1 := CONCAT(
"SELECT *
FROM table
INTO OUTFILE '/SQLOut/test"
, DATE_FORMAT(NOW(),'%Y%m%d')
,".csv'"
, "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'n'");
PREPARE s1 FROM @q1;
EXECUTE s1;
DROP PREPARE s1;
如您所见,我必须使用prepared statements
才能获得包含今天日期的文件名。这个部分是有效的——但如果我想让它成为csv,我需要在之后包括"字段终止"的东西,我认为这就是破坏它的原因
我相信,如果OPTIONALLY ENCLOSED BY '"'
不包含"
,它后面的双引号会在CONCAT(
之后关闭我原来的"
,'LINES TERMINATED BY 'n'");
的最后一行会被切断,MySQL也会被混淆。
还有别的办法吗?
尝试:
SET @`qry` := CONCAT('
SELECT *
FROM `table`
INTO OUTFILE '/SQLOut/test', DATE_FORMAT(NOW(), '%Y%m%d'), '.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
');
PREPARE `stmt` FROM @`qry`;
EXECUTE `stmt`;
DROP PREPARE `stmt`;