Why do I get \N when executing INTO OUTFILE query?

I am connecting to a MySQL database using Java. I am executing this query:

    String query = 
        "SELECT * FROM TT2 " +
        "INNER JOIN " +
        "`Language Pref` " + 
        "ON tt2.msisdn =  `language pref`.MDN " +
            "INTO OUTFILE 'c:/test12226.csv' " + 
            "FIELDS TERMINATED BY ','  " +
            "ENCLOSED BY '\"'  " +
            "LINES TERMINATED BY '\n'";

I am executing this query using executeQuery(query).

The CSV file generated is perf开发者_高级运维ect except for the last column. In the original table called Language Pref the last column had some null or empty cells. In the CSV file, wherever there were these null cells in the original table language pref, I am getting "\N" instead of the null cells as should be.

How can I correct this?

If you want a '\' char in a String, you need to type '\\'.

Example:

System.out.print("The newline char \\n makes a new line: \n");

So in your example your last line should read:

"LINES TERMINATED BY '\\n'";

The string \N is used to represent a null value. So it's the right answer.

\n is completely different, that is a newline.

Well, ,\n at the end of a line represents a null-value in the last column. So everything is fine.

You can use MySQL IFNULL function: ifnull(fieldname, '') to avoid this.

This will put '' instead of NULL thus you'll get empty strings in your output file. The only problem is that you'll have to specify it individually for every columns.

NOTE: The '\N' seems to appear only when opened with Excel.