mysql的truncate说明

秒速五厘米 2022-06-01 09:46 92阅读 0赞

我们在编写mysql数据库时，经常用到delete和truncate这两个保留字，他们都具有删除的功能，但两者有什么区别呢？

--------------------

#### *创建数据库* ####

create database test;
    
    use test;  //使用数据库

--------------------

#### *创建两张测试表* ####

*  用户表

DROP TABLE IF EXISTS `account`;
    
    CREATE TABLE `account` ( `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT, `money` float(8,2) NOT NULL DEFAULT '0.00', `uid` int(11) NOT NULL, PRIMARY KEY (`id`), //主键 KEY `fk_user_account` (`uid`), CONSTRAINT `fk_user_account` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

*  账户表

DROP TABLE IF EXISTS `user`;
    
    CREATE TABLE `user` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `uname` char(20) NOT NULL, PRIMARY KEY (`uid`), //主键 KEY `idx_uname` (`uname`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--------------------

#### *添加数据* ####

mysql> insert into user(uname) values('tom'),('jack'),('rose');
    Query OK, 3 rows affected (0.01 sec)
    
    mysql> insert into account(money,uid) values(1200,1),(2000,2),(500,3);
    Query OK, 3 rows affected (0.01 sec)

--------------------

#### *分析数据* ####

##### 分析delete #####

delete是删除这条某条数据额记录，其主键的值没有被删掉，依旧隐藏在数据表中（因为我采用的是mysql的innodb的搜索引擎，这是支持外键的。）

//首先执行查询语句：
    mysql> select * from user order by uid asc; +-----+-------+
    | uid | uname | +-----+-------+
    |   1 | tom   |
    |   2 | jack  |
    | 3 | rose | +-----+-------+
    3 rows in set (0.00 sec)
    
    //删除uid为3的数据
    mysql> delete from user where uid=3;
    Query OK, 1 row affected (0.03 sec)
    
    //再添加两条数据
    mysql> insert into user(uname) values('mark'),('jane');
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    //再执行查询语句
    
    mysql> select * from user order by uid asc; +-----+-------+
    | uid | uname | +-----+-------+
    |   1 | tom   |
    |   2 | jack  |
    |   4 | mark  |
    | 5 | jane | +-----+-------+
    4 rows in set (0.00 sec)

你会发现，uid的编号不是从3开始的，而是从4开始的，因为我虽然删除了编号为3的数据，但uid为主键，主键的值没有被删除，暗含在数据表中的。

##### *分析truncate* #####

truncate是在没有外键限制的情况下，删除整个表数据的信息，其中也包括主键的值，但有外键情况的限制，那么删除就出现了问题：

![这里写图片描述][SouthEast]  
报错的信息是，因为外键的约束，不能truncate这张表。

把外键删掉，再truncate用户这张表，就可以了，比如以下代码：

mysql> truncate user;
    Query OK, 0 rows affected (0.03 sec)
    
    //执行查询语句
    mysql> select * from user;
    Empty set (0.00 sec)

这样就可以truncate用户表，此时，查询到的数据时空的，因而，我们在使用truncate这个保留字时，需要格外的谨慎，因为，它删的不是一条数据，而是整个数据。同时，我们再向用户表中添加数据，你就会发现：

mysql> insert into user(uname) values('tom'),('jack'),('rose');
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    
    mysql> insert into user(uname) values('mark'),('jane');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    //执行查询语句
    mysql> select * from user order by uid asc; +-----+-------+
    | uid | uname | +-----+-------+
    |   1 | tom   |
    |   2 | jack  |
    |   3 | rose  |
    |   4 | mark  |
    | 5 | jane | +-----+-------+
    5 rows in set (0.00 sec)

这样你就会发现，主键uid的值也改变了，但你需要注意的是，如果你非要truncate整张表，需要格外的谨慎：

1.  在没有外键的情况下
2.  在对数据进行备份的情。

这样才不会出现因失误造成的数据损失。

[SouthEast]: /images/20220601/c16605b77d974fcd80fa9c969c91ec33.png