DELETE 在删除数据的时候,系统需要记录每一个命令,以便在需要时能够撤回该命令,DELETE将需要使用表所占用空间的两倍.
TRUNCATE在删除数据的时候不记录日志.所需的时间比较快,所占用的磁盘空间也比较小.
DROP TABLE直接删除表,而不是表中的数据.
DELETE与TRUNCATE区别如下:
mysql> set AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Account;
+----------+
| count(*) |
+----------+
| 557547 |
+----------+
1 row in set (0.87 sec)
mysql> delete from Account;
Query OK, 557547 rows affected (9.57 sec)
mysql> rollback;
Query OK, 0 rows affected (8.01 sec)
mysql> select count(*) from Account;
+----------+
| count(*) |
+----------+
| 557547 |
+----------+
1 row in set (0.10 sec)
mysql> truncate table Account;
Query OK, 0 rows affected (0.95 sec)
mysql> select count(*) from Account;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Account;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
