MySQL - DELETE and TRUNCATE TABLE

mysql

DELETE FROM customer WHERE id=1005;
TRUNCATE TABLE table_name

The word TABLE in TRUNCATE TABLE is optional. To remove only specific records in a table, TRUNCATE TABLE cannot be used. You must use DELETE with a WHERE clause. When you omit the WHERE clause from DELETE, it is logically equivalent to TRUNCATE TABLE, but there are operational differences.

If you need to know how may records were deleted, DELETE returns a true row count, but TRUNCATE TABLE returns 0.

If a table contains an AUTO_INCREMENT column, emptying it completely with TRUNCATE TABLE might have the side effect of resetting the sequence. This also happens for DELETE statement that does not include WHERE clause. If this side effect is undesirable, use a WHERE clause that always evaluates to true:

DELETE FROM table_name WHERE 1;

The present of the WHERE clause cause MySQL to evaluate it for each row, so the effect of the WHERE clause is to produce a row-by-row table-emptying operation. This form of DELETE (with a WHERE clause that always evaluate to true) avoid the side effect of resetting AUTO_INCREMENT sequence, but execute much more slowly than a DELETE with no WHERE.

ORDER BY and LIMIT can be used with DELETE to provide better control.

DELETE also supports a multi-table syntax that enables you to delete records from a table based on the content of another table. This syntax also allows records to be deleted from multiple tables simultaneously.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License