The difference between delete and truncate

In MySQL, delete and truncate all have the ability to clear table data and preserve the table structure. But there are some differences between the 2 commands.

The difference between the two is that, even in an environment that supports transactions, delete can be rolled back, and truncate cannot be rolled back.

Look at the following example:

> Use Hellodb;

> select * from COC; The original table contents are as follows:

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/84/22/wKiom1eGaNfzFJh_AABhiV-zi0o355.jpg "title=" 1.jpg " Style= "Float:none;" alt= "wkiom1eganfzfjh_aabhiv-zi0o355.jpg"/>

# #delete方式

> begin;

> Delete from COC;

> rollback;

> select * from COC; And now the data is back up.

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/84/22/wKiom1eGaNfzFJh_AABhiV-zi0o355.jpg "title=" 1.jpg " Style= "Float:none;" alt= "wkiom1eganfzfjh_aabhiv-zi0o355.jpg"/>

# #truncate方式

> begin;

> Truncate COC;

> rollback;

> select * from COC; At this point the data can no longer be restored, such as:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/84/22/wKiom1eGaNjzQnuCAAB_N82sdTE147.jpg "style=" float: none; "title=" 3.jpg "alt=" Wkiom1eganjzqnucaab_n82sdte147.jpg "/>

The difference between delete and truncate