Bug #13805 import numeric fields with missing data
Submitted: 6 Oct 2005 14:52 Modified: 6 Oct 2005 17:41
Reporter: Sylvain Smith Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:
Assigned to: MySQL Verification Team CPU Architecture:Any

[6 Oct 2005 14:52] Sylvain Smith
Description:
Good evening,

I have a problem when importing numeric data with MySql. The database is managed thanks to PhpMyAdmin. I import the data from csv files. In the database, there are numerous numeric fields, with Null authorized and default value=Null. In the csv files, many of these numeric fields are often null (missing data). The problem is that when I import these csv files to the database, the numeric missing data are replaced by 0 instead of null. This problem is quite serious for us, as we can not distinguish a real 0 value from a missing data.

How can I solve this problem?
Tanhk you for your help.

How to repeat:
[6 Oct 2005 17:41] MySQL Verification Team
I think this problem isn't with the server and instead how the
data file was created. Please see:

http://dev.mysql.com/doc/mysql/en/problems-with-null.html

When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances. See Section 13.2.5, “LOAD DATA INFILE Syntax”.
[7 Oct 2005 7:00] Sylvain Smith
Thank you very much for your answer. I tried to replace '' with \N and the fields in the database were correctly replaced by Null values. Thank you again.