How MySQL evaluates if we export the data to CSV file from a table which contains a NULL value(s)?

MySQLMySQLi Database

If we export the data from a table having NULL values then MySQL will store \N in CSV file for the record MySQL table having NULL values. It can be illustrated with the help of the following example −

Example

Suppose if we want to export the values of the table ‘student_info’ having the following data −

mysql> Select * from Student_info;
+------+---------+------------+------------+
| id   | Name    | Address    | Subject    |
+------+---------+------------+------------+
| 101  | YashPal | Amritsar   | History    |
| 105  | Gaurav  | Chandigarh | Literature |
| 125  | Raman   | Shimla     | Computers  |
| 130  | Ram     | Jhansi     | Computers  |
| 132  | Shyam   | Chandigarh | Economics  |
| 133  | Mohan   | Delhi      | Computers  |
| 150  | Saurabh | NULL       | Literature |
+------+---------+------------+------------+
7 rows in set (0.00 sec)

As we can see that the result has a NULL value for the address field where id is 150. Now the following query will export this table’s data into Student_27.CSV −

mysql> Select * from Student_info INTO OUTFILE 'C:/mysql/bin/mysql-files/student_27.csv' FIELDS TERMINATED BY ',';
Query OK, 7 rows affected (0.02 sec)

The above query has stored following values in the file Student_27.CSV −

101   YashPal   Amritsar     History
105   Gaurav    Chandigarh   Literature
125   Raman     Shimla       Computers
130   Ram       Jhansi       Computers
132   Shyam     Chandigarh   Economics
133   Mohan     Delhi        Computers
150   Saurabh   \N           Literature

We can see that MySQL stores \N where the table has a NULL value(s).

raja
Published on 20-Feb-2018 09:48:15
Advertisements