1、一个表要删除全部数据如何实现：①delete，②truncate

EG：删除a表中的所有数据：

CREATE TABLE `b` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=16711412 DEFAULT CHARSET=utf8;

快速导入数据：

insert into a(name) values('qcj');

insert into a(name) values('qcj_a');

insert into a(name) values('qcj_b');

insert into a(name) select name from a;

mysql> insert into a(name) select name from a;

Query OK, 25165824 rows affected (32 min 53.16 sec)

Records: 25165824 Duplicates: 0 Warnings:

将a表重新复制一份b

测试：

复制表a，delete和truncate分别删除表测试：

mysql> truncate table b;

Query OK, 0 rows affected (5.79 sec)

mysql> delete from a;

Query OK, 50331648 rows affected (32 min 29.32 sec)

show tables status like 'table_name'

mysql> show table status like 'a'\G;

*************************** 1. row ***************************

Name: a

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 1509949440

Max_data_length: 0

Index_length: 0

Data_free: 335544320

Auto_increment: 50920682

Create_time: 2016-03-23 14:44:34

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql> show table status like 'b'\G;

*************************** 1. row ***************************

Name: b

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 1

Create_time: 2016-03-23 15:22:55

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

 

区别：

1.delete 删除表后，会存在碎片 并且 删除后，不会自增ID号的派发

 truncate 速度快，并且自增id 会被初始化 

2.truncate只能一次清空，不能按条件删除。但是delete可以按条件清除部分记录。 

3.truncate清空数据表性能（速度）比delete快。 

4.truncate不会记录到系统日志，不会触发delete触发器