From: Michael Widenius Date: April 17 1999 7:19am Subject: NULL ? List-Archive: http://lists.mysql.com/mysql/1981 Message-Id: <14104.13643.108941.21920@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >>>>> "Darrell" == Darrell Shifflett 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