I'm maintaining a database with InnoDB tables.
These tables have some columns of type (from show create table):
`val0` varchar(30) default NULL,
`val1` varchar(30) default NULL,
etc...
From the mysql command line I am searching for NULL entries in these tables.
Now I SELECTed some rows where val0 IS NULL AND val1 = ''
(empty string), but the mysql command line shows both values as
+------+------+
| val0 | val1 |
+------+------+
| NULL | NULL |
+------+------+
Apparently this is confusing me. Is this behavior intentional? Is there a method to define how empty strings are displayed?
EDIT :
I should mention that STRICT MODE (see the docs) seems to be enabled.
The query
SELECT * FROM table WHERE val0 IS NULL AND val1 = '';
finds the row above. The query
SELECT * FROM table WHERE val0 IS NULL AND val1 IS NULL;
doesn't find that row. Still the '' is displayed as 'NULL'. How do I change that?
Server version is "5.0.22-log".
EDIT 2 :
Here is the full (anonymized) SHOW CREATE TABLE mytable:
CREATE TABLE `mytable` (
`id` int(11) NOT NULL,
`time` int(20) NOT NULL,
`val0` varchar(30) default NULL,
`val1` varchar(30) default NULL,
`val2` varchar(30) default NULL,
`val3` varchar(30) default NULL,
`val4` varchar(30) default NULL,
`val5` varchar(30) default NULL,
`val6` varchar(30) default NULL,
`val7` varchar(30) default NULL,
`val8` varchar(30) default NULL,
`val9` varchar(30) default NULL,
`status` tinyint(4) default '0',
UNIQUE KEY `idtime` (`id`,`time`),
KEY `status` (`status`),
KEY `id` (`id`),
KEY `time` (`time`),
CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`id`) REFERENCES `idtable` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Here are my queries:
select * from mytable where val1 is null limit 1;
-> no result
select * from mytable where val1 = '' limit 1;
result:
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| id | time | val0 | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8 | val9 | status |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| 300 | 1346855716 | 0 | NULL | 42 | 4711 | blib | blub | NULL | NULL | NULL | NULL | 5 |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
fun fact: These last four NULLs are actually the string "NULL". (I'm only maintaining this system, I didn't build it…)
Best Answer
Bug #20067 empty string returns NULL as dispalyed value
This bug is possibly fixed 5.0.23. Look at the document referenced by the above page.