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

互联网全栈开发实战

已于 2022-07-29 12:20:28 修改

阅读量1.8k

点赞数

分类专栏：免费专栏文章标签： mysql 索引外键 drop delete

于 2018-01-26 11:11:21 首次发布

本文链接：https://blog.csdn.net/lvoelife/article/details/79170129

版权

免费专栏专栏收录该内容

154 篇文章 19 订阅

订阅专栏

引言

我们在编写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)