CODEWITHSUNDEEP
mysqlnull
lowerglower
lowerglower

Reputation: 1

MySQL/MariaDB equation to null isn't working

I have database with scheme:

CREATE TABLE `pages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`descr` blob,
`updated` binary(14) NOT NULL,
`weight` float NOT NULL,
`s1` varchar(1024) DEFAULT NULL,
`s2` varchar(1024) DEFAULT NULL,
`s3` varchar(1024) DEFAULT NULL,
`s4` varchar(1024) DEFAULT NULL,
`s5` varchar(1024) DEFAULT NULL,
`s6` varchar(1024) DEFAULT NULL,
`s7` varchar(1024) DEFAULT NULL,
`s8` varchar(1024) DEFAULT NULL,
`s9` varchar(1024) DEFAULT NULL,
`s10` varchar(1024) DEFAULT NULL,
`s11` varchar(1024) DEFAULT NULL,
`s12` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `page_title` (`title`),
KEY `page_updated` (`updated`)
) ENGINE=InnoDB AUTO_INCREMENT=16074 DEFAULT CHARSET=utf8

Then, I have above 10.000 entries like this:

|  3 | !1           | NULL  | 20160412095518 |      0 | NULL | NULL | NULL | ["redirect","1111"]                                                                                                                                         | NULL            | NULL | NULL                                              | NULL | NULL | NULL | NULL | NULL |

But:

> select * from pages where pages.s1 = null limit 5;
Empty set (0.05 sec)

Selection with "pages.s1 like '[%' " working, btw

Why?

Upvotes: 0

Views: 25

Answers (1)

O. Jones
O. Jones

Reputation: 108776

NULL is never =, or <>, or >= or <= to anything, even itself.

It's a bit strange that way. But when you get used to it, it makes sense. NULL has no value at all.

You need where pages.s1 IS NULL not = NULL.

(This can be very confusing in Oracle where a zero-length varchar2 string is considered NULL. But it works in a more straightforward way on MySQL.)

Upvotes: 1

Related Questions