title: How do I handle NULL values in a mysql SELECT ... OUTFILE statement in conjunction with FIELDS ESCAPED BY? NULL values are currently being truncated
body: <p>I'm encountering some difficulties using MySQL's SELECT ... OUTFILE on result sets that include both null values and columns that require double quote escaping (ie, columns that contain '"' characters).  This is the outfile syntax I am using:</p>
  
  <pre><code>INTO OUTFILE '$csv_file' 
  FIELDS ESCAPED BY '""' TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
  </code></pre>
  
  <p>My problem is concerning the FIELDS ESCAPED BY portion of the query - if this portion is omitted, then null values will export properly (...,"\N",... is what it looks like in the csv).</p>
  
  <p>However, columns that contain double quotes will get split across multiple lines/columns in excel.  This is because excel requires that '"' characters inside columns to be escaped by writing them as '""'.  </p>
  
  <p>Including the FIELDS ESCAPED BY clause fixes the excel problem with columns containing double quote characters, however, it breaks NULL columns.  NULL columns get exported as ( ..."N,... ) missing both the backslash and the trailing quotation mark on the column.  In excel, this causes multiple columns to collapse into each other due to the lack of a closing quotation.</p>
  
  <p>My goal is to be able to export columns that contain double quotes and newlines, as well as export null columns as \N, however I can't seem to figure out how to do it.  MySQL docs state that FIELDS ESCAPED BY affects how NULL columns are outputted, but I can't figure out how an escape sequence of '""' results in dropping the backslash and the trailing quote on a NULL column</p>
  
  <p>Currently, my solution is to perform a string replace on each line as I output it to the user, by using FIELDS ESCAPED BY and replacing '"N,' with '"\N",'.   This seems to work, but it doesn't feel right, and I'm afraid of it causing some sort of issues down the line</p>
  
  <p>IFNULL( ) on the select columns is potentially an option, but the way we are using this in our code, is actually quite difficult to implement.  It also needs to be done for each column that could potentially have NULL values, so it's a solution I'd like to avoid if I can</p>
  
  <p>Thanks!</p>