一、环境
创建一个表s_user（有主键且自增）
|
1 2 3 4 5 6 7 8 |
create table s_user( u_id int auto_increment primary key, u_name varchar(15), u_pwd varchar(15), u_truename varchar(20), u_role varchar(6), u_email varchar(30) ); |
插入几行数据
|
1 2 3 4 5 |
insert into s_user values (1,"wangc","aaaaaa","wangchao","buyer","wang@163.com"), (2,"huangfp","bbbbbb","huangfp","seller","yang@126.com"), (3,"zhang3","cccccc","zhangsan","buyer","zhang@163.com"), (4,"li4","dddddd","lisi","seller","li@1256.com"); |
创建一个表（有外键到s_user）
|
1 2 3 4 5 6 7 8 9 10 |
create table s_orderform( o_id int auto_increment primary key, o_buyer_id int, o_seller_id int, o_totalprices double, o_state varchar(50), o_information varchar(200), foreign key(o_buyer_id) references s_user(u_id), foreign key(o_seller_id) references s_user(u_id) ); |
二、需求分析
由于s_user有自增长，现在我想清空s_user表的信息并且把自增长给清空。一般执行truncate命令可以满足此需求，会把自增长给清空。但是在被其他表有外键关联的情况下，是不允许此操作的，以免破坏数据一致性。如下操作：
|
1 2 3 |
mysql> truncate s_user; ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`dd`.`s_orderform`, CONSTRAINT `s_orderform_ibfk_1` FOREIGN KEY (`o_buyer_id`) REFERENCES `dd`.`s_user` (`u_id`)) |
但MySQL中有一个变量可以解决此问题。
|
1 |
mysql> SET foreign_key_checks=0; |
表示关闭外键检查，执行完truncate后，再打开即可。
|
1 |
mysql> SET foreign_key_checks=1; |
三、truncate、drop、delete操作
现在来分别进行SQL语句的操作truncate、drop、delete，看看各自会出现的状况：
truncate
|
1 2 3 |
mysql> truncate s_user; ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`dd`.`s_orderform`, CONSTRAINT `s_orderform_ibfk_1` FOREIGN KEY (`o_buyer_id`) REFERENCES `dd`.`s_user` (`u_id`)) |
drop
|
1 2 |
mysql> drop table s_user; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails |
delete
|
1 2 |
mysql> delete from s_user; Query OK, 4 rows affected (0.01 sec) |
可以看到有外键关联的情况下，执行这三种命令会出现三种执行结果。有了直观的认识后，我们接着来说说这几个命令的区别，就知道了为什么又外键关联的情况下，会报如上错误。
四、truncate、drop、delete区别介绍
相同点：
1）truncate和delete以及drop都会删除表内的数据。
2）drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。而delete属于DML（数据操作语言），在事务中，提交才会生效。
不同点：
1）truncate 和 delete 只删除数据不删除表的结构（定义）
drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留，但是变为 invalid 状态。
2）delete 语句是数据库操作语言(dml)，这个操作会放到rollback segement中，事务提交之后才生效；如果有相应的trigger，执行的时候将被触发。
truncate、drop是数据库定义语言(ddl)，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。
3）delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不动
drop语句将表所占用的空间全部释放。
truncate语句缺省情况下见空间释放到minextents个extent，除非使用reuse storage；truncate会将高水线复位(回到最开始)。
4）速度对比
一般来说：drop> truncate > delete
5）安全性：小心使用drop和truncate，尤其没有备份的时候。
使用上，想删除部分数据行用delete，注意带上where子句，回滚段要足够大。
想删除表，当然用drop。
想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关，或者想触发trigger，还是用delete。
6）TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
7）TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETE。如果要删除表定义及其数据，请使用DROP TABLE 语句。
8）对于由FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHERE 子句的DELETE 语句，但是如果想清空自增长，必须使用truncate的话，那就按照上面的方法，关闭外键约束（foreign_key_checks=0）。因为不记录在日志中，所以它不能激活触发器。 为了实现高性能，TRUNCATE TABLE绕过删除数据的DML方法。因此，无法回滚，它不会导致ON DELETE触发器的触发，所以无法执行外键操作。
9）TRUNCATE TABLE不能用于参与了索引视图的表。
