DELETE与TRUNCATE的区别

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)