文档章节

MySQL_TRUNCATE

秋风醉了
 秋风醉了
发布于 2014/10/30 19:20
字数 615
阅读 1977
收藏 1

MySQL_TRUNCATE

MySQL TRUNCATE 语法

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table completely. It requires the DROP privilege.

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.


Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL(数据库定义语言) statement rather than a DML(数据操作语言) statement. It differs from DELETE in the following ways in MySQL 5.7:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

  • Truncate operations cause an implicit(隐式) commit, and so cannot be rolled back.

  • Truncation operations cannot be performed if the session holds an active table lock.

  • TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

  • Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”

  • As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted(败坏的).

  • Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

  • When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

  • The TRUNCATE TABLE statement does not invoke ON DELETE triggers.


TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.


TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE—that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not permit statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug #36763) However, it is still applied on replication slaves using InnoDB in the manner described previously.


TRUNCATE TABLE can be used with Performance Schema summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows. 


测试TRUNCATE TABLE

有两个表t_parent和t_child,t_child表中有一个外键,关联t_parent

TRUNCATE t_parent;

执行这条语句报错:

[TRUNCATE - 0 row(s), 0.000 secs]  [Error Code: 1701, SQL State: 42000]  Cannot truncate a table referenced in a foreign key constraint (`test`.`t_child`, CONSTRAINT `FK_t_child` FOREIGN KEY (`parent_id`) REFERENCES `test`.`t_parent` (`id`))

Code: 1701 SQL State: HY000 --- Cannot truncate a table referenced in a foreign key constraint (`test`.`t_child`, CONSTRAINT `FK_t_child` FOREIGN KEY (`parent_id`) REFERENCES `test`.`t_parent` (`id`))

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

因为有外键关联

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE t_parent;
select count(*) from t_parent;

最后查出来的结果是0,首先可以取消外键关联,就可以TRUNCATE表。

============END==========

© 著作权归作者所有

秋风醉了
粉丝 250
博文 536
码字总数 408416
作品 0
朝阳
程序员
私信 提问
mysql中delete table和truncate table的区别

delete from 表名; truncate table 表名; 不带where参数的delete语句可以删除mysql表中所有内容,使用truncate table也可以清空mysql表中所有内容。 效率上truncate比delete快,但truncate删...

issac宝华
2016/03/14
52
0
mysql清空表:delete和truncate命令

清空mysql表中的记录有以下两种方法: delete from 表名; truncate table 表名; 不带where参数的delete语句可以删除mysql表中所有内容; 使用truncate table也可以清空mysql表中所有内容; ...

孟飞阳
2016/08/05
17
0
mysql系统信息查询(不完全,未做排版)

一)mysql> status 当前数据库的信息 二)mysql> show status(当前会话) 和show global status(全局) 查看服务器的状态信息 例如: 查看临时表:showglobal status like 'created_tmp%'; 三) m......

zxiaohe
2014/08/27
0
0
无备份情况下恢复MySQL truncate table

无备份情况下恢复MySQL truncate table love wife & love life --Roger2017-07-3119 阅读 deleteMySQL 本站文章除注明转载外,均为本站原创: 转载自 love wife & love life —Roger的Oracl...

love wife & love life --Roger
2017/07/31
0
0
zabbix 历史数据清理及libdata1文件过大处理

一 历史数据清理 停止相关服务,避免写入数据 /etc/init.d/zabbix-server stop /etc/init.d/httpd stop 清空历史数据 mysql -uroot -p use zabbix; truncate tablehistory; optimize tableh......

lrtao2010
2017/05/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

分享一波 RabbitMQ 面试题有答案

1、什么是rabbitmq 2、为什么要使用rabbitmq 3、使用rabbitmq的场景 4、如何确保消息正确地发送至RabbitMQ? 如何确保消息接收方消费了消息? 发送方确认模式 接收方确认机制 接收方消息确认...

搜云库技术团队
刚刚
0
0
2019年JAVA面试题(高级资深)

记录下本年度最新的面试题: 2019-04-24 //某互联网公司,劳工资源管理方向职位 1.bio/nio/aio介绍下,粘包、拆包问题怎么解决? 2.数据库四个特性是什么,事务传播性是怎么样的?spring事务和...

em_aaron
3分钟前
0
0
yarn如何全局安装命令以及和环境变量的关系

npm全局安装 npm i -g xxx yarn 全局安装 yarn global add xxx 然而你可能会发现npm全局安装后的命令可以直接使用,而yarn却不行,这是为什么呢? 我们来查看下npm和yarn的bin目录 使用npm全...

单线程生物
12分钟前
0
0
异步线程RequestContextHolder.getRequestAttributes()为null

使用Spring框架,在Service中开启一个新的线程,在新的线程中使用 RequestAttributes ra = RequestContextHolder.getRequestAttributes(); 获取出来为null,有没有什么办法能解决? 问题出现...

xiaomin0322
15分钟前
0
0
mingw64环境搭建

mingw64环境搭建 转自:http://www.cr173.com/soft/132367.html MinGW64位版,默认编译出来是64位的,需要编译32位请使用-m32 参数!mingw是一款gnu工具集合是Minimalist GNU on Windows的简称...

shzwork
18分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部