MySQL 使用 ON 从多个表中删除不起作用

Question

我正在尝试使用以下查询从多个表中删除

mysql>         DELETE
    ->           info, pagelets, shingles, links
    ->         FROM
    ->           info
    ->         INNER JOIN pagelets
    ->           ON info.page_key=144
    ->           AND info.page_key=pagelets.page_key
    ->         INNER JOIN shingles
    ->           ON pagelets.pagelet_serial=shingles.pagelet_serial
    ->         INNER JOIN links
    ->           ON pagelets.pagelet_serial=links.pagelet_serial
    -> ;
Query OK, 2050 rows affected (0.08 sec)

mysql> SELECT * FROM info;
+--------+----------+
| netloc | page_key |
+--------+----------+
|      1 |        2 |
|      1 |      118 |
+--------+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pagelets WHERE page_key =144;
+----------+----------------+
| page_key | pagelet_serial |
+----------+----------------+
|      144 |            245 |
|      144 |            246 |
|      144 |            249 |
|      144 |            253 |

不幸的是，这仅适用于表格链接和信息。 (不知道为什么它在 pagelet 和 shingles 上不起作用)

原始查询是:

#        DELETE 
#          info, pagelets, shingles, links
#        FROM
#          info INNER JOIN pagelets INNER JOIN shingles INNER JOIN links
#        WHERE
#          info.page_key=%(page_key)s
#          AND info.page_key=pagelets.page_key
#          AND pagelets.pagelet_serial=shingles.pagelet_serial
#          AND pagelets.pagelet_serial=links.pagelet_serial
#

谁能解释一下这个谜团？

Answer 1

这将从信息和连接到信息的所有其他表中删除。我知道语法具有误导性，但它会起作用!

DELETE 
  info
FROM
  info INNER JOIN pagelets INNER JOIN shingles INNER JOIN links
WHERE
  info.page_key=%(page_key)s
  AND info.page_key=pagelets.page_key
  AND pagelets.pagelet_serial=shingles.pagelet_serial
  AND pagelets.pagelet_serial=links.pagelet_serial

顺便说一下，我知道这是一篇古老的帖子，但是有很多用户在搜索答案时找到了这篇帖子，所以确实没有什么古老的东西! -兰斯