在MySQL中,可以使用LIMIT来限制删除的数量,但部分写法并不支持LIMIT.
LIMIT方式
对于查询:
SELECT * FROM T1 LIMIT 1;
可以转换成:
DELETE FROM T1 LIMIT 1;
别名方式:
## 使用别名
DELETE T FROM T1 AS T;
但MySQL不支持别名+LIMIT方式,如:
查询:
SELECT * FROM T1 AS T LIMIT 1;
不能转换为:
DELETE T FROM T1 AS T LIMIT 1;
对于多表关联操作,同样不允许别名+LIMIT操作,但可以将LIMIT放入子查询中:
对于查询:
SELECT * FROM TB_1 AS T1
LEFT JOIN TB_2 AS T2
ON T1.ID = T2.ID
WHERE T2.ID IS NULL
LIMIT 10;
不能修改为:
DELETE T1
FROM TB_1 AS T1
LEFT JOIN TB_2 AS T2
ON T1.ID = T2.ID
WHERE T2.ID IS NULL
LIMIT 10;
需要转换为:
DELETE T1
FROM TB_1 AS T1
(SELECT T1.ID
FROM TB_1 AS T1
LEFT JOIN TB_2 AS T2
ON T1.ID = T2.ID
WHERE T2.ID IS NULL
LIMIT 10) AS T3
INNER JOIN TB_2 AS T2
ON T1.ID=T2.ID