The difference between MySQL drop, delete, and truncate

Note: Delete In this case refers to the DELETE statement without a WHERE clause
same Point
truncate and delete without a WHERE clause, and drop deletes the data in the table

different points:
1. Truncate and delete only delete data without deleting the structure of the table (definition)
The drop statement will delete the structure of the table that is dependent on the constraint (constrain), the trigger (trigger), index, or the stored procedure/function that depends on the table will remain, but becomes invalid state.
The 2.delete statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.
Truncate,drop is DDL, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger the trigger.
The 3.delete statement does not affect the extent occupied by the table, and the high waterline (watermark) remains in its original position.
apparently the drop statement frees all the space occupied by the table.
The TRUNCATE statement releases space to minextents extent by default, unless reuse storage is used, and truncate resets the high watermark (back to the beginning).
4. Speed, in general: drop>; truncate >; Delete
5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.
on use, to delete some data rows with delete, note the WHERE clause. The rollback segment is large enough.
want to delete the table, of course with drop
You want to keep the table and delete all the data. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete.
If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/Insert Data

in layman's words:

in practical applications, the difference between the three is clear.
when you no longer need the table, use drop;
when you still want to keep the table, but to delete all records, use truncate;
when you want to delete some records (always with a WHERE clause), use Delete.

The difference between MySQL drop, delete, and truncate