NULL ?

[Michael Widenius]

>>>>> "Darrell" == Darrell Shifflett <perl@stripped>
> writes:

Darrell> Was performing some simple tests for the use of NULL in a table.
Darrell> Can't get NULL to display, it only displays N ?
Darrell> I have a table called 'pet'

mysql> SELECT * FROM pet;
Darrell> +----------+---------+---------+------+------------+------------+
Darrell> | name     | owner   | species | sex  | birth      | death      |
Darrell> +----------+---------+---------+------+------------+------------+
Darrell> | Buffy    | Harold  | dog     | f    | 1989-05-13 | NULL       |
Darrell> | Bowser   | Diane   | dog     | m    | 1985-08-31 | 1995-07-29 |
Darrell> | Whistler | Gwen    | bird    | N    | 1997-12-09 | NULL       |
Darrell> | Slim     | Benny   | snake   | m    | 1996-04-29 | NULL       |
Darrell> +----------+---------+---------+------+------------+------------+
Darrell> The 'Whistler' name row under 'sex' is 'N' not 'NULL' ?
Darrell> I put this in pet.txt:
Darrell> __________________________________________________________
Darrell> Buffy   Harold  dog     f       1989-05-13      \N
Darrell> Bowser  Diane   dog     m       1998-08-31      1995-07-29
Darrell> Whistler        Gwen    bird    \N      1997-12-09      \N
Darrell> Slim    Benny   snake   m       1996-04-29      \N
Darrell> ----------------------------------------------------------
Darrell> and did:
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Darrell> Why is the NULL not showing?

Darrell> Then i just did:
mysql> delete * pet where owner = "Gwen";
mysql> INSERT INTO pet
mysql> VALUES ('Whistler','Gwen','bird',NULL,'1997-12-09',NULL);
mysql> FLUSH PRIVILEGES;

mysql> SELECT * FROM pet;
Darrell> +----------+---------+---------+------+------------+------------+
Darrell> | name     | owner   | species | sex  | birth      | death      |
Darrell> +----------+---------+---------+------+------------+------------+
Darrell> | Buffy    | Harold  | dog     | f    | 1989-05-13 | NULL       |
Darrell> | Bowser   | Diane   | dog     | m    | 1985-08-31 | 1995-07-29 |
Darrell> | Whistler | Gwen    | bird    | NULL | 1997-12-09 | NULL       |
Darrell> | Slim     | Benny   | snake   | m    | 1996-04-29 | NULL       |
Darrell> +----------+---------+---------+------+------------+------------+
Darrell> 4 rows in set (0.00 sec)

Darrell> Now it is correct. Does LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
Darrell> not take \N as NULL in a center field? The docs says it does, but ...... 

Hi!

Please ALWAYS use mysqlbug when posting questions !

(In this case I would like to know which MySQL version you are using!)

You will also get faster responces if you post a full example, like
the following:

-----

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20002 to server version: 3.23.0-alpha-debug

Type 'help' for help.

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.65 sec)

mysql> load data infile "/tmp/skr" into table pet;
Query OK, 4 rows affected (0.14 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser   | Diane  | dog     | m    | 1998-08-31 | 1995-07-29 |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
4 rows in set (0.02 sec)

Contains of /tmp/skr:

Buffy	Harold	dog	f	1989-05-13	\N
Bowser	Diane	dog	m	1998-08-31	1995-07-29
Whistler	Gwen	bird	\N	1997-12-09	\N
Slim	Benny	snake	m	1996-04-29	\N

---------

As you can see, it works for me (and should work in MySQL 3.22)

Regards,
Monty