Is there any way to set a variable in mysql so that it equals NULL?
I'm trying to write a system for managing persistent prepared statements on multiple connection pools. I'm doing PREPARE / SET / EXECUTE directly on a database. For instance:
PREPARE test FROM 'SELECT * FROM users WHERE email=?';
SET @email='[email protected]';
EXECUTE test USING @email;
The problem is, if you set @email=NULL it won't pick up rows where email was null. You'd have to use the <=> operator for that. The trouble is, this statement only works with equality and can't use a null-safe operator:
PREPARE test FROM 'UPDATE users SET email=?';In other words, I need a way to ALWAYS use = or always use <=> in mixed prepared statements that may include select / where, update / set, etc., so I can bind the value NULL freely to any parameter... ideally without demanding that the consumer always use a null-safe equality in their WHERE if a null (or not null) might end up being bound.
So I think my question is how to define a variable so that it equals NULL, or doesn't equal NULL, in a way that can be used in comparisons without the null-safe operator. Is that possible in Mysql?
Edit: Just to add, as of now the only way I can see getting around this would be trying to parse where I am in the statement and find out if I'm behind a WHERE or behind a SET, and replace = with <=> in the former instance. That seems insane for what I'm trying to do...