MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / SQL Statements / Question No: 158

What is difference between delete and truncate statements of MySQL?

MySQL TRUNCATE TABLE and DELETE statements are used to delete table rows. But I am not clear about their differences. Can you help me to explain the difference between DELETE and TRUNCATE statements of MySQL?

Answer No: 158

Logically, TRUNCATE statement is equivalent to a DELETE statement that deletes all rows from the table, but there are important differences in some cases.  The following list of TRUNCATE and DELETE statements' features help us to understand their working and differences respectively:

DELETE statement features

  • DELETE statement deletes table rows and returns number of rows deleted. 
  • DELETE statement has two modifiers namely LOW_PRIORITY and QUICK. With LOW_PRIORITY keyword, DELETE execution is delayed until no other clients are reading from the table. If QUICK modifier is specified then the table handler does not merge index leaves during delete, this may cause to speed up certain kind of deletes. 
  • If WHERE clause is specified then rows are deleted satisfying the given conditions and finally returns the number of rows deleted. 
  • If a DELETE is issued with no WHERE clause, all rows are deleted. 
  • With no  WHERE clause  in AUTOCOMMIT mode, DELETE  works as TRUNCATE and does not return affected rows. 
  • ORDER BY clause can be used in DELETE statement. In this case, the rows are deleted in the specified order.
  • LIMIT clause can also be used to set a limit on the number of rows to be deleted.

In MySQL 3.23, DELETE without a WHERE clause does not return number of affected records instead returns zero.  However, if it is necessary to know how many records are deleted, the following DELETE statement can be used:

DELETE FROM table_name WHERE 1>0;

By the above method, you will have to suffer a speed penalty, because it deletes the rows one at a time. Keep in mind DELETE without WHERE clause works faster considerably.

TRUNCATE statement features

TRUNCATE TABLE differs from DELETE in the following ways excluding InnoDB tables:

  • TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one.
  • TRUNCATE does not return number of deleted rows.
  • TRUNCATE is not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.
  • With TRUNCATE TABLE, a table can be re-created (as an empty table) when only the table format file tbl_name.frm is valid, and its data or index files have become corrupted.
  • With TRUNCATE TABLE, the table handler reset the AUTO_INCREMENT value starting from the beginning. This is true even for MyISAM and InnoDB engines.
  • In case of partitioned tables, TRUNCATE TABLE drops the data and index files and re-creates but preserves the partition definitions (.par) file.

For InnoDB tables, TRUNCATE TABLE works similar to DELETE if there are foreign key constraints that reference the table; otherwise it simply drops and re-creates the table. The AUTO_INCREMENT value is reset by TRUNCATE TABLE even ignoring  foreign key constraint, if there is.

TRUNCATE  funtionality is an Oracle SQL extension adopted in MySQL.

Related MySQL FAQs to the Above FAQ

What-does-alias-means-in-MySQL What does alias means in MySQL?

What-does-SQL-Expressions-mean-in-MySQL What does SQL Expressions mean in MySQL?

How-to-use-numeric-expressions-in-MySQL How to use numeric expressions in MySQL?

How-to-use-string-expressions-in-MySQL How to use string expressions in MySQL?

How-to-manage-case-sensitivity-in-string-comparisons How to manage case sensitivity in string comparisons?

How-to-use-LIKE-pattern-matching-operator How to use LIKE pattern-matching operator?

How-to-use-date-or-temporal-expressions How to use date or temporal expressions?

What-does-NULL-value-mean-in-MySQL What does NULL value mean in MySQL?

How-to-use-comments-in-SQL-statements How to use comments in SQL statements?

How-to-validate-user-login-in-case-sensitive-manner How to validate user login in case sensitive manner?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.