如何在mysql中将表导出成不同名称的平面文件？ [英] how to export table into flat file with different name in mysql?

问题描述

我正在使用 -

SELECT * INTO OUTFILE 'd:\\result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY  '\n'
FROM result;

现在，我想通过放在程序中每天运行这个脚本，我想使用文件名result，其中包含日期或其他动态数字，我可以每天增加它会给我错误的重复文件名。

Now I want to run this script daily by putting it in procedure. And I want use file name "result" with date or some other dynamic number which i can increase daily. Otherwise it will give me error for the duplicate file name.

我应该像 -

declare var_result varchar(100);
set var_result = 'result01012014';
SELECT * INTO OUTFILE var_result
................................

是否可能

推荐答案

而不是创建存储过程您可以创建一个 shell或php 脚本相同的

Instead of creating Stored Procedure You can create a shell or php script for the same

我通常使用的shell脚本的示例

Example of a shell script that I usually use

#!/bin/bash

location="/home/abdul/Desktop"
user="root"
pass="root"
table="City"
database="world_innodb"

mysql -u$user -p$pass --column-names -e "SELECT * FROM $database.$table;" | sed 's/\t/"|"/g;s/^/"/;s/$/"/;s/\n//g' > $location/$table-$(date +"%Y-%m-%d").csv

这将创建文件名，如 City-2014-02-20.csv

存储过程相同的

DROP PROCEDURE IF EXISTS ExportToCSV;

DELIMITER $$

CREATE PROCEDURE `ExportToCSV`()

    BEGIN
    SET @cur_date =  CURRENT_DATE();
    SET @path = "/tmp/";
    SET @table_name =  "City";

    SET @VarSQL = CONCAT('SELECT * INTO OUTFILE  \'' ,@path,@table_name,'-',@cur_date,'.csv\' FIELDS TERMINATED BY ''\,','\' OPTIONALLY ENCLOSED BY \'','"' ,'\' LINES TERMINATED BY \'','\\n','\' FROM ',@table_name,';' );

    PREPARE stmt FROM @VarSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    END$$

DELIMITER ;

这篇关于如何在mysql中将表导出成不同名称的平面文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！