mysql 删除操作(delete+TRUNCATE)

delete

语法

单表语法：

delete [low_priority] [quick] [ignore] from tbl_name
    [where where_definition]
    [order by ...]
    [limit row_count]多表语法：

delete [low_priority] [quick] [ignore]
    tbl_name[.*] [, tbl_name[.*] ...]
    from table_references
    [where where_definition]或

delete [low_priority] [quick] [ignore]
    from tbl_name[.*] [, tbl_name[.*] ...]
    using table_references
    [where where_definition]如果您编写的delete语句中没有where子句，则所有的行都被删除。当您不想知道被删除的行的数目时，有一个更快的方法，就是用truncate table。

delete语句支持以下修饰符：

•如果您指定low_priority，则delete的执行被延迟，直到没有其它客户端读取本表时再执行。
• 对于myisam表，如果您使用quick关键词，则在删除过程中，存储引擎不会合并索引端结点，这样可以加快部分种类的删除操作的速度。
•在删除行的过程中，ignore关键词会使mysql忽略所有的错误。（在分析阶段遇到的错误会以常规方式处理。）由于使用本选项而被忽略的错误会作为警告返回。
在myisam表中，被删除的记录被保留在一个带链接的清单中，后续的insert操作会重新使用旧的记录位置。要重新使用未使用的空间并减小文件的尺寸，则使用optimize table语句或myisamchk应用程序重新编排表。optimize table更简便，但是myisamchk速度更快。

quick修饰符会影响到在删除操作中索引端结点是否合并。当用于被删除的行的索引值被来自后插入的行的相近的索引值代替时，delete quick最为适用。在此情况下，被删除的值留下来的空间被重新使用。

未充满的索引块跨越某一个范围的索引值，会再次发生新的插入。当被删除的值导致出现未充满的索引块时，delete quick没有作用。在此情况下，使用quick会导致未利用的索引中出现废弃空间。

如果您打算从一个表中删除许多行，使用delete quick再加上optimize table可以加快速度。这样做可以重新建立索引，而不是进行大量的索引块合并操作。

用于delete的mysql唯一的limit row_count选项用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。本选项用于确保一个delete语句不会占用过多的时间。您可以只重复delete语句，直到相关行的数目少于limit值为止。

如果delete语句包括一个order by子句，则各行按照子句中指定的顺序进行删除。此子句只在与limit联用是才起作用。例如，以下子句用于查找与where子句对应的行，使用timestamp_column进行分类，并删除第一（最旧的）行：

delete from somelog where user = 'jcole' order by timestamp_column limit 1;可以同时删除许多个表中的行，并使用其它的表进行搜索：

delete t1, t2 from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;
--或者
delete from t1, t2 using t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;

当搜索待删除的行时，这些语句使用所有三个表，但是只从表t1和表t2中删除对应的行。

以上例子显示了使用逗号操作符的内部联合，但是多表delete语句可以使用select语句中允许的所有类型的联合，比如left join，不过，您不能在一个多表delete语句中使用order by或limit。
 

当引用表名称时，您必须使用别名（如果已给定）：

delete t1 from test as t1, test2 where ...

进行多表删除时支持跨数据库删除，但是在此情况下，您在引用表时不能使用别名。举例说明：

delete test1.tmp1, test2.tmp2 from test1.tmp1, test2.tmp2 where ...

目前，您不能从一个表中删除，同时又在子查询中从同一个表中选择。

truncate

语法

truncate [table] tbl_nametruncate table用于完全清空一个表。从逻辑上说，该语句与用于删除所有行的delete语句等同，但是在有些情况下，两者在使用上有所不同。

对于innodb表，如果有需要引用表的外键限制，则truncate table被映射到delete上；否则使用快速删减（取消和重新创建表）。使用truncate table重新设置auto_increment计数器，设置时不考虑是否有外键限制。

对于其它存储引擎，truncate table与delete from有以下几处不同：

•删减操作会取消并重新创建表，这比一行一行的删除行要快很多。
•删减操作不能保证对事务是安全的；在进行事务处理和表锁定的过程中尝试进行删减，会发生错误。
•被删除的行的数目没有被返回。
•只要表定义文件tbl_name.frm是合法的，则可以使用truncate table把表重新创建为一个空表，即使数据或索引文件已经被破坏。
•表管理程序不记得最后被使用的auto_increment值，但是会从头开始计数。即使对于myisam和innodb也是如此。myisam和innodb通常不再次使用序列值。
•当被用于带分区的表时，truncate table会保留分区；即，数据和索引文件被取消并重新创建，同时分区定义（.par）文件不受影响。
truncate table是在mysql中采用的一个oracle sql扩展。

转自