引言

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

创建测试表

登录数据库

PS C:\Users\zxy> mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 110
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

创建数据库

create database test;

use test;  //使用数据库

创建测试表

用户表


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;

创建账户表

user表作为账户表的外键，外键关系是fk_user_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;

插入数据


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)

分析数据

分析drop

drop语句将表所占用的空间全释放掉， drop > truncate > delete

方式1

如果要删除的数据库存在，则删除成功。如果不存在，则报错，如下代码所示：

mysql> drop table testAccount ;
ERROR 1051 (42S02): Unknown table 'test.testaccount'

表testAccount不存在，所以报出如上的错误。

方式2

推荐。如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错，如下代码所示：

mysql> drop table if exists testAccount;
Query OK, 0 rows affected, 1 warning (0.00 sec)

即便表testAccount不存在，也不会报出错误。

分析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是在没有外键限制的情况下，删除整个表数据的信息，其中也包括主键的值，但有外键情况的限制，那么删除就出现了问题，如下代码：

mysql> truncate user;

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`test`.`account`, CONSTRAINT `fk_user_account` FOREIGN KEY (`uid`) REFERENCES `test`.`user` (`uid`))

报错的信息是，因为外键的约束，不能truncate这张表。

删除外键

方式1

外键删除

mysql> alter table account drop foreign key fk_user_account;

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

成功删除外键。

查询外键

如下代码查询外键：

mysql> SELECT * from information_schema.key_column_usage where table_name = 'account' and CONSTRAINT_NAME <> 'primary';
Empty set (0.00 sec)

方式2

外键删除

当然，网上给出删除外键的代码，如下所示：

mysql> alter table account drop fk_user_account;

ERROR 1091 (42000): Can't DROP 'fk_user_account'; check that column/key exists

这种方式删除外键，代码会报错，且外键没有删除成功：

查询外键

 SELECT * from information_schema.key_column_usage where table_name = 'account' and CONSTRAINT_NAME <> 'primary';
+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def                | test              | fk_user_account | def           | test         | account    | uid         |                1 |                             1 | test                    | user                  | uid                    |
+--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
1 row in set (0.00 sec)

你会发现fk_user_account外键依然存在。

如果你想了解更多information_schema数据库的信息，可以参看这篇文档：https://blog.csdn.net/lvoelife/article/details/125873284

如果你想了解更多key_column_usage数据表的信息，也可以参看这篇文档：https://blog.csdn.net/lvoelife/article/details/125873284

删除外键索引

索引删除

我们在创建外键时，会自动创建外键索引，因而，当外键删除了，我们就要删除外键索引，如下代码所示：

mysql> alter table account drop index fk_user_account;

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

查询外键索引

mysql> SELECT * from information_schema.statistics where table_name = 'account' ;
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
| def           | test         | account    |          0 | test         | PRIMARY    |            1 | id          | A         |           3 |     NULL | NULL   |          | BTREE      |         |               |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+
1 row in set (0.00 sec)

通过表信息，外键索引也不存在了。

如果你想了解更多statistics 数据表的信息，也可以参看这篇文档：https://blog.csdn.net/lvoelife/article/details/125873284

truncate user

外键删除成功后，再truncate用户这张表，就可以了，如下代码：


mysql> truncate user;
Query OK, 0 rows affected (0.03 sec)

//执行查询语句
mysql> select * from user;
Empty set (0.00 sec)

这样就可以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整张表，需要格外的谨慎：

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

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

重要提醒

【重要提醒】：因而，我们在使用truncate这个保留字时，需要格外的谨慎，因为，它删的不是一条数据，而是整个数据。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/99288.html

mysql的delete truncate drop说明 mysql删除外键 mysql删除索引 mysql查询外键key_column_usage mysql查询索引tatistics

这里写目录标题

引言

创建测试表

登录数据库

创建数据库

创建测试表

用户表

创建账户表

插入数据

分析数据

分析drop

方式1

方式2

分析delete

分析truncate

删除外键

方式1

外键删除

查询外键

方式2

外键删除

查询外键

删除外键索引

索引删除

查询外键索引

truncate user

重要提醒

mysql的delete truncate drop说明 mysql删除外键 mysql删除索引 mysql查询外键key_column_usage mysql查询索引tatistics

这里写目录标题

引言

创建测试表

登录数据库

创建数据库

创建测试表

用户表

创建账户表

插入数据

分析数据

分析drop

方式1

方式2

分析delete

分析truncate

删除外键

方式1

外键删除

查询外键

方式2

外键删除

查询外键

删除外键索引

索引删除

查询外键索引

truncate user

重要提醒

相关推荐

分享到: