Truncate TABLE statement usage in MySQL

Source: Internet
Author: User
Tags table definition


TRUNCATE table deletes all rows in a table without recording a single row delete operation.

Grammar
TRUNCATE TABLE name;
Parameters
Name (the names of the tables you want to truncate or the names of the tables whose rows you want to delete)
TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.
The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data pages used to store the table data, and only records the release of the page in the transaction log.
TRUNCATE table deletes all rows in the tables, but the table structure and its columns, constraints, indexes, and so on remain unchanged. The count value used for the new line identification is reset to the seed of the column. If you want to keep the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table, but you should use a DELETE statement without a WHERE clause. Because the TRUNCATE TABLE is not logged in the log, it cannot activate the trigger.
TRUNCATE table cannot be used for tables that participate in indexed views.

Example

The following example deletes all the data in the authors table.
TRUNCATE TABLE authors;

Permissions

The TRUNCATE table permission is granted to table owners, members of the sysadmin fixed server role, db_owner and db_ddladmin fixed database role members and are not transferable

About the difference between delete and truncate

The biggest difference between delete and TRUNCATE TABLE is that delete can select the record to delete through the where statement. But the speed of execution is not fast. You can also return the number of records that were deleted. The TRUNCATE TABLE cannot delete the specified record, and the deleted record cannot be returned. But it executes very fast.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.