MySQL delete operation (delete+truncate) _mysql

Delete

Grammar

Single table Syntax:

DELETE [low_priority] [QUICK] [IGNORE] from Tbl_name
[WHERE Where_definition]
[Order BY ...]
[LIMIT row_count] Multiple table syntax:

DELETE [low_priority] [QUICK] [IGNORE]
TBL_NAME[.*] [, tbl_name[.*] ...]
From Table_references
[WHERE where_definition] or

DELETE [low_priority] [QUICK] [IGNORE]
From tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[Where where_definition] if you write a DELETE statement that does not have a WHERE clause, all rows are deleted. When you don't want to know the number of rows that are deleted, there is a quicker way to use truncate TABLE.

The DELETE statement supports the following modifiers:

• If you specify low_priority, the execution of delete is deferred until no other client reads this table.
• For MyISAM tables, if you use quick keywords, the storage engine will not merge index-end nodes during the deletion process, which can speed up some kinds of deletions.
• In the process of deleting rows, ignore keywords will cause MySQL to ignore all errors. (Errors encountered during the analysis phase are handled in a regular manner.) Errors that are ignored due to the use of this option are returned as warnings.
In the MyISAM table, the deleted record is kept in a linked list, and subsequent inserts reuse the old record location. To reuse unused space and reduce the size of the file, use the Optimize table statement or the MYISAMCHK application to rearrange the tables. OPTIMIZE table is simpler, but myisamchk faster.

The quick modifier affects whether the index end node is merged in the delete operation. Delete Quick is most useful when the index value for the deleted row is replaced by a similar index value from the row that was inserted later. In this case, the space left by the deleted value is reused.

A new insertion occurs again when an index block that is not full spans a range of index values. Delete Quick does not work when the deleted value causes the not-filled index block to appear. In this case, using quick will result in discarded space in the unused index.

If you plan to remove many rows from a table, use delete quick plus optimize table to speed up. Doing so allows you to re-establish the index, rather than doing a large number of block merge operations.

The MySQL unique limit row_count option for delete tells the server the maximum value of the row that was deleted before the control command was returned to the client. This option is used to ensure that a DELETE statement does not consume too much time. You can repeat the DELETE statement only until the number of related rows is less than the limit value.

If the DELETE statement includes an ORDER BY clause, the rows are deleted in the sequence specified in the clause. This clause works only if it is associated with limit. For example, the following clause is used to find the row corresponding to the WHERE clause, to classify using timestamp_column, and to delete the first (oldest) row:

Delete from somelog WHERE user = ' jcole ' ORDER by Timestamp_column LIMIT 1; You can delete rows from many tables at the same time and use other tables for searching:

DELETE T1, t2 from T1, T2, T3 WHERE t1.id=t2.id and t2.id=t3.id;
--or
DELETE from T1, T2 USING T1, t2, T3 WHERE t1.id=t2.id and t2.id=t3.id;

When searching for rows to be deleted, the statements use all three tables, but only the corresponding rows are deleted from the table T1 and table T2.

The above example shows the inner union using the comma operator, but the multiple table DELETE statement can use all the types of unions allowed in the SELECT statement, such as a left JOIN, but you cannot use order by or limit in a multiple table DELETE statement.

When referencing a table name, you must use an alias (if given):

DELETE t1 from test as T1, test2 where ...

Cross-database deletions are supported when multiple table deletions are made, but in this case, you cannot use aliases when referencing tables. An example is provided:

DELETE test1.tmp1, test2.tmp2 from Test1.tmp1, TEST2.TMP2 where ...

Currently, you cannot delete from a table and select from the same table in a subquery.

TRUNCATE

Grammar

TRUNCATE [table] tbl_nametruncate table is used to completely empty a table. Logically, the statement is identical to the DELETE statement used to delete all rows, but in some cases the two are different in use.

For InnoDB tables, if there are foreign key restrictions that need to refer to the table, the TRUNCATE TABLE is mapped to delete, otherwise a quick cut (cancel and recreate the table) is used. Use the TRUNCATE table to reset the auto_increment counter, regardless of the foreign key limit when set.

For other storage engines, the TRUNCATE table differs from the delete from:

• The deletion will cancel and recreate the table, which is much faster than the row-by-row deletion line.
• The deletion operation does not guarantee that the transaction is safe, and an error occurs during transaction processing and table locking attempts to delete.
• The number of rows that were deleted is not returned.
• As long as the table definition file tbl_name.frm is legitimate, you can use TRUNCATE table to recreate the table as an empty table, even if the data or index files have been corrupted.
• The table Manager does not remember the last auto_increment value used, but it starts counting from scratch. This is true even for MyISAM and InnoDB. MyISAM and InnoDB typically do not use sequence values again.
• When used with partitioned tables, the TRUNCATE table preserves partitions, that is, the data and index files are canceled and recreated, and the partition definition (. par) file is not affected.
TRUNCATE table is an Oracle SQL extension that is used in MySQL.

Transferred from http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html