List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 2 2001 3:45pm
Subject:Re: 0000-00-00 vs NULL in Date Field
View as plain text  
At 9:06 AM -0400 10/2/01, Frank J. Schmuck wrote:
>If I read a file into a database with a \N in an empty date field the field
>is populated with 0000-00-00.  If I insert a row into the same database with
>NULL in the date field it is populated with NULL.
>
>Programmatically is there a difference between the two?

If you're using LOAD DATA to read the file with \N, you should get a
NULL in the field.

Here's a test data file:

\N
1999-12-31

Here's my set of test statements:

mysql> create table t (d date);
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile 'junk' into table t;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+------------+
| d          |
+------------+
| NULL       |
| 1999-12-31 |
+------------+
2 rows in set (0.00 sec)


If the DATE column had been declared NOT NULL, then the \N turns into
0000-00-00 instead.  But it sounds from your description that the column
does allow NULL.


>
>Thanks
>Frank


-- 
Paul DuBois, paul@stripped
Thread
Multi-Row InsertGlendon Solsberry2 Oct
  • 0000-00-00 vs NULL in Date FieldFrank J. Schmuck2 Oct
    • Re: 0000-00-00 vs NULL in Date FieldPaul DuBois2 Oct
    • RE: 0000-00-00 vs NULL in Date FieldCarsten H. Pedersen2 Oct
      • RE: 0000-00-00 vs NULL in Date FieldFrank J. Schmuck2 Oct
        • RE: 0000-00-00 vs NULL in Date FieldPaul DuBois2 Oct
Re: Multi-Row Insertalec.cawley2 Oct