mysql - 如何删除其外键为 NULL 的行？

Question

我有两个表，名称分别为 tab1 和 tab2。

tab1 的 pprimary key is price and price is foreign key in tab2 并且 on delete set null.

当我从存在于 tab2 中的 tab1 中删除一个 primary key 时，相应的外键

设置为 NULL。

现在我想从 tab2 中删除外键为 null 的行。

我写了这个查询，但它不起作用。

delete from tab2 where price = null.

请帮我解决一下。

Answer 1

您不能使用 = 比较 NULL。您应该改用 IS NULL

WHERE price IS NULL

来自MySQL Reference Manual

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>.

但是，如果您已经在使用 on delete set null，为什么不简单地将其更改为 on delete cascade，从而省去手动删除行的麻烦？