Mysql – How to change the display of an empty string in thesql command line tool

innodbMySQLnullselectvarchar

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.