Export a query result to a file

from the Artful MySQL Tips List


SELECT ... INTO OUTFILE | DUMPFILE ... exports a query result to a file on the server, but it demands a literal filename. What if you need a generic method that accepts the query and filename as variables? PREPARE the command, eg to save the content of a text column in one row of murphy(id INT PRIMARY KEY, law TEXT) ...
SET @id=100;
SET @f = 'saying.txt';
SET @sql = CONCAT( 'SELECT law FROM murphy WHERE id=',@id, 
                   ' INTO DUMPFILE',' ',CHAR(39),@f,CHAR(39) );
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
What we do once, we often need to do again. It's easy to port this code to a stored procedure ...
DROP PROCEDURE IF EXISTS export;
DELIMITER |
CREATE PROCEDURE export( baseqry CHAR(255), id INT, fname CHAR(128) )
BEGIN
  SET @sql = CONCAT( baseqry, ' WHERE id=', id, ' INTO DUMPFILE',
                     ' ', CHAR(39), fname, CHAR(39) );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DROP PREPARE stmt;
END;
|
DELIMITER ;

Last updated 22 May 2009


Return to the Artful MySQL Tips page