mysql truncate命令

truncate命令结果跟delete很像，但是他们的执行原理完全不一样。truncate本质还是ddl语句

1.truncate先使用create命令创建表，然后drop源表，最后rename新表。

2 drop只是删除元数据，所以比delete快很多，特别是大表

3 truncate本质是ddl，需要ddl权限。ddl本身是自提交的，所以truncate也不能rollback回滚

4 因为是truncate是重建表，所以truncate是可以整理表碎片的（delete不可以）

5 truncate在执行有外键约束的reference表时会失败

 

 

下面对truncate外键约束的reference表的测试

mysql> show create table testa;
| Table | Create Table                                                                                                                                                                                                                  
| testa | CREATE TABLE `testa` (
  `a` int(11) DEFAULT NULL,
  `b` char(4) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  KEY `idx_aaa` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.01 sec)

mysql> show create table testb;
| Table | Create Table                                                                                                  
| testb | CREATE TABLE `testb` (
  `a` int(11) DEFAULT NULL,
  KEY `idx_sam` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

mysql> select * from testb;
Empty set (0.00 sec)

mysql> alter table testb add constraint fk_testab foreign key (a) REFERENCES testa(a);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table testb;
| Table | Create Table                                                                                                  |
| testb | CREATE TABLE `testb` (
  `a` int(11) DEFAULT NULL,
  KEY `idx_sam` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
1 row in set (0.01 sec)

mysql> truncate table testa;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into testa values(1,'1',1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into testb values(1);
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

mysql> select * from testb;
Empty set (0.03 sec)

mysql>  alter table   testb drop  foreign key fk_testab;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into testb values(2);
Query OK, 1 row affected (0.02 sec)

--有外键约束的mysiam表，在开启GTID时无法insert数据

 

 

--创建innodb表进行测试
mysql>  create table testc(a int ,  foreign key (a)  REFERENCES testa(a)) ;    
Query OK, 0 rows affected (0.12 sec)

mysql> insert into testc values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into testc values(2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`lzldb`.`testc`, CONSTRAINT `testc_ibfk_1` FOREIGN KEY (`a`) REFERENCES `testa` (`a`))

mysql> truncate table testa;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`lzldb`.`testc`, CONSTRAINT `testc_ibfk_1` FOREIGN KEY (`a`) REFERENCES `lzldb`.`testa` (`a`))