在Linux中清除数据表中的数据可以使用truncate和delete命令
COMMIT:一旦执行COMMIT,数据将被永久保存至数据库中,意味数据不能回滚
ROLLBACK:回滚数据,一旦执行ROLLBACK,则可以实现数据回滚,回滚至最近一次COMMIT之后
对比TRUNCATE和DELETE FROM
相同点:都可以实现数据表中数据的删除,TRUNCATE删除表时会保留表结构
不同点:
TRUNCATE TABLE:一旦执行此操作,表数据全部清除,同时不能回滚
DELETE FROM:一旦执行此操作,表数据会全部删除(不带WHERE),数据可以实现回滚。
DDL和DML
DDL的操作一旦执行,不可回滚,指令SET autocommit=false对DDL操作失效。(因为执行完DDL语句后,一定会执行一次COMMIT,而此COMMIT操作不受SET autocommit=false影响)
DML的操作默认情况下,一旦执行,也不可以回滚,但如果在执行DML之前,使用了SET autocommit=false(默认autocommit为true),则执行的DML操作可以实现回滚
delete from可回滚
mysql> select * from myemp3;
+-------------+-------------+------------------+
| employee_id | last_name | department_name |
+-------------+-------------+------------------+
| 200 | Whalen | Administration |
| 201 | Hartstein | Marketing |
| 202 | Fay | Marketing |
... ...
| 110 | Chen | Finance |
| 111 | Sciarra | Finance |
| 112 | Urman | Finance |
| 113 | Popp | Finance |
| 205 | Higgins | Accounting |
| 206 | Gietz | Accounting |
+-------------+-------------+------------------+
106 rows in set (0.01 sec)
mysql> set autocommit=false;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from myemp3;
Query OK, 106 rows affected (0.01 sec)
mysql> select * from myemp3;
Empty set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myemp3;
+-------------+-------------+------------------+
| employee_id | last_name | department_name |
+-------------+-------------+------------------+
| 200 | Whalen | Administration |
| 201 | Hartstein | Marketing |
| 202 | Fay | Marketing |
... ...
| 110 | Chen | Finance |
| 111 | Sciarra | Finance |
| 112 | Urman | Finance |
| 113 | Popp | Finance |
| 205 | Higgins | Accounting |
| 206 | Gietz | Accounting |
+-------------+-------------+------------------+
106 rows in set (0.00 sec)
truncate table,不能回滚
mysql> select * from myemp3;
+-------------+-------------+------------------+
| employee_id | last_name | department_name |
+-------------+-------------+------------------+
| 200 | Whalen | Administration |
| 201 | Hartstein | Marketing |
| 202 | Fay | Marketing |
... ...
| 110 | Chen | Finance |
| 111 | Sciarra | Finance |
| 112 | Urman | Finance |
| 113 | Popp | Finance |
| 205 | Higgins | Accounting |
| 206 | Gietz | Accounting |
+-------------+-------------+------------------+
106 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table myemp3;
Query OK, 0 rows affected (0.06 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myemp3;
Empty set (0.00 sec)
比较(truncate和delete)
TRUNCATE 比 DELEETE速度快,且使用的系统和事务日志资源少,但TRUNCATE无事务,且不触发TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句。
TRUNCATE与不带WHERE子句的DELETE语句相同