I know that using column = NULL in an SQL statement´s WHERE clause results in an empty result set.
As this is very inefficient in programming environments, I´m searching for a way to let MySQL interpret column = NULL like column IS NULL, maybe by setting an SQL mode?
Well, at least PostgreSQL does have a parameter for that
transform_null_equals = true
When is set to true, the query parser will transform
field = NULL
to
field is NULL
beforehand. Something tells me there's an equivalent parameter hidden in MySQL less documented params.
EDIT as of 2020-07-15
My "not really a solution" might be one of my first answers at SO. I'm somewhat ashamed for answering something about another DBRMS engine instead of what I was asked by the OP.
Anyway, the correct answer would be using the <=> operator as others have said in their answers already. You replace = for that and it will behave as other comparisons:
A = B // zero if different, one if equals, this is treated as a boolean result
A != NULL // always null, therefore not false nor true.
A <=> B // zero if different, one if equals, zero is one of the variables are null, 1 if both are.
This is not a setting and therefore it's behavior would never be session-wise or DDBB wise.
You can use
<=>instead. I wouldn't though. This is not portable.
(Thanks to Martin Smith who pointed me to this which was a solution to my problem)
You can have a case statement in your WHERE clause to use the correct syntax:
http://www.postgresql.org/docs/9.4/static/functions-conditional.html
SELECT * FROM my_items WHERE
CASE WHEN 1=1 THEN mac = '08:00:2b:01:02:03'
WHEN 1=2 THEN mac IS NULL
END;
Obviously, the first case (WHEN 1=1) will always be called in this example. You can put your own logic in that satisfies your condition.
