prepared mysql query where value may be NULL

Question

I'm using node's mysql library and trying to do a query like so:

connection.query(`SELECT * FROM table WHERE name = ? AND field = ?`, ['a', value]);

The problem I'm running into is that sometimes value = 1 but sometimes value = null.

From my testing, results only return when the query is written as WHERE value IS null and doesn't work with WHERE value = null.

Q: How can I use the prepared query if the value may be null?

Answer 1

Sorry to make you disappointed, but YOU CANNOT

You should use different comparative statement, which is

1. WHERE value IS NULL

2. WHERE value = <your value>

Regards to the Mysql Reference, null values are treated differently, furthermore null values are a missing values. So You can't use arithmetic comparison for NULL

Here is the reference https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

Answer 2

Nobody mentioned spaceship operator <=>, it works with null-to-null comparsions

Here is great spaceship operator description

Answer 3

Maybe you want try this:

SELECT * FROM table WHERE IFNULL(name, 'null') = 'null' AND IFNULL(field, 'null') = 'null'

But the next problem, you cannot fill your field with value 'null', or it will makes your query and data ambiguous.