MySQL从多个表中删除行

Z时代
2024-01-10
分类：问答

这是正确的方法吗？

DELETE t1, t2, t3, t4 FROM table1 as t1 INNER JOIN table2 as t2 on t1.id = t2.id INNER JOIN table3 as t3 on t1.id=t3.id INNER JOIN table4 as t4 on t1.id=t4.id WHERE t1.username='%s' AND t1.id='%s'

回答：

对，那是正确的。在这里工作正常：

CREATE TABLE table1 (id int, username nvarchar(30));
CREATE TABLE table2 (id int);
CREATE TABLE table3 (id int);
CREATE TABLE table4 (id int);
INSERT INTO table1 VALUES (1, 'Foo'),(2, 'Bar');
INSERT INTO table2 VALUES (1),(2);
INSERT INTO table3 VALUES (1),(2);
INSERT INTO table4 VALUES (1),(2);
SELECT COUNT(*) FROM table1;
2
SELECT COUNT(*) FROM table2;
2
SELECT COUNT(*) FROM table3;
2
SELECT COUNT(*) FROM table4;
2
DELETE t1, t2, t3, t4 FROM
  table1 as t1
  INNER JOIN  table2 as t2 on t1.id = t2.id
  INNER JOIN  table3 as t3 on t1.id=t3.id
  INNER JOIN  table4 as t4 on t1.id=t4.id
  WHERE  t1.username='Foo' AND t1.id='1';
SELECT COUNT(*) FROM table1;
1
SELECT COUNT(*) FROM table2;
1
SELECT COUNT(*) FROM table3;
1
SELECT COUNT(*) FROM table4;
1