DELETE语句
DELETE
是DML语句,用于从表中删除行。
一个DELETE
语句可以用开始WITH
子句来定义内访问的公共表表达式DELETE
。请参见“ WITH(公用表表达式)”。
单表语法
DELETE [LOW_PRIORITY ] [QUICK ] [IGNORE ]FROM tbl_name [[AS ] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
该DELETE
语句从中删除行tbl_name
并返回删除的行数。要检查已删除的行数,请调用“信息函数”中ROW_COUNT()
描述的函数。
主要条款
可选WHERE
子句中的条件标识要删除的行。没有no WHERE
子句,将删除所有行。
where_condition
是一个表达式,对于要删除的每一行,其值为true。如“ SELECT语句”中所述指定它。
如果ORDER BY
指定了子句,则按指定的顺序删除行。该LIMIT
子句限制了可以删除的行数。这些子句适用于单表删除,但不适用于多表删除。
多表语法
DELETE [LOW_PRIORITY ] [QUICK ] [IGNORE ] tbl_name[.*] [, tbl_name[.*]] ...FROM table_references [WHERE where_condition]DELETE [LOW_PRIORITY ] [QUICK ] [IGNORE ]FROM tbl_name[.*] [, tbl_name[.*]] ...USING table_references [WHERE where_condition]
礼遇
您需要DELETE
表的特权才能从中删除行。SELECT
对于仅读取的任何列(例如在WHERE
子句中命名的列),您仅需要特权。
性能
当您不需要知道已删除的行数时,与没有子句TRUNCATE TABLE
的DELETE
语句相比,该语句是清空表的更快方法WHERE
。与不同的是DELETE
,TRUNCATE TABLE
不能在事务内或表上有锁时使用。请参见“ TRUNCATE TABLE语句”和“ LOCK TABLES和UNLOCK TABLES语句”。
删除操作的速度也可能受“优化DELETE语句”中讨论的因素的影响。
为了确保给定的DELETE
语句不会花费太多时间,MySQL专用的子句指定要删除的最大行数。如果要删除的行数大于限制,请重复该语句,直到受影响的行数小于该值为止。LIMIT row_count
DELETE
DELETE
LIMIT
子查询
您不能从表中删除,也不能从子查询的同一表中选择。
分区表支持
DELETE
支持使用PARTITION
选项进行显式分区选择,该选项采用一个或多个分区或子分区(或两者)的逗号分隔名称列表,从中选择要删除的行。未包括在列表中的分区将被忽略。给定一个分区表t
有一个名为分区的表p0
,执行该语句与执行该表DELETE FROM t PARTITION(p0)
具有相同的作用ALTER TABLE t TRUNCATE PARTITION(p0)
;在这两种情况下,分区p0
中的所有行均被删除。
PARTITION
可以与WHERE
条件一起使用,在这种情况下,仅在列出的分区中的行上测试条件。例如,DELETE FROM t PARTITION(p0)WHERE c < 5
仅从p0
条件c < 5
为真的分区中删除行;不会检查任何其他分区中的行,因此不受的影响DELETE
。
该PARTITION
选项也可以在多表DELETE
语句中使用。每个选项中指定的表最多可以使用一个这样的FROM
选项。
有关更多信息和示例,请参见“分区选择”。
自动增量列
如果删除包含一AUTO_INCREMENT
列最大值的行,则该值不会再用于MyISAM
或InnoDB
表。如果您以模式(不带子句)删除表中的所有行,则该序列将重新启动除和以外的所有存储引擎。对于表,此行为有一些例外,如“ InnoDB中的AUTO_INCREMENT处理”中所述。DELETE FROM tbl_name
WHERE
autocommit
InnoDB
MyISAM
InnoDB
对于MyISAM
表,您可以AUTO_INCREMENT
在多列键中指定第二列。在这种情况下,即使对于MyISAM
表,也会重复使用从序列顶部删除的值。请参见“使用AUTO_INCREMENT”。
修饰符
该DELETE
语句支持以下修饰符:
- 如果指定
LOW_PRIORITY
修饰符,服务器将延迟执行,DELETE
直到没有其他客户端从表中读取。这会影响只使用表级锁只存储引擎(例如MyISAM
,MEMORY
和MERGE
)。 - 对于
MyISAM
表,如果使用QUICK
修饰符,则存储引擎不会在删除期间合并索引叶,这可能会加快某些类型的删除操作。 - 该
IGNORE
修饰符使MySQL在删除行的过程中忽略错误。(在解析阶段遇到的错误将以通常的方式处理。)由于使用而IGNORE
被忽略的错误将作为警告返回。有关更多信息,请参见IGNORE关键字和严格SQL模式的比较。
删除顺序
如果该DELETE
语句包含一个ORDER BY
子句,则按该子句指定的顺序删除行。这主要与结合使用LIMIT
。例如,以下语句查找与该WHERE
子句匹配的行,按对其进行排序timestamp_column
,然后删除第一个(最旧的)行:
DELETE FROM somelogWHERE user = 'jcole'ORDER BY timestamp_columnLIMIT 1;
ORDER BY
还有助于按照避免引用完整性违规的顺序删除行。
InnoDB表
如果要从大表中删除许多行,则可能会超出表的锁定表大小InnoDB
。为避免此问题,或只是为了最小化表保持锁定的时间,以下策略(根本不使用DELETE
)可能会有所帮助:
选择行不被删除到具有相同的结构与原始表的空表:
INSERT INTO t_copySELECT *FROM tWHERE ... ;用于
RENAME TABLE
以原子方式将原始表移开,并将副本重命名为原始名称:RENAME TABLE tTO t_old, t_copyTO t;删除原始表:
DROP TABLE t_old;
在RENAME TABLE
执行过程中,没有其他会话可以访问所涉及的表,因此重命名操作不会遇到并发问题。请参见“ RENAME TABLE语句”。
MyISAM表
在MyISAM
表中,已删除的行保留在链接列表中,并且后续INSERT
操作重用旧的行位置。要回收未使用的空间并减小文件大小,请使用OPTIMIZE TABLE
语句或myisamchk实用程序重新组织表。OPTIMIZE TABLE
更容易使用,但myisamchk更快。请参见“REPAIR TABLE 语句(优化)”和“myisamchk— MyISAM表维护实用程序”。
该QUICK
修改会影响指数的叶子是否合并的删除操作。DELETE QUICK
对于删除行的索引值被以后插入的行的相似索引值替换的应用程序最有用。在这种情况下,删除值留下的漏洞将被重用。
DELETE QUICK
当删除的值导致索引块的填充不足时,该索引块跨越了索引值范围,并再次出现了新的插入,则该功能不可用。在这种情况下,使用QUICK
可能会导致索引中浪费空间而无法回收。这是这种情况的示例:
- 创建一个包含索引
AUTO_INCREMENT
列的表。 - 在表中插入许多行。每次插入都会产生一个索引值,该值将添加到索引的高端。
- 使用删除列范围底端的一行行
DELETE QUICK
。
在这种情况下,与已删除索引值关联的索引块将被填充不足,但由于使用,因此不会与其他索引块合并QUICK
。当发生新的插入操作时,它们仍会填充不足,因为新行的索引值不在删除范围内。此外,即使您以后DELETE
不使用,它们也会保持未填充状态QUICK
,除非某些已删除的索引值恰好位于未填充块内部或附近的索引块中。要在这种情况下回收未使用的索引空间,请使用OPTIMIZE TABLE
。
如果你是从一个表要删除多行,它可能是更快地使用DELETE QUICK
之后OPTIMIZE TABLE
。这将重建索引,而不是执行许多索引块合并操作。
多表删除
您可以在一条DELETE
语句中指定多个表,以根据WHERE
子句中的条件从一个或多个表中删除行。不能使用ORDER BY
或LIMIT
在多台DELETE
。该table_references
子句列出了连接所涉及的表,如“ JOIN子句”中所述。
对于第一个多表语法,仅FROM
删除该子句之前列出的表中的匹配行。对于第二种多表语法,仅删除子句中列出的表中(该FROM
子句之前USING
)的匹配行。结果是您可以同时从许多表中删除行,并使其他表仅用于搜索:
DELETE t1, t2FROM t1INNER JOIN t2INNER JOIN t3WHERE t1.id=t2.id AND t2.id=t3.id;
要么:
DELETE FROM t1, t2USING t1INNER JOIN t2INNER JOIN t3WHERE t1.id=t2.id AND t2.id=t3.id;
在搜索要删除的行时,这些语句使用所有三个表,但是仅从表t1
和中删除匹配的行t2
。
前面的示例使用INNER JOIN
,但是多表DELETE
语句可以使用语句中允许的其他类型的联接SELECT
,例如LEFT JOIN
。例如,要删除中t1
不匹配的行t2
,请使用LEFT JOIN
:
DELETE t1FROM t1LEFT JOIN t2ON t1.id=t2.idWHERE t2.id IS NULL;
.*
每种语法都允许tbl_name
与Access兼容。
如果您使用DELETE
包含InnoDB
有外键约束的表的多表语句,则MySQL优化器可能以与其父/子关系不同的顺序处理表。在这种情况下,该语句将失败并回滚。相反,您应该从单个表中删除,并依靠提供的ON DELETE
功能InnoDB
来相应地修改其他表。
注意如果为表声明别名,则在引用表时必须使用别名:
DELETE t1FROM testAS t1, test2WHERE ...
多表中的表别名DELETE
仅应table_references
在语句的一部分中声明。在其他地方,允许别名引用,但不允许别名声明。
正确:
DELETE a1, a2FROM t1AS a1INNER JOIN t2AS a2WHERE a1.id=a2.id;DELETE FROM a1, a2USING t1AS a1INNER JOIN t2AS a2WHERE a1.id=a2.id;
不正确:
DELETE t1AS a1, t2AS a2FROM t1INNER JOIN t2WHERE a1.id=a2.id;DELETE FROM t1AS a1, t2AS a2USING t1INNER JOIN t2WHERE a1.id=a2.id;
DELETE
从MySQL 8.0.16开始,单表语句也支持表别名。(错误#89410,错误#27455809)