CODEWITHSUNDEEP
mysqlnode.jsnode-mysql
d-_-b
d-_-b

Reputation: 23191

prepared mysql query where value may be NULL

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?

Upvotes: 0

Views: 690

Answers (3)

Krzysiek
Krzysiek

Reputation: 2495

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

Here is great spaceship operator description

Upvotes: 2

Ahmed Subkhi
Ahmed Subkhi

Reputation: 49

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.

Upvotes: 0

Hari Setiawan
Hari Setiawan

Reputation: 136

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

Upvotes: 3

Related Questions