List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 2 2001 6:04pm
Subject:RE: 0000-00-00 vs NULL in Date Field
View as plain text  
>Here's what I did:
>
>mysql> create database testnull;
>Query OK, 1 row affected (0.05 sec)
>
>mysql> use testnull;
>Database changed
>
>mysql> create table info (birth date);
>Query OK, 0 rows affected (0.03 sec)
>
>mysql> show tables;
>+--------------------+
>| Tables_in_testnull |
>+--------------------+
>| info               |
>+--------------------+
>1 row in set (0.00 sec)
>
>mysql> describe info;
>+-------+------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+------+------+-----+---------+-------+
>| birth | date | YES  |     | NULL    |       |
>+-------+------+------+-----+---------+-------+
>1 row in set (0.01 sec)
>
>mysql> describe info;
>+-------+------+------+-----+---------+-------+
>| Field    | Type  | Null   | Key  | Default   | Extra   |
>+-------+------+------+-----+---------+-------+
>| birth     | date   | YES   |         | NULL   |            |
>| death    | date   | YES  |         | NULL    |           |
>+-------+------+------+-----+---------+-------+
>2 rows in set (0.00 sec)

How come two identical commands show different results here?



>
>mysql> load data local infile "date.txt" into table info;
>Query OK, 4 rows affected (0.01 sec)
>Records: 4  Deleted: 0  Skipped: 0  Warnings: 1
>
>mysql> select * from info;
>+------------+------------+
>| birth             | death          |
>+------------+------------+
>| 1999-01-01 | 0000-00-00 |
>| 1990-05-21 | 0000-00-00 |
>| 1989-12-20 | 0000-00-00 |
>| 0000-00-00 | NULL         |
>+------------+------------+
>4 rows in set (0.00 sec)
>
>date.txt is as follows:
>
>1999-01-01	/N                     (date followed by tab followed by /N)
>1990-05-21	NULL                (date followed by tab followed by NULL)
>1989-12-20 (date followed by tab with nothing beyond)
>(single tab - no data)
>
>
>Any idea of why this works as it does?


Because you used /N rather than \N ...


-- 
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