I was wondering the other day how can someone export a certain result set, containing calculated or formatted fields directly from MySQL. I did my quick research and discovered the SELECT … INTO OUTFILE statement.
However, I was missing the complete parameter documentation. So, one Stack Overflow question later, turns out that the export options are the FIELDS and LINES as specified in the LOAD DATA INFILE syntax.
Let’s move to an example.
Create a new table:
CREATE TABLE <code>foo</code>(<code> id</code>INT AUTO_INCREMENT PRIMARY KEY, description VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Let’s insert a few rows as well:
INSERT INTO foo(description) VALUES('A small piece of text. It\'s the first row.'), ('A second piece of text. It has some "double quotes"');
… and let’s say we execute the following query:
SELECT id, description, DATE(created_at) AS date_created_at FROM foo;
The goal is to convert the result set to
INSERT statements. Now using SELECT … INTO OUTFILE:
SELECT id, description, DATE(created_at) INTO OUTFILE '/tmp/devopslog.out' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES STARTING BY '(' TERMINATED BY '),\n' FROM foo;
I used the
DATE function to underline the use of calculated fields, if necessary. Also, make sure the path you are writing to is writable by the mysql user. Open the /tmp/devopslog.out file in your editor, you should probably see something like this:
("1","A small piece of text. It's the first row.","2012-02-05"), ("2","A second piece of text. It has some \"double quotes\"","2012-02-05"),
We need to get the corresponding
INSERT statement as well. This requires a little help from the quite convenient INFORMATION_SCHEMA database.
SELECT TRIM(CONCAT("INSERT INTO foo(", (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'foo' AND TABLE_SCHEMA = 'test'), ") VALUES")) INTO OUTFILE '/tmp/devopslog-header.out' FIELDS ENCLOSED BY '' TERMINATED BY '' LINES TERMINATED BY '' ;
Now it’s time to quit the MySQL client run a couple of commands to put it all together:
# we remove any whitespace from the end of the INSERT statement (optional) sed 's/ *$//g' /tmp/devopslog-header.out > /tmp/devopslog-export.sql # the following is just to replace the comma of the last line with a semi-colon head -n -1 /tmp/devopslog.out >> /tmp/devopslog-export.sql tail -n 1 /tmp/devopslog.out | sed 's/,$/;/' >> /tmp/devopslog-export.sql
… and you are ready to go. If you don’t want to leave the MySQL client you can use the system command (or \!) to execute the above, provided that you use Linux of course.
There are a few problems/limitations that I think of though.
- Your whole file must not actually exceed the max_allowed_packet system variable for the server that you’ll be running the SQL file on. This is configurable though, check the documentation.
- I am not entirely sure about what happens to hex/blob fields.