Mysql - 删除多表 [英] Mysql - delete multi table
问题描述
DELETE a,b
FROM a AS a
INNER JOIN b AS b ON a.b_id = b.id
WHERE a.id IN (1,2,3,4)
上面的查询只删除了1条记录，为什么?我想删除所有 4 条记录
The above query only delete 1 records, why ? i want delete all 4 records
推荐答案
我创建了一个测试示例如下:
I created a test sample as follows:
使用测试
如果存在，则删除表；
如果存在则删除表 b;
创建表 a (id INT NOT NULL PRIMARY KEY);
创建表 b (id INT NOT NULL PRIMARY KEY);
插入值 (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
插入 b 值 (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT * FROM a;
SELECT * FROM b;
DELETE a.* from a INNER JOIN b ON a.id = b.id WHERE a.id IN (1,2,3,4);
SELECT * FROM a;
use test
DROP TABLE IF EXISTS a;
DROP TABLE IF EXISTS b;
CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
CREATE TABLE b (id INT NOT NULL PRIMARY KEY);
INSERT INTO a VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO b VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT * FROM a;
SELECT * FROM b;
DELETE a.* FROM a INNER JOIN b ON a.id = b.id WHERE a.id IN (1,2,3,4);
SELECT * FROM a;
这是我的结果:
lwdba@localhost (DB 测试):: 使用测试
数据库已更改
lwdba@localhost (DB 测试) :: DROP TABLE IF EXISTS a;
查询正常，0 行受影响(0.06 秒)
lwdba@localhost (DB test) :: use test
Database changed
lwdba@localhost (DB test) :: DROP TABLE IF EXISTS a;
Query OK, 0 rows affected (0.06 sec)
lwdba@localhost (DB test) :: DROP TABLE IF EXISTS b;
查询正常，0 行受影响(0.06 秒)
lwdba@localhost (DB test) :: DROP TABLE IF EXISTS b;
Query OK, 0 rows affected (0.06 sec)
lwdba@localhost (DB test) :: CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
查询正常，0 行受影响(0.23 秒)
lwdba@localhost (DB test) :: CREATE TABLE a (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.23 sec)
lwdba@localhost (DB test) :: CREATE TABLE b (id INT NOT NULL PRIMARY KEY);
查询正常，0 行受影响(0.17 秒)
lwdba@localhost (DB test) :: CREATE TABLE b (id INT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.17 sec)
lwdba@localhost (DB test) :: INSERT INTO a VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)),(10);
查询正常，10 行受影响(0.06 秒)
记录:10 重复:0 警告:0
lwdba@localhost (DB test) :: INSERT INTO a VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0
lwdba@localhost (DB test) :: INSERT INTO b VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)),(10);
查询正常，10 行受影响(0.06 秒)
记录:10 重复:0 警告:0
lwdba@localhost (DB test) :: INSERT INTO b VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0
lwdba@localhost(数据库测试):: SELECT * FROM a;
+----+
|id |
+----+
|1 |
|2 |
|3 |
|4 |
|5 |
|6 |
|7 |
|8 |
|9 |
|10 |
+----+
10 行(0.00 秒)
lwdba@localhost (DB test) :: SELECT * FROM a;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.00 sec)
lwdba@localhost(数据库测试):: SELECT * FROM b;
+----+
|id |
+----+
|1 |
|2 |
|3 |
|4 |
|5 |
|6 |
|7 |
|8 |
|9 |
|10 |
+----+
10 行(0.00 秒)
lwdba@localhost (DB test) :: SELECT * FROM b;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0.00 sec)
lwdba@localhost (DB test) :: DELETE a,b FROM a INNER JOIN b ON a.id = b.id WHERE a.id IN(1,2,3,4);查询正常，4 行受影响(0.08 秒)
lwdba@localhost (DB test) :: DELETE a,b FROM a INNER JOIN b ON a.id = b.id WHERE a.id IN(1,2,3,4);
Query OK, 4 rows affected (0.08 sec)
lwdba@localhost(数据库测试):: SELECT * FROM a;
+----+
|id |
+----+
|5 |
|6 |
|7 |
|8 |
|9 |
|10 |
+----+
6 行(0.00 秒)
lwdba@localhost (DB test) :: SELECT * FROM a;
+----+
| id |
+----+
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
6 rows in set (0.00 sec)
lwdba@localhost(数据库测试):: SELECT * FROM b;
+----+
|id |
+----+
|5 |
|6 |
|7 |
|8 |
|9 |
|10 |
+----+
6 行(0.00 秒)
lwdba@localhost (DB test) :: SELECT * FROM b;
+----+
| id |
+----+
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
6 rows in set (0.00 sec)
试一试！！！
顺便说一句，我从您的查询中删除了AS a"和AS b"，并将 a.b_id 更改为 a.id
BTW I removed "AS a" and "AS b" from your query and I changed a.b_id to a.id
这篇关于Mysql - 删除多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
