blocks|key|937097|text|truncate+table不能回滚，就像删除并重新创建表一样。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|937098|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

truncate table cannot be rolled back, it is like dropping and recreating the table.

blocks|key|149562|text|...just来添加一些细节。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|149563|调用DELETE语句将通知数据库引擎生成所有已删除记录的事务日志。如果删除操作出错，您可以恢复您的记录。|149564|调用TRUNCATE语句是一种“要么全有要么全无”的笼统说法，它将删除所有没有事务日志可供还原的记录。这绝对是更快的，但只有当你确定你不需要任何你要删除的记录时才应该这样做。|149565|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

...just to add some detail.

Calling the DELETE statement tells the database engine to generate a transaction log of all the records deleted. In the event the delete was done in error, you can restore your records.

Calling the TRUNCATE statement is a blanket "all or nothing" that removes all the records with no transaction log to restore from. It is definitely faster, but should only be done when you're sure you don't need any of the records you're going to remove.

blocks|key|942217|text|Delete+from+table一次删除一行中的每一行，并在事务日志中添加一条记录，以便可以回滚操作。删除所用的时间也与表上的索引数量成正比，以及是否有任何外键约束(对于innodb)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|942218|Truncate可以有效地删除表并重新创建它，并且不能在事务中执行。因此，它需要的操作更少，执行速度也更快。Truncate也不使用任何on+delete触发器。|942219|在MySql文档中可以找到有关为什么在MySql中更快的确切详细信息：http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html|offset|length|942220|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html^0|0|0|Z|1M|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

Delete from table deletes each row from the one at a time and adds a record into the transaction log so that the operation can be rolled back. The time taken to delete is also proportional to the number of indexes on the table, and if there are any foreign key constraints (for innodb).

Truncate effectively drops the table and recreates it and can not be performed within a transaction. It therefore required fewer operations and executes quickly. Truncate also does not make use of any on delete triggers.

Exact details about why this is quicker in MySql can be found in the MySql documentation: 
<a href="http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html</a>

blocks|key|942237|text|你的问题是关于MySQL的，我对MySQL这个产品知之甚少，甚至一无所知，但我想我应该补充一句，在SQL+Server中，TRUNCATE语句可以回滚。你自己试试吧|type|unstyled|depth|inlineStyleRanges|entityRanges|data|942238|create+table+test1+(col1+int)
go
insert+test1+values(3)
begin+tran
truncate+table+test1
select+*+from+test1
rollback+tran
select+*+from+test1|code-block|syntax|javascript|942239|在记录SQL+Server+TRUNCATE时，它只是没有像记录DELETE那样以详细的方式记录。我相信它被称为最小日志记录操作。实际上，数据页仍然包含数据，但它们的区段已被标记为删除。只要数据页仍然存在，您就可以回滚截断。希望这能对你有所帮助。如果有人在MySQL上尝试一下，我很有兴趣知道结果。|942240|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Your question was about MySQL and I know little to nothing about MySQL as a product but I thought I'd add that in SQL Server a TRUNCATE statement can be rolled back. Try it for yourself

<pre><code>create table test1 (col1 int)
go
insert test1 values(3)
begin tran
truncate table test1
select * from test1
rollback tran
select * from test1
</code></pre>

In SQL Server TRUNCATE is logged, it's just not logged in such a verbose way as DELETE is logged. I believe it's referred to as a minimally logged operation. Effectively the data pages still contain the data but their extents have been marked for deletion. As long as the data pages still exist you can roll back the truncate. Hope this is helpful. I'd be interested to know the results if somebody tries it on MySQL.

blocks|key|4213396|text|对于使用InnoDb作为存储引擎的MySql+5，TRUNCATE的作用就像没有WHERE子句的DELETE+:也就是说，对于大表，它需要很长时间，因为它逐个删除行。这在版本6.x中发生了变化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4213397|看见|4213398|http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html|offset|length|4213399|对于5.1信息(使用InnoDB逐行)和|4213400|http://blogs.mysql.com/peterg/category/personal-opinion/|4213401|对于6.x中的更改|4213402|4213403|编者按|4213404|这个答案是clearly+contradicted+by+the+MySQL+documentation|4213405|对于5.0.3版之前的InnoDB表，InnoDB通过逐行删除来处理截断表。从MySQL+5.0.3开始，仅当存在引用该表的外键约束时才使用逐行删除。如果没有外键约束，InnoDB将通过删除原始表并创建具有相同定义的空表来执行快速截断，这比逐行删除行快得多。|4213406|entityMap|0|LINK|mutability|MUTABLE|url|1|2|http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html^0|0|0|0|1M|0|0|0|0|1K|1|0|0|0|0|5|1B|2|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@$F|17|G|18|1|19]]|A|$]]|$1|H|3|I|5|6|7|1A|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1B|8|@]|9|@$F|1C|G|1D|1|1E]]|A|$]]|$1|L|3|M|5|6|7|1F|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1H|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1I|8|@]|9|@$F|1J|G|1K|1|1L]]|A|$]]|$1|S|3|T|5|6|7|1M|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|E]]|11|$5|X|Y|Z|A|$10|K]]|12|$5|X|Y|Z|A|$10|13]]]]

For MySql 5 using InnoDb as the storage engine, TRUNCATE acts just like DELETE without a WHERE clause: i.e. for large tables it takes ages because it deletes rows one-by-one. This is changing in version 6.x.
see
<a href="http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html</a>
for 5.1 info (row-by-row with InnoDB) and
<a href="http://blogs.mysql.com/peterg/category/personal-opinion/" rel="nofollow noreferrer">http://blogs.mysql.com/peterg/category/personal-opinion/</a>
for changes in 6.x
<hr />
<h2>Editor's note</h2>
This answer is <a href="http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html" rel="nofollow noreferrer">clearly contradicted by the MySQL documentation</a>:
&quot;For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row deletion is used only if there are any FOREIGN KEY constraints that reference the table. If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.&quot;

blocks|key|149711|text|Truncate位于表级，而Delete位于行级。如果要将其转换为其他语法的sql，则truncate将为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|149712|DELETE+*+FROM+table|code-block|syntax|javascript|149713|因此一次删除所有行，而DELETE语句(在PHPMyAdmin中)是这样的：|149714|DELETE+*+FROM+table+WHERE+id+=+1
DELETE+*+FROM+table+WHERE+id+=+2|149715|直到桌子空了。每次查询需要几(毫秒)秒的时间，这些时间加起来比截断花费的时间更长。|149716|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Truncate is on a table level, while Delete is on a row level. If you would translate this to sql in an other syntax, truncate would be:

<pre><code>DELETE * FROM table
</code></pre>

thus deleting all rows at once, while DELETE statement (in PHPMyAdmin) goes like:

<pre><code>DELETE * FROM table WHERE id = 1
DELETE * FROM table WHERE id = 2
</code></pre>

Just until the table is empty. Each query taking a number of (milli)seconds which add up to taking longer than a truncate.

(I've tried this in MySql)

I believe they're semantically equivalent. Why not identify this trivial case and speed it up?

Why 'delete from table' takes a long time when 'truncate table' takes 0 time?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

(我已经在MySql中尝试过了)我相信它们在语义上是等价的。为什么不找出这个微不足道的案例，并加快速度呢？

问为什么'delete from table‘需要很长时间，而'truncate table’需要0时间？
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么'delete from table‘需要很长时间，而'truncate table’需要0时间？EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么'delete from table‘需要很长时间，而'truncate table’需要0时间？
EN