一、概述
DELETE是一个DML语句,用于从表中删除行。
DELETE语句可以从with子句开始,以定义DELETE中可访问的公用表表达式。
单表语法
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子句中的条件标识要删除的行。如果没有WHERE子句,则会删除所有行。
其中_condition是一个表达式,对于要删除的每一行,该表达式的计算结果都为true。
如果指定了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权限才能从表中删除行。您只需要对任何只读取的列(如WHERE子句中命名的列)具有SELECT权限。
性能
当您不需要知道删除的行数时,TRUNCATE TABLE语句是比不带WHERE子句的DELETE语句更快清空表的方法。与DELETE不同,TRUNCATE TABLE不能在事务中使用,也不能在表上有锁的情况下使用。
为了确保给定的DELETE语句不会花费太多时间,DELETE的MySQL特定的LIMIT row_count子句指定了要删除的最大行数。如果要删除的行数大于限制,请重复delete语句,直到受影响的行数小于limit值。
子查询
不能从表中删除,也不能在子查询中从同一表中选择。
分区表支持
DELETE支持使用partition子句进行显式分区选择,该子句采用一个或多个分区或子分区(或两者)的逗号分隔名称列表,从中选择要删除的行。未包含在列表中的分区将被忽略。给定分区为p0的分区表t,执行语句DELETE FROM t partition(p0)对该表的影响与执行ALTER table t TRUNCATE partition (p0);在这两种情况下,分区p0中的所有行都被删除。
PARTITION可以与WHERE条件一起使用,在这种情况下,只在列出的分区中的行上测试条件。例如,DELETE FROM t PARTITION(p0)WHERE c<5仅从条件c<5为true的分区p0中删除行;不检查任何其他分区中的行,因此不受DELETE的影响。
PARTITION子句也可以用于多个表DELETE语句中。每个在FROM选项中命名的表最多可以使用一个这样的选项。
自动递增列
如果删除包含AUTO_INCREMENT列最大值的行,则MyISAM或InnoDB表不会重用该值。如果在自动提交模式下使用delete FROM tbl_name(不带WHERE子句)删除表中的所有行,则除InnoDB和MyISAM外,所有存储引擎的顺序都将重新开始。InnoDB表的这种行为有一些例外。
对于MyISAM表,可以在多列键中指定AUTO_INCREMENT辅助列。在这种情况下,即使对于MyISAM表,也会重复使用从序列顶部删除的值。
修饰符
DELETE语句支持以下修饰符:
- 如果指定LOW_PRIORITY修饰符,则服务器会延迟DELETE的执行,直到没有其他客户端从表中读取为止。这只会影响仅使用表级锁定的存储引擎(如MyISAM、MEMORY和MERGE)。
- 对于MyISAM表,如果使用QUICK修饰符,存储引擎在删除期间不会合并索引叶,这可能会加快某些类型的删除操作。
- IGNORE修饰符导致MySQL在删除行的过程中忽略可忽略的错误。(解析阶段遇到的错误以通常的方式处理。)由于使用IGNORE而被忽略的错误将作为警告返回。有关详细信息
删除顺序
如果DELETE语句包含ORDER BY子句,则按该子句指定的顺序删除行。这主要与LIMIT一起使用。例如,以下语句查找与WHERE子句匹配的行,按时间戳_列对它们进行排序,并删除第一个(最旧的)行:
DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;
ORDERBY还有助于按照避免引用完整性冲突所需的顺序删除行。
三、InnoDB 表
如果要从一个大表中删除许多行,则可能会超过InnoDB表的锁表大小。为了避免这个问题,或者只是为了尽量减少表保持锁定的时间,以下策略(根本不使用DELETE)可能会有所帮助:
- 在与原始表具有相同结构的空表中选择不删除的行:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
- 使用RENAME TABLE自动移动原始表,并将副本重命名为原始名称:
RENAME TABLE t TO t_old, t_copy TO t;
- 删除原始表格:
DROP TABLE t_old;
当RENAME TABLE执行时,没有其他会话可以访问所涉及的表,因此重命名操作不会出现并发问题。
四、多表删除
根据WHERE子句中的条件,可以在DELETE语句中指定多个表来删除一个或多个表中的行。不能在多表DELETE中使用ORDER BY或LIMIT。
对于第一个多表语法,只删除from子句之前列出的表中的匹配行。对于第二个多表语法,只删除from子句(在USING子句之前)中列出的表中的匹配行。其效果是,您可以同时从多个表中删除行,并具有仅用于搜索的其他表:
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
或
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
这些语句在搜索要删除的行时使用所有三个表,但仅从表t1和t2中删除匹配的行。
前面的示例使用INNER JOIN,但是多个表DELETE语句可以使用SELECT语句中允许的其他类型的联接,例如LEFT JOIN。例如,要删除t1中存在但t2中没有匹配项的行,请使用LEFT JOIN:
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
语法允许在每个tbl_name后面加.*,以与Access兼容。
如果使用涉及存在外键约束的InnoDB表的多表DELETE语句,MySQL优化器可能会以不同于其父/子关系的顺序处理表。在这种情况下,语句将失败并回滚。相反,您应该从单个表中删除,并依靠InnoDB提供的on delete功能来相应地修改其他表。
多表DELETE中的表别名只能在语句的Table_references部分声明。在其他地方,允许使用别名引用,但不允许使用别名声明。
正确:
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;
不正确的文章来源:https://www.toymoban.com/news/detail-678939.html
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;
从MySQL 8.0.16开始的单表DELETE语句也支持表别名。文章来源地址https://www.toymoban.com/news/detail-678939.html
到了这里,关于MySql Delete 使用及优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!