MySQL Export to CSV with Column Headings and Dynamic Filename

README.md

Export a MySQL Query's Results to CSV with Column Headings and a Dynamic Filename

If we want to generate a CSV file directly from MySQL with column headings as the first row and a filename generated dynamically in the script, there are a few caveats which this snippet covers. These are described in full in long.sql amongst the comments and references.

• long.sql - The script with all the comments and explanation of what/why.
• short.sql - The script that has no comments and is as brief as possible to be used as a template.

Usage

1. Copy short.sql to a new .sql file in a location that is readable by the user running the MySQL process.
2. In your new file:
   1. Update the variables/configuration as required.
   2. Create/insert your main query in the appropriate location (section 3).
3. Execute the new file from MySQL:
   1. Ensure you are useing the correct schema.
   2. Optionally specify an output directory by setting the session variable @output_dir.
      • Must be writable by the user running the MySQL process.
      • Defaults to /var/lib/mysql-files.
   3. Set any other session variables you intend to use in the SQL script.
   4. source the script - note that quotes should not be used for the parameter, unlike with the paths in the variable declarations.

mysql> source /path/to/new/script.sql;

OR

mysql> set @output_dir := '/path/to/desired/output/folder';
mysql> source /path/to/new/script.sql;

You can of course, just execute the .sql file by any other valid means.

Empty fields

For fields that are nullable and are currently empty, MySQL will insert "\N" (NULL) as the value.

To customize that value, when selecting columns check for the existence of the column's value and return a default value if it is currently empty, e.g.:

SET @main_query := "
  SELECT
    IF( column_one IS NULL, 'Default Value', column_one ),
    IF( column_two IS NULL, 'Default Value', column_two ),
    ...
";

Additional Variables

You can always call upon other customer session variables inside the .sql file, and set them on a per-use basis.

For example, we could set these variables before executing the file:

mysql> set @report_start := '2018-12-01';
mysql> set @report_end := '2018-12-31';
mysql> source /path/to/new/script.sql;

To control the filename and/or main query in the .sql file:

...

SET @output_file := CONCAT( "my_report_", @report_start, "_to_", @report_end );

...

SET @main_query := CONCAT(
    "SELECT ... ",
    "FROM ... ",
    "WHERE date_col >= '", @report_start, "' ",
    "AND date_col <= '", @report_end, "' "
);

...

long.sql

/**
 * Basic ideas/reasons:
 *
 *   In order to have a dynamic filename and "interpolate" a variable we must
 *   build the query as a string, leveraging functions such as "CONCAT", and
 *   execute it as a prepared statement:
 *   - https://stackoverflow.com/a/13549427/3389737
 *   - https://stackoverflow.com/a/11338469/3389737
 *   - https://lists.mysql.com/mysql/226921
 *
 *   To include headings with the resulting CSV, we must union two queries -
 *   one that selects our headings and the other is the actual query you are
 *   using to produce the CSV:
 *   - https://stackoverflow.com/a/25205752/3389737
 *   - https://stackoverflow.com/a/25205752/3389737
 *
 *   For the sake of readability the queries (strings) are separated into three:
 *   - The headings query (2)
 *   - The main query (whatever you're using to generate the CSV) (3)
 *   - The outer query unioning the previous two and defining the CSV (4)
 */

--------------------------------------------------------------------------------

/**
 * (1)  Configuration for our new CSV file.
 */

-- The default output directory:
-- - No trailing slash pls.
-- - The user running the MySQL process must have write permissions here.
-- - Used if the session variable for output directory is not already set.
-- - "/var/lib/mysql-files" is the default writable directory for Ubuntu 16.04
--   with the "--secure-file-prev" option. (https://stackoverflow.com/a/47515369/3389737).
SET @default_output_dir := "/var/lib/mysql-files";

-- The "real" output directory
-- - If the session variable already exists, use that.
-- - If not, use the default specified above.
SET @output_dir := IF( @output_dir IS NULL, @default_output_dir, @output_dir );

-- Generate the dynamic filename as per your requirements.
-- - Using other session variables, MySQL functions, for example.
-- - No need to include the file extension.
-- - e.g. for a name with the current date and time:
SET @output_file := CONCAT( "my_report_", DATE_FORMAT( NOW(), "%Y-%m-%d_%k%i%s" ) );

-- The characters used to construct the CSV string.
-- - I believe the default behaviour is to produce a tab separated CSV string so
--   if you want the traditional CSV experience you must be explicit (4.2).
SET @csv_enclose_char         := """";
SET @csv_field_terminate_char := ",";
SET @csv_line_terminate_char  := "\n";

--------------------------------------------------------------------------------

/**
 * (2)  The CSV Headings Query.
 */

-- Simply "select" all of the headings:
-- - Must match the column selection order in your @main_query below (3).
-- - No need for a terminating symbol (";", "\G", etc.), this will be executed
--   as a subquery in our prepared statement below (5).
SET @headings_query := "
  SELECT
    'Column One',
    'Column Two',
    'Column Three',
    'Column Four'
";

--------------------------------------------------------------------------------

/**
 * (3)  The main CSV-generating Query.
 *
 * - Each row in the result set is equivalent to a row in the CSV.
 * - The order the columns are selected must match the headings above (2).
 * - As above, no need for a terminating symbol (";", "\G", etc.), this will
 *   be executed as a subquery in our prepared statement below (5).
 * - Any aliases go unused for the CSV (i.e. "SELECT x AS y" is pointless),
 *   hence our need to union this query with the @headings_query above (2).
 */

SET @main_query := "
  SELECT
    `column_one`,
    `column_two`,
    `column_three`,
    `column_four`
  FROM ...
  JOIN ...
  WHERE ...
  ORDER BY ...
";

--------------------------------------------------------------------------------

/**
 * (4)  The Outer Union/CSV Query.
 *
 * - For some situations, we could simply union both queries and be done:
 *   - https://stackoverflow.com/a/5941905/3389737
 * - However, for queries where order is important, we must use the code
 *   described at the previous link as a subquery and select our rows from that
 *   combined result set:
 *   - (4.1) https://stackoverflow.com/a/25205752/3389737
 * - Pipes results into a CSV file using `INTO OUTFILE`:
 *   - (4.2) https://stackoverflow.com/a/356605/3389737
 * - More superfluous formatting here and unnecessary CONCAT parameters :) just
 *   to make the query structure more obvious here.
 */

SET @final_query := CONCAT(

  -- (4.1)
  "SELECT * FROM (",
    "(", @headings_query, ")",
    "UNION ALL",
    "(", @main_query, ")",
  ") `result_set_alias__anything_will_do` ",

  -- (4.2)
  "INTO OUTFILE '", @output_dir, "/", @output_file, ".csv' ",
  "FIELDS TERMINATED BY '", @csv_field_terminate_char, "' ",
  "ENCLOSED BY '", @csv_enclose_char, "' ",
  "LINES TERMINATED BY '", @csv_line_terminate_char, "';"

);

--------------------------------------------------------------------------------

/**
 * (5)  Execute the Final Query
 */

PREPARE stmt FROM @final_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

--------------------------------------------------------------------------------

/**
 * (6)  Cleanup
 *
 * Optional, a personal preference. "Destroy" the session variables we used in
 * the script above, so we don't run in to any problems arising from naming
 * conflicts with your other MySQL activites in this session. You might want
 * some of these values to persist beyond execution of this script.
 */

SET @default_output_dir := NULL;
SET @output_dir := NULL;
SET @output_file := NULL;
SET @csv_enclose_char := NULL;
SET @csv_field_terminate_char := NULL;
SET @csv_line_terminate_char := NULL;
SET @headings_query := NULL;
SET @main_query := NULL;
SET @final_query := NULL;

short.sql

SET @output_dir  := IF( @output_dir IS NULL, "/var/lib/mysql-files", @output_dir );
SET @output_file := CONCAT( "my_report_", DATE_FORMAT( NOW(), "%Y-%m-%d_%k%i%s" ) );

SET @csv_enclose_char         := """";
SET @csv_field_terminate_char := ",";
SET @csv_line_terminate_char  := "\n";

SET @headings_query := "
  SELECT
    'Column One',
    'Column Two',
    'Column Three',
    'Column Four'
";

SET @main_query := "
  SELECT
    `column_one`,
    `column_two`,
    `column_three`,
    `column_four`
  FROM ...
  JOIN ...
  WHERE ...
  ORDER BY ...
";

SET @final_query := CONCAT(
  "SELECT * FROM ((", @headings_query, ") UNION ALL (", @main_query, ")) `res` ",
  "INTO OUTFILE '", @output_dir, "/", @output_file, ".csv' ",
  "FIELDS TERMINATED BY '", @csv_field_terminate_char, "' ",
  "ENCLOSED BY '", @csv_enclose_char, "' ",
  "LINES TERMINATED BY '", @csv_line_terminate_char, "';"
);

PREPARE `stmt` FROM @final_query;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

SET @default_output_dir := NULL;
SET @output_dir := NULL;
SET @output_file := NULL;
SET @csv_enclose_char := NULL;
SET @csv_field_terminate_char := NULL;
SET @csv_line_terminate_char := NULL;
SET @headings_query := NULL;
SET @main_query := NULL;
SET @final_query := NULL;