mysql同时删除多个表中字段_mysql – 从多个字段匹配的表中删除从其他...

也许DELETE JOIN会更好

DELETE A.* FROM table1 A INNER JOIN table2 b

ON A.id = B.id

AND a.field1 = b.field1

AND a.field2 = b.field2

AND a.field3 = b.field3

AND b.id = ?;

我写了为什么DELETE与WHERE涉及一个子查询在2011年2月22日我过去的帖子中处理有点不健康：Problem with MySQL subquery

即使在改进查询优化器方面取得了很大进展,但子查询的评估可能会使密钥无法用于下游的密钥比较.

备选

尝试收集您知道需要删除的密钥并使用它执行DELETE JOIN：

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;

INSERT INTO table1

SELECT A.id FROM table1 A INNER JOIN table2 b

ON A.id = B.id

AND a.field1 = b.field1

AND a.field2 = b.field2

AND a.field3 = b.field3

AND b.id = ?

;

ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);

DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;

DROP TABLE DeleteIDs;

建议

也许索引会有所帮助

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);

ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);

试试看 ！！！