MySQL删除数据的三种方式

本篇主要讨论MySQL删除数据的三种方式：delete、drop、truncate的区别

当行数据批量delete时，InnoDB如何处理自增ID的

参考来源：

https://www.51cto.com/article/708260.html

以下步骤根据上面的参考来源进行验证：

1、建表

CREATE DATABASE del;
use del;
CREATE TABLE ex(
id INT NOT NULL PRIMARY KEY auto_increment,
name VARCHAR(10) UNIQUE
);
-----------------------------------
CREATE TABLE ex_2(
id INT NOT NULL PRIMARY KEY auto_increment,
name VARCHAR(10) UNIQUE
);

2、插入数据

INSERT INTO ex VALUES(1,'name');
INSERT INTO ex_2 VALUES(1,'name');

-- 插入多条
INSERT INTO ex(name) VALUES('name2'),('name3'),('name4')
INSERT INTO ex_2(name) VALUES('name2'),('name3'),('name4');

此时查看表：

3、删除数据

delete

-- 删除所有数据
DELETE FROM ex;

此时执行 SELECT * FROM ex：表结构还存在，数据已被删除

truncate

TRUNCATE ex_2;

此时执行 SELECT * FROM ex：表结构还存在，数据已被删除

drop

DROP TABLE ex_2;
# 1146 - Table 'del.ex_2' doesn't exist

！！！ 直接把表也给删了！

4、再次插入数据

使用过delect情况下：

INSERT INTO ex VALUES(0,'000');

INSERT INTO ex VALUES(1,'111');

INSERT INTO ex(name) VALUES('222');

现在再执行SELECT * FROM ex，发现：自增列计数并不会从头开始

使用过truncate情况下

-- 插入成功
INSERT INTO ex_2 VALUES(0,'000');
-- 插入失败：1062 - Duplicate entry '1' for key 'PRIMARY'
INSERT INTO ex_2 VALUES(1,'111');
-- 插入成功
INSERT INTO ex_2(name) VALUES('222');

现在再执行SELECT * FROM ex，发现：自增列计数复原

使用 drop 的话表都直接干没

5、结论

批量删除数据有三种常见的方法：

(1) drop table：直接表都删没；当不需要该表时，可以使用该方法。

(2) truncate table：删除所有数据，同时保留表，速度很快。

画外音：可以理解为，drop table然后再create table。

(3) delete from table：可以删除所有数据，也能保留表，但性能较差。也可以带where条件删除部分数据，灵活性强。

虽然truncate和delete都能够删除所有数据，且保留表，但他们之间是有明显差异的。

区别

(1)

• truncate是DDL语句，它不存在所谓的“事务回滚”；
• delete是DML语句，它执行完是可以rollback的。

(2)

• truncate table返回值是0；
• delete from table返回值是被删除的行数。

(3) InnoDB支持一个表一个文件，此时：

• truncate会一次性把表干掉，且不会激活触发器，速度非常快；
• delete from table则会一行一行删除，会激活触发器，速度比较慢。

画外音：delete数据，是要记录日志的，truncate表不需要记录日志。

(4) 当表中有列被其它表作为外键(foreign key)时：

• truncate会是失败；
• delete则会成功。

画外音：这类数据删除失败很容易定位问题，因为报错提示简单易懂。

(5) 当表中有自增列时：

• truncate会使得自增列计数复原;
• delete所有数据后，自增列计数并不会从头开始。

画外音：因此，delete所有数据后，自增列计数的这个行为，往往不是用户想要的，所以是一个潜在坑。