MySQL export to .csv – Alan’s Notes

It took a while, review of a number of weblogs, support sites, and error message causes but export from MySQL to .csv with the column titles is finally working!

SELECT convert('Title' using utf8mb4) 'Title',
	convert('Year' using utf8mb4) 'Year',
	convert('Time' using utf8mb4) 'Time',
	convert('Rating' using utf8mb4) 'Rating',
	convert('Genre' using utf8mb4) 'Genre'  
UNION ALL
SELECT * from 
	(SELECT * from movies_i_want order by Title) a
INTO OUTFILE '/var/lib/mysql-files/movies_i_want.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"';

and the file it produces…

"Title","Year","Time","Rating","Genre"
"Amazon Women on the Moon","1987","85","R","Comedy,Sci-Fi"
"Blade of the Immortal","2017","140","R","Action,Drama"
"Bound","1996","108","NR","Crime,Romance,Thriller"
 ...

The first SELECT above produces a single row table with the column’s titles as the field values in the row. The table is not named.

mysql> SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre';
+-------+------+------+--------+-------+
| Title | Year | Time | Rating | Genre |
+-------+------+------+--------+-------+
| Title | Year | Time | Rating | Genre |
+-------+------+------+--------+-------+

The full SELECT expression uses convert('Title' using utf8mb4) 'Title'… for each field because UNION failed from incompatible collations between the first SELECT’s row and the “SELECT ... movies_i_want” statement.

Collations can be associated with one character set. A character set can be associated with many collations. For collations to match, both character sets must match. The purpose of the first SELECT is to emit the column titles for the .csv export. It produces only one row, the column titles. Converting that SELECT’s character set to match the character set used in the live data table permitted the collation the UNION was doing to work.

Once the collations were compatible it was necessary to get each column’s title into the output as the first row of the output. The view used for the second SELECT has the same column titles in the same order as the first select. Its data needs to be the 2nd and subsequent rows.

After many attempts, several ways to state the problem were found. Unfortunately none included the column titles in the output. The column titles are a must have for free wpDataTables to show headings on the tables it displays from reading the .csv files.

ignoring the comment about collation to simplify the statements...
The following both work except for no column titles

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT * 
FROM movies_i_want order by Title;

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre' 
FROM movies_i_want order by Title;

To include the column titles in the output three SELECTs are needed. And be sure to include an alias for the ‘derived table’, the one created by the second SELECT.

SELECT 'Title', 'Year', 'Time', 'Rating', 'Genre'  
UNION ALL
SELECT * FROM (SELECT * FROM movies_i_want order by Title) a;

Now the output can be created in the necessary format using native MySQL. The method is a bit dense but I think simpler than the PHP, Use PHP to export MySQL to .csv.

With this and the PHP method of exporting the data a trigger is need to cause it to happen, either method can run on demand, or either method can run on a schedule.

A trigger, in other words “only when needed”, is my preferred approach. MySQL can be triggered by INSERT, UPDATE, and DELETE.

Solutions:

A MySQL trigger runs a SQL user procedure to export the data.
Problem: export destinations are limited to a single location with very restrictive access and it is not the location where either .csv file for the web tables is.

A MySQL trigger runs an external program, the PHP, to export the data.
Problem: can a trigger launch an external program?

Leave a Reply Cancel reply