mysql删除表数据几种情况

1、drop table table_name : 删除表全部数据和表结构,立刻释放磁盘空间,不管是 Innodb 和 MyISAM;

实例,删除学生表:

drop table student;

2、truncate table table_name : 删除表全部数据,保留表结构,立刻释放磁盘空间 ,不管是 Innodb 和 MyISAM;

实例,删除学生表:

truncate table student;

3、delete from table_name : 删除表全部数据,表结构不变,对于 MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;

实例,删除学生表:

delete from student;

4、delete from table_name where xxx : 带条件的删除,表结构不变,不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

实例,删除学生表中姓名为 "张三" 的数据:

delete from student where T_name = "张三";

5、delete 操作以后,使用 optimize table table_name 会立刻释放磁盘空间,不管是 innodb 还是 myisam;

实例,删除学生表中姓名为 "张三" 的数据:

delete from student where T_name = "张三";

实例,释放学生表的表空间:

optimize table student;

6、delete from 表以后虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以使用这部分空间。

总结

1、当你不再需要该表时, 用 drop;

2、当你仍要保留该表,但要删除所有记录时, 用 truncate;

3、当你要删除部分记录时, 用 delete。

 

mysql  truncate、delete与drop区别

相同点:

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。

 

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

 

 

不同点:

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。
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。

 

6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

 

7、TRUNCATE   TABLE   在功能上与不带   WHERE   子句的   DELETE   语句相同:二者均删除表中的全部行。但   TRUNCATE   TABLE   比   DELETE   速度快,且使用的系统和事务日志资源少。DELETE   语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE   TABLE   通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。 

 

8、TRUNCATE   TABLE   删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用   DELETE。如果要删除表定义及其数据,请使用   DROP   TABLE   语句。  
    
9、对于由   FOREIGN   KEY   约束引用的表,不能使用   TRUNCATE   TABLE,而应使用不带   WHERE   子句的   DELETE   语句。由于   TRUNCATE   TABLE   不记录在日志中,所以它不能激活触发器。    
 

10、TRUNCATE   TABLE   不能用于参与了索引视图的表。  

 

欢迎关注公众号:日常bug,每天写至少一篇技术文章,每天进步一点点。

### MySQL 删除数据后行数未归零的原因 当执行 `DELETE` 或者 `TRUNCATE TABLE` 命令来清空中的数据时,物理上并不会立即释放磁盘空间。对于 InnoDB 存储引擎而言,在删除操作之后,尽管逻辑上的记录被标记为已删除,但实际的空间回收机制取决于具体的实现细节[^3]。 #### DELETE 和 TRUNCATE 的区别 - **DELETE**: 这种方法逐行删除符合条件的数据,并且会触发任何定义好的触发器动作。它是一个事务安全的操作,支持回滚。然而,这种方式效率较低,特别是针对大。 - **TRUNCATE TABLE**: 此命令通过重置结构并重新分配新的段(segment)来快速移除所有行。这是一个DDL语句而非DML语句,所以无法撤销此更改。通常情况下,这比使用 DELETE 更高效得多,因为它不会产生大量日志条目也不涉及单个行锁。 即使使用了上述任一方式清除数据,由于InnoDB内部维护着一些元数据信息如隐藏列(trx_id, roll_pointer),这些辅助性的管理开销可能导致统计出来的“行数”看起来并没有完全回到初始状态。 ### 如何使行数真正归零 为了确保在删除全部数据后能够看到预期的行为——即显示的行数确实变为0,有几种可行的方法: 1. 使用 `OPTIMIZE TABLE` 来整理碎片化存储空间,重建索引树,从而让后续查询得到更精确的结果集大小估计值; 2. 如果不需要保留现有结构,则可以直接 DROP 并重新 CREATE ; 3. 对于某些版本的MySQL,还可以尝试设置参数 `innodb_file_per_table=ON` 后再做 truncate 操作,这样可以让文件系统层面也彻底清理掉该占用的空间资源[^4]。 ```sql -- 方法 1: Optimize Table OPTIMIZE TABLE your_table_name; -- 方法 2: Drop and Recreate Table (仅适用于无需保存原结构的情况) DROP TABLE IF EXISTS your_table_name; CREATE TABLE your_table_name (...); -- 方法 3: 设置 innodb_file_per_table 参数后再truncate SET GLOBAL innodb_file_per_table = ON; TRUNCATE TABLE your_table_name; ``` 请注意,具体采取哪种措施应考虑实际情况下的需求权衡利弊,比如性能影响、业务连续性等因素。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值