DELETE、DROP和TRUNCATE：详细比较

DELETE、DROP和TRUNCATE：详细比较

1. DELETE 命令

用途：

DELETE 用于根据指定条件从表中删除特定行。

特点：

• 逐行删除数据

• 可以回滚（支持事务）

• 会触发触发器

• 保留表结构不变

• 相比 TRUNCATE，删除所有行时速度较慢

语法和示例：

DELETE FROM 表名 WHERE 条件;
​
-- 示例：删除工资低于30000的所有员工
DELETE FROM employees WHERE salary < 30000;
​
-- 示例：删除表中所有行
DELETE FROM employees;

性能考虑：

• 对于大表，DELETE 可能会很慢，特别是在删除所有行时

• 生成单独的行锁，可能影响并发性

• 记录每个被删除行的日志，可能导致较大的事务日志

2. TRUNCATE 命令

用途：

TRUNCATE 用于快速删除表中的所有行。

特点：

• 一次性删除所有行

• 在大多数数据库中不能回滚（不完全支持事务）

• 不触发行级触发器

• 重置标识/自增列

• 删除所有行时比 DELETE 快得多

• 保留表结构不变

语法和示例：

TRUNCATE TABLE 表名;
-- 示例：删除employees表中的所有数据
TRUNCATE TABLE employees;

性能考虑：

• 速度很快，特别是对大表

• 最小化日志记录（仅记录数据页的释放）

• 在大多数数据库中立即释放表使用的空间

3. DROP 命令

用途：

DROP 用于从数据库中删除整个表结构。

特点：

• 删除整个表，包括结构、索引和约束

• 不能回滚（不支持事务）

• 从数据库中删除所有数据和表定义

• 比 DELETE 和 TRUNCATE 都快

语法和示例：

DROP TABLE 表名;
​
-- 示例：从数据库中删除整个employees表
DROP TABLE employees;
​
-- 示例：如果表存在则删除（避免错误）
DROP TABLE IF EXISTS employees;

性能考虑：

• 极快，因为它只是从系统目录中删除表元数据

• 最小化日志记录

• 立即释放表使用的所有空间

比较表

特性	DELETE	TRUNCATE	DROP
范围	行	所有行	整个表
速度	最慢	快	最快
回滚	可以	通常不可以	不可以
触发器	触发	不触发	不适用
条件	可以使用 WHERE 子句	不能使用 WHERE 子句	不适用
表结构	保留	保留	删除
自增重置	否	是	不适用
空间释放	不立即	大多数情况下立即	立即
事务日志	记录每一行	最小化日志	最小化日志

使用场景

• DELETE：当你需要删除特定行或需要能够回滚操作时。

• TRUNCATE：当你想快速删除表中的所有数据，但保留表结构时。

• DROP：当你想从数据库中完全删除表，包括其结构和数据时。

其他注意事项

1. 权限：DROP 通常需要比 DELETE 或 TRUNCATE 更高的权限。

2. 引用完整性：外键约束可能会阻止 TRUNCATE 操作或要求级联 DELETE 操作。

3. 审计：如果需要跟踪单个行的删除，请使用 DELETE 而不是 TRUNCATE。

4. 恢复：通过 TRUNCATE 或 DROP 删除的数据通常比通过 DELETE 删除的数据更难恢复。

请注意，不同数据库管理系统之间的具体行为可能略有不同，因此请务必查阅您特定数据库的文档以获取准确详情。

创建表时实现主键自增长以及清空删除表时自增计时器的变化

create table book_type 
( 
book_id int not null auto_increment, 
book_name varchar(255) not null, 
book_desc varchar(255)not null, 
primary key(book_id), UNIQUE(book_name) 
)
engine=INNODB auto_increment=1001 default charset=gbk;

• book_id: 这是一个整型（INT）字段，不允许为空（NOT NULL），自动递增（AUTO_INCREMENT）。这意味着每当向表中插入新记录时，book_id的值会自动增加，且从1001开始（因为您设置了auto_increment=1001）。

TRUNCATE table book_type;以后那个自增ID就会变成从1开始

使用TRUNCATE

TRUNCATE TABLE book_type;

命令时，表中的所有数据会被删除，而且自增ID（如果存在）会被重置为初始值，通常是1。这是因为TRUNCATE`不仅仅清空数据，它还会重置表的某些元数据，包括自增列的计数器，使之回到初始状态。

如果你希望在清空表后自增ID从某个特定值（比如1001）开始，你应该在执行TRUNCATE之后，再手动设置自增ID的起始值。可以使用以下命令：

ALTER TABLE book_type AUTO_INCREMENT = 1001;

使用DELETE

DELETE FROM book_type;
INSERT INTO book_type (book_name, book_desc) VALUES ('Book Title 2', 'Description for Book 2');
INSERT INTO book_type (book_name, book_desc) VALUES ('Book Title 3', 'Description for Book 3');

DELETE语句也可以用来删除表中的所有数据，但是它会为每一条删除的记录生成一个日志，这在大数据量时可能会比较慢，并且会占用更多的日志空间。另外，如果有删除触发器，它们会被执行

使用delete删除，自增计数器会保持在最后一次分配的值上，即不清空计时器，不会从1001开始。

这是因为 TRUNCATE 相当于重新创建了一个新表，原有的自增计数器状态丢失，回到了定义时的状态或默认的初始值。 而使用 DELETE FROM book_type; 命令时，虽然表中的数据同样被删除，但是自增计数器的值不会被重置。

只能通过命令重新设置

ALTER TABLE book_type AUTO_INCREMENT = 1001;

使用DROP

DROP TABLE 这个命令是用来完全删除一个表的结构以及所有数据、索引、触发器、约束等相关的元数据。执行后，表将不再存在于数据库中，直到你再次创建它。因此，表的自增计数器状态也会丢失，因为整个表的定义都已被移除。