mysql - 多删除不起作用

Question

我有一个像这样设置的 9 表数据库。

表A账户ID

表BtabB_idtabA_id_fk

表CtabC_idtabB_id_fk

表DtabD_idtabB_id_fk

表EtabE_idtabB_id_fk

我相信您现在已经看到了模式。第二个表引用第一个，所有其他表引用第二个。第二个表 (TableB) 有七个表，它们通过外键引用它。其中一张表与 TableB 是一对多的，比方说 TableD。我想用一个查询删除所有表中的相应行。我已经搜索了一段时间，找不到答案。这是我正在使用的 SQL 语句。

DELETE FROM photos, location, contacts, messages, stats, viewed_by, ethnicity,  profile, members
USING photos INNER JOIN location 
INNER JOIN contacts 
INNER JOIN messages 
INNER JOIN stats 
INNER JOIN viewed_by 
INNER JOIN ethnicity 
INNER JOIN profile 
INNER JOIN members 
WHERE photos.profile_id_fk = profile.profile_id 
AND location.profile_id_fk = profile.profile_id 
AND stats.profile_id_fk = profile.profile_id 
AND viewed_by.profile_id_fk = profile.profile_id 
AND ethnicity.profile_id_fk = profile.profile_id 
AND profile.member_id_fk = members.member_id 
AND members.member_id=?

查询不会返回任何错误，但也不会删除任何行。可能是什么问题？

我是否应该只使用九个单独的删除查询，每个表一个？

谢谢大家

Answer 1

我的第一个答案假设是 InnoDB。这个答案是为 MyISAM 做的。 delete in mysql

> desc parent; desc child_1; desc child_2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| parent_id_fk | int(11)      | YES  |     | NULL    |       |
| name         | varchar(100) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| parent_id_fk | int(11)      | YES  |     | NULL    |       |
| name         | varchar(100) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

> select * from parent; select * from child_1; select * from child_2;
+----+------------+
| id | name       |
+----+------------+
|  1 | Andreas    |
|  2 | Wederbrand |
+----+------------+
2 rows in set (0.00 sec)

+----+--------------+-----------+
| id | parent_id_fk | name      |
+----+--------------+-----------+
|  1 |            1 | Child 1 1 |
|  2 |            1 | Child 1 2 |
|  3 |            2 | Child 2 1 |
|  4 |            2 | Child 2 2 |
+----+--------------+-----------+
4 rows in set (0.00 sec)

+----+--------------+-----------+
| id | parent_id_fk | name      |
+----+--------------+-----------+
|  1 |            1 | Child 1 1 |
|  2 |            1 | Child 1 2 |
|  3 |            2 | Child 2 1 |
|  4 |            2 | Child 2 2 |
+----+--------------+-----------+
4 rows in set (0.00 sec)

> delete p, c1, c2 from parent p, child_1 c1, child_2 c2 where p.id = c1.parent_id_fk and p.id = c2.parent_id_fk and p.id = 2;
Query OK, 5 rows affected (0.33 sec)

你的查询可能是正确的，我没有你的表，但用

DELETE photos, location, contacts, messages, stats, viewed_by, ethnicity,  profile, members
FROM photos 
INNER JOIN location 
INNER JOIN contacts 
INNER JOIN messages 
INNER JOIN stats 
INNER JOIN viewed_by 
INNER JOIN ethnicity 
INNER JOIN profile 
INNER JOIN members 
WHERE photos.profile_id_fk = profile.profile_id 
AND location.profile_id_fk = profile.profile_id 
AND stats.profile_id_fk = profile.profile_id 
AND viewed_by.profile_id_fk = profile.profile_id 
AND ethnicity.profile_id_fk = profile.profile_id 
AND profile.member_id_fk = members.member_id 
AND members.member_id=?