Imagine you have a table with 50000 rows and want to delete 5 entries based on the PK value. The expected query might look like this:

DELETE FROM table_name WHERE id = 5 OR id = 10 OR id = 15 OR id = 20 OR id = 25

This works as expected. However, if you're in a hurry and accidentally format it like this:

DELETE FROM table_name WHERE id = 5 OR 10 OR 15 OR 20 OR 25

It will delete all 50000 rows. Does anyone know if this is a bug? If not I don't understand why it treats it like a delete all instead of return a syntax error.

CodePudding user response：

That is not a bug in MySQL.

10, 15, 20 and 25 are truthy values. So when the OR is evaluated against one of them, the whole condition is true.

You should consider an IN clause.

CodePudding user response：

In MySQL, unlike standard SQL:

• false and the integer 0 are treated the same
• true and the integer 1 are treated the same
• any integer value other than zero is also treated as true

See https://dev.mysql.com/doc/refman/8.0/en/boolean-literals.html

Your expression is combining several terms as boolean conditions:

(id = 5) OR (10) OR (15) OR (20) OR (25)

Since all non-zero integers are treated as true, this ends up being like:

(id = 5) OR (true) OR (true) OR (true) OR (true)

And you should recall from high school math that X OR true evaluates as true.

Note that the your expression would also do something you don't expect in many other programming languages. Using integer as a series of boolean terms doesn't compare those integers to the operand of the first term.