MySQL where value NOT in SET datatype field -

i should start saying know how check existence of set datatype, however, i'm trying negate lookup, doesn't seem work, i'm assuming i'm doing stupid.

i've got field:

| billing_payment_prefs  | set('allow_admin','allow_trans','allow_supress','requires_nag')       | yes  |     | null                |                             | 

and records null said field. have 3000+ records in table, , running following query:

mysql> select count(id) customers billing_payment_prefs not '%allow_trans%'; +-----------+ | count(id) | +-----------+ |         0 | +-----------+ 1 row in set (0.00 sec) 

... 0 instead of 3000 plus (which i'd expect, they're null). now, i'm unsure of how not like against set field, had assumed (incorrectly, looks of things) work, though mysql dev doesn't mention it.

any help, appreciated. thanks.

the reason getting no records because mysql (as rdbms) treat null special value. comparing text against null not yield true or false, rather yields null, because comparing unknown value against known value yields unknown result.

to make logic in query work, can add condition null checks:

select count(id) customers billing_payment_prefs not '%allow_trans%' or       billing_payment_prefs null