CODEWITHSUNDEEP
mysql
laminatefish
laminatefish

Reputation: 5246

MySQL where value NOT in SET datatype field

I should start by saying that I know how to check for the existence of a SET datatype, however, I'm now trying to negate the lookup, which doesn't seem to work, so I'm assuming I'm doing something stupid.

I've got this field:

| billing_payment_prefs  | set('allow_admin','allow_trans','allow_supress','requires_nag')       | YES  |     | NULL                |                             |

And all records are currently NULL for said field. I have 3000+ records in this table, and running the following query: 

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

... I get 0 instead of the 3000 plus (which I'd expect, as they're all NULL). Now, I'm obviously unsure of how to look up a NOT LIKE against a SET field, but I had assumed (incorrectly, by the looks of things) that this would work, even though MYSQL DEV doesn't mention it.

Any help, is very much appreciated. Thanks.

Upvotes: 1

Views: 57

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

The reason you are getting no records back is because MySQL (as well as most RDBMS) treat NULL as a special value. Comparing some text against NULL does not yield a true or false, but rather yields NULL, because comparing an unknown value against a known value yields an unknown result.

To make the logic in your query work, you can add a condition for NULL checks:

SELECT COUNT(id)
FROM customers
WHERE billing_payment_prefs NOT LIKE '%allow_trans%' OR
      billing_payment_prefs IS NULL

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You have to explicitly check for NULL values:

select count(id) 
from customers 
where billing_payment_prefs not like '%allow_trans%' or 
      billing_payment_prefs is null;

This holds for all data types, not only enumerations.

Upvotes: 2

Related Questions