MySQL截断表是重置的意思吗 sql 截断表

Truncating a table is removing all the records in an entire table or a table partition. TRUNCATE table is functionally similar to DELETE table with no WHERE clause. However, TRUNCATE table is much faster than DELETE with respect to the time and the resource consumptions which we will look at in this article. TRUNCATE statement removes the data by de-allocating the data pages in the table data. This means that TRUNCATE is similar to drop and re-create the table. Also, it records only the page de-allocations in the transaction log, not the row-wise as in DELETE statement.

截断表将删除整个表或表分区中的所有记录。 TRUNCATE表在功能上类似于没有WHERE子句的DELETE表。 但是，就时间和资源消耗而言，TRUNCATE表比DELETE要快得多，我们将在本文中介绍它。 TRUNCATE语句通过取消分配表数据中的数据页来删除数据。 这意味着TRUNCATE类似于删除并重新创建表。 此外，它仅在事务日志中记录页面取消分配，而不像DELETE语句那样按行记录。

(Setting up the Environment)

Let us create a sample table and populate a few records to demonstrate different aspects of TRUNCATE by using the following T-SQL code.

让我们创建一个示例表并填充一些记录，以使用以下T-SQL代码演示TRUNCATE的不同方面。

CREATE TABLE SampleTable
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
NAME CHAR(200),
ADDRESS CHAR(1000))
GO 
INSERT INTO [dbo].[SampleTable]
        ([Name]  ,[Address])
    VALUES
        ('AAA' ,'CCCC')

From the above T-SQL code, a sample table called SampleTable is created and an adequate number of sample records were populated.

从上面的T-SQL代码中，创建了一个名为SampleTable的示例表，并填充了足够数量的示例记录。

To truncate the table, following T-SQL command can be executed.

要截断该表，可以执行以下T-SQL命令。

TRUNCATE TABLE [SampleTable]

It is important to note that there is no WHERE clause in the TRUNCATE statement. TRUNCATE option is available in SQL Server all editions, Azure SQL Server and Azure data warehouse instances.

重要的是要注意，TRUNCATE语句中没有WHERE子句。 在SQL Server所有版本，Azure SQL Server和Azure数据仓库实例中都可以使用TRUNCATE选项。

(Comparison of Resources)

Let us compare the resource consumptions between the TRUNCATE and DELETE statements. This was done to the same table with 500,000 records.

让我们比较一下TRUNCATE和DELETE语句之间的资源消耗。 这对具有500,000条记录的同一张表进行了处理。

Statement	CPU	Reads	Writes	Duration	Row Count
TRUNCATE	0	52	0	0	0
DELETE	3,297	1,509,542	84,090	99,905	500,000

声明	中央处理器	读	写	持续时间	行数
截短	0	52	0	0	0
删除	3,297	1,509,542	84,090	99,905	500,000

There is nothing to compare between TRUNCATE and DELETE as observed in the above table. It is obvious that TRUNCATE is much faster and use fewer resources than the DELETE statement in all aspects.

如上表所示，在TRUNCATE和DELETE之间没有可比较的内容。 很明显，TRUNCATE在所有方面都比DELETE语句快得多，并且使用的资源更少。

(Comparison of Transaction Log File)

Let us compare the usage of transaction log file during the TRUNCATE and DELETE statements.

让我们比较一下TRUNCATE和DELETE语句期间事务日志文件的用法。

Following screenshot shows the log size after the inserts are done to the sample table which was created before.

下面的屏幕快照显示了对之前创建的示例表进行插入后的日志大小。

From the above screenshot, it can be seen that transaction log usage percentage is 0.3%.

从上面的屏幕截图可以看出，事务日志的使用百分比为0.3％。

Following is the log file usage after the DELETE statement is completed.

以下是DELETE语句完成后的日志文件使用情况。

As you can see from the above screenshot, transaction log usage has increased to 0.7% when those records are deleted via DELETE statement.

从上面的屏幕快照中可以看到，当通过DELETE语句删除这些记录时，事务日志的使用率已增加到0.7％。

Following is the screenshot for the log usage percentage when the data is deleted from the TRUNCATE statement.

以下是从TRUNCATE语句删除数据时日志使用率百分比的屏幕截图。

You will see from the above screenshot that, log usage has not grown at all. This means that during the TRUNCATE will be minimally logged in the transaction log than the DELETE statement.

您将从上面的屏幕截图中看到，日志使用量根本没有增加。 这意味着在TRUNCATE期间，与DELETE语句相比，最小记录在事务日志中。

Since TRUNCATE statement minimally logged in the Transaction Log, TRUNCATE is faster than DELETE statement. This is due to the fact that TRUNCATE is equivalent to dropping the table and recreating it. Since TRUNCATE will not consume large transaction log resources, transaction log backup file will be less in size and will improve the performances of recovery options such as mirroring and log shipping.

由于TRUNCATE语句最少记录在事务日志中，因此TRUNCATE比DELETE语句快。 这是由于TRUNCATE等效于删除表并重新创建它。 由于TRUNCATE不会消耗大量的事务日志资源，因此事务日志备份文件的大小会减小，并会提高诸如镜像和日志传送之类的恢复选项的性能。

(IDENTITY Property during TRUNCATE TABLE)

IDENTITY property is used in a table when you need to auto increase a number for a column. This means that when the first record is inserted IDENTITY column will become 1 and the next record will be 2 and so on. When entire data in the table is deleted what will happen to the next number.

当您需要为列自动增加数字时，可以在表中使用IDENTITY属性。 这意味着当插入第一条记录时，IDENTITY列将变为1，下一条记录将为2，依此类推。 删除表中的所有数据后，下一个数字将发生什么。

When the DELETE statement is executed, previous records are counted. Let us execute the following T-SQL to get the record count and maximum number for the identity column.

当执行DELETE语句时，将计算以前的记录。 让我们执行以下T-SQL来获取身份列的记录数和最大数目。

SELECT MAX(ID) [Current Number], 
COUNT(*) [Record Count]
FROM SampleTable

Following is the output for the above query.

以下是上述查询的输出。

You will see that though the record count is 1, the current maximum number of the identity column is 500001. This means that DELETE statement will not be impacted to IDENTITY column’s next value and DELETE statement will NOT reset the IDENTITY column even though there are no records.

您会看到，尽管记录计数为1，但identity列的当前最大数目为500001。这意味着DELETE语句将不会影响IDENTITY列的下一个值，即使没有IDLETE语句也不会重置IDENTITY列记录。

Let us look at the same behavior with the TRUNCATE statement.

让我们用TRUNCATE语句查看相同的行为。

You can see that with the TRUNCATE statement, IDENTITY column is reset.

您可以看到，使用TRUNCATE语句重置了IDENTITY列。

(Transactions)

Since when TRUNCATE statement is executed, fewer transactions log resources are used, rollback transaction will take less duration than DELETE statement even though, it the table has a large number of records.

由于执行TRUNCATE语句时，使用较少的事务日志资源，因此即使表具有大量记录，回滚事务所花费的时间也比DELETE语句少。

(Partition Truncations)

TRUNCATE cannot be executed with a WHERE clause means that all records will be removed from the TRUNCATE / statement. However, partitions can be truncated as shown in the below T-SQL statement.

无法使用WHERE子句执行TRUNCATE，这意味着将从TRUNCATE /语句中删除所有记录。 但是，分区可以被截断，如下面的T-SQL语句所示。

TRUNCATE TABLE [SampleTable]   
WITH (PARTITIONS (2, 4, 6 TO 8));  
GO

From the above statement, partitions 2,4,6,7,8 will be truncated leaving the other partitions data will not be truncated.

从上面的语句中，分区2,4,6,7,8将被截断，而其他分区数据将不被截断。

(Triggers)

Though DELETE statement will trigger the DELETE trigger, it is important to note that TRUNCATE will not trigger the DELETE trigger. Therefore, if your system needs to delete trigger to fire, the TRUNCATE TABLE statement should not be issued.

尽管DELETE语句将触发DELETE触发器，但需要注意的是TRUNCATE不会触发DELETE触发器。 因此，如果系统需要删除触发触发器，则不应发出TRUNCATE TABLE语句。

(Security)

TRUNCATE TABLE needs minimum alter table permission to a user to. This means that DELETE table permission is not sufficient to execute the TRUNCATE statement.

TRUNCATE TABLE需要用户具有最低的更改表权限。 这意味着DELETE表权限不足以执行TRUNCATE语句。

(Limitations)

TRUNCATE statement will be limited during the following scenarios.

在以下情况下，TRUNCATE语句将受到限制。

翻译自: https://www.sqlshack.com/truncate-table-operations-in-sql-server/