Mysql中Truncate用法详解
mysql 详解 用法 truncate
2023-09-11 14:16:37 时间
前言:
1.当我们想要清空某张表时,往往会使用truncate语句。大多时候我们只关心能否满足需求,而不去想这类语句的使用场景及注意事项。本篇文章主要介绍truncate语句的使用方法及注意事项。
1.truncate使用语法 truncate的作用是清空表或者说是截断表,只能作用于表。truncate的语法很简单,后面直接跟表名即可,例如: truncate table tbl_name 或者 truncate tbl_name 。 执行truncate语句需要拥有表的drop权限,从逻辑上讲,truncate table类似于delete删除所有行的语句或drop table然后再create table语句的组合。
为了实现高性能,它绕过了删除数据的DML方法,因此,它不能回滚。尽管truncate table与delete相似,但它被分类为DDL语句而不是DML语句。
2.truncate与drop,delete的对比(重要)
上面说过truncate与delete,drop很相似,其实这三者还是与很大的不同的,下面简单对比下三者的异同。
truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
truncate只能作用于表;delete,drop可作用于表、视图等。
truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
truncate会重置表的自增值;delete不会。
truncate不会激活与表有关的删除触发器;delete可以。
truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。
TRUNCATE 关键字用于完全清空一个表。其语法格式如下:
TRUNCATE [TABLE] 表名
其中,TABLE 关键字可省略。
例 1
新建表 tb_student_course,插入数据并查询,SQL 语句和运行结果如下:
mysql> CREATE TABLE `tb_student_course` ( -> `id` int(4) NOT NULL AUTO_INCREMENT, -> `name` varchar(25) NOT NULL, -> PRIMARY KEY (`id`) -> ); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO tb_student_course(name) VALUES ('Java'),('MySQL'),('Python'); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_student_course; +----+--------+ | id | name | +----+--------+ | 1 | Java | | 2 | MySQL | | 3 | Python | +----+--------+ 3 rows in set (0.00 sec)
使用 TRUNCATE 语句清空 tb_student_course 表中的记录,SQL 语句和运行结果如下:
mysql> TRUNCATE TABLE tb_student_course; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM tb_student_course; Empty set (0.00 sec)
TRUNCATE 和 DELETE 的区别(再次强调) 从逻辑上说,TRUNCATE 语句与 DELETE 语句作用相同,但是在某些情况下,两者在使用上有所区别。 DELETE 是 DML 类型的语句;TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。 DELETE 是逐行一条一条删除记录的;TRUNCATE 则是直接删除原来的表,再重新创建一个一模一样的新表,而不是逐行删除表中的数据,执行数据比 DELETE 快。
因此需要删除表中全部的数据行时,尽量使用 TRUNCATE 语句, 可以缩短执行时间。 DELETE 删除数据后,配合事件回滚可以找回数据;TRUNCATE 不支持事务的回滚,数据删除后无法找回。 DELETE 删除数据后,系统不会重新设置自增字段的计数器;TRUNCATE 清空表记录后,系统会重新设置自增字段的计数器。 DELETE 的使用范围更广,因为它可以通过 WHERE 子句指定条件来删除部分数据;而 TRUNCATE 不支持 WHERE 子句,只能删除整体。 DELETE 会返回删除数据的行数,但是 TRUNCATE 只会返回 0,没有任何意义。
3.truncate使用场景及注意事项
通过前面介绍,我们很容易得出truncate语句的使用场景,即该表数据完全不需要时可以用truncate。
如果想删除部分数据用delete,注意带上where子句;如果想删除表,当然用drop;如果想保留表而将所有数据删除且和事务无关,用truncate即可;
如果和事务有关,或者想触发trigger,还是用delete;如果是整理表内部的碎片,可以用truncate然后再重新插入数据。
无论怎样,truncate表都是高危操作,特别是在生产环境要更加小心,下面列出几点注意事项,希望大家使用时可以做下参考。
truncate无法通过binlog回滚。
truncate会清空所有数据且执行速度很快。
truncate不能对有外键约束引用的表使用。
执行truncate需要drop权限,不建议给账号drop权限。
执行truncate前一定要再三检查确认,最好提前备份下表数据。
相关文章
- 【C/C++学院】(23)Mysql数据库编程--C语言编程实现mysql客户端
- mysql数据库表间内外链接详解
- Mysql第八天 分区与分表
- mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
- MySQL触发器更新本表数据异常:Can't update table 'tbl' in stored function/trigger because it
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- mysql的存储过程
- Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
- mysql中 where in 用法详解
- MySQL group_concat()详解
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- [转]mysql 存储过程中使用多游标
- MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写
- [手游新项目历程]-54-c,MySQL如何复制表中的一条记录并插入
- replace into 详解 update mysql
- MySQL技术内幕读书笔记(一)——Mysql体系结构和存储引擎
- Mysql distinct语法详解
- Mysql之安全清理mysql-slow.log
- MySQL基础篇 | 聚合(分组)函数 、分组查询
- mysql 父子节点查询