Mysql delete operation (delete + TRUNCATE) _ MySQL

This article mainly introduces the mysql delete operation, including the use of delete and TRUNCATE. For more information, see Delete

Syntax

Single table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_definition]
[Order by...]
[LIMIT row_count] multi-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 the DELETE statement you write does not contain the WHERE clause, all rows are deleted. If you do not want to know the number of rows to be deleted, you can use truncate table to accelerate the process.

The DELETE statement supports the following modifiers:

• If LOW_PRIORITY is specified, the execution of DELETE is delayed until no other client reads the table.
• For MyISAM tables, if you use the QUICK keyword, the storage engine does not merge the index end nodes during the deletion process, which can speed up the deletion of some types.
• The IGNORE keyword causes MySQL to IGNORE all errors during row deletion. (Errors encountered during the analysis phase will be handled in a regular manner .) Errors ignored due to the use of this option will be returned as a warning.
In the MyISAM table, deleted records are retained in a linked list, and subsequent INSERT operations will 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 re-orchestrate the TABLE. Optimize table is easier, but myisamchk is faster.

The QUICK modifier affects whether the index end nodes are merged during the delete operation. When the index value of the row to be deleted is replaced by a similar index value of the row inserted later, delete quick is the most suitable. In this case, the space left by the deleted value is reused.

If the index value of an unfilled index block spans a certain range, a new insert will occur again. Delete quick does not work when the deleted value causes unfilled index blocks. In this case, using QUICK may lead to obsolete space in unused indexes.

If you want to DELETE many rows from a TABLE, use delete quick with optimize table to speed up. In this way, indexes can be re-created, rather than a large number of index block merge operations.

The unique LIMIT row_count option of MySQL for DELETE is used to notify the server of the maximum value of the row deleted before the control command is returned to the client. This option ensures that a DELETE statement does not take too much time. You can repeat the DELETE statement until the number of related rows is less than the LIMIT value.

If the DELETE statement contains an order by clause, all rows are deleted in the ORDER specified in the clause. This clause takes effect only when used with LIMIT. For example, the following clause is used to find the row corresponding to the WHERE clause, use timestamp_column for classification, and delete the first (oldest) row:

Delete from somelog WHERE user = 'jcol' order by timestamp_column LIMIT 1; you can DELETE rows FROM multiple tables at the same time and use other tables for search:

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 you search for rows to be deleted, these statements use all three tables, but only delete the corresponding rows from table t1 and table t2.

The preceding example shows the internal union using the comma operator. However, the multi-table DELETE statement can use all types of union allowed in the SELECT statement, such as left join. However, you cannot use order by or LIMIT in a multi-table DELETE statement.

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

DELETE t1 FROM test AS t1, test2 WHERE...

You can delete multiple tables across databases. However, you cannot use aliases when referencing tables. Example:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE...

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

TRUNCATE

Syntax

TRUNCATE [TABLE] tbl_nameTRUNCATE TABLE is used to completely empty a TABLE. Logically, this statement is equivalent to the DELETE statement used to DELETE all rows, but in some cases, the two are used differently.

For an InnoDB TABLE, if there is a foreign key restriction for the TABLE to be referenced, the truncate table is mapped to the delete table; otherwise, you can use quick DELETE (cancel and recreate the TABLE ). Use truncate table to reset the AUTO_INCREMENT counter. when setting the counter, do not consider whether there is a foreign key restriction.

For other storage engines, the truncate table and delete from have the following differences:

• The delete operation will cancel and recreate the table, which is much faster than deleting a row.
• The delete operation cannot guarantee the transaction security. an error occurs when you try to delete the transaction during transaction processing and table locking.
• The number of deleted rows is not returned.
• If the TABLE definition file tbl_name.frm is valid, you can use truncate table to recreate the TABLE as an empty TABLE even if the data or index file has been damaged.
• The Table manager does not remember the AUTO_INCREMENT value that was last used, but starts counting from the beginning. Even for MyISAM and InnoDB. MyISAM and InnoDB generally do not use sequence values again.
• When used for a TABLE with partitions, the truncate table retains partitions. that is, the data and index files are canceled and re-created, and the partition definition (. par) files are not affected.
Truncate table is an Oracle SQL extension used in MySQL.