11.9 删除表

在本教程中，我们将向您展示如何使用MySQL DROP TABLE语句删除数据中存在的表。

MySQL DROP TABLE语句语法

要删除现有表，请使用MySQL DROP TABLE语句。 DROP TABLE的语法如下：

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] ...
[RESTRICT | CASCADE]

DROP TABLE语句从数据库中永久删除一个表及其数据。 在MySQL中，也可以使用单个DROP TABLE语句删除多个表，每个表之间用逗号(，)分隔。

TEMPORARY标志指定仅删除临时表。用于以确保不会意外删除非临时表时非常方便。

IF EXISTS添加可帮助防止删除不存在的表。当使用IF EXISTS添加时，MySQL生成一个”提示信息”，可以使用SHOW WARNING语句检索。 重要的是要注意，当在指定删除表的列表中存在不存在的表时，DROP TABLE语句将删除所有现有表并发出错误消息或“提示信息”。

如上所述，DROP TABLE语句仅删除表及其数据。 但是，它不会删除与表关联的特定用户权限。 因此，如果在此之后重新创建了具有相同名称的表，则现有权限将适用于新表，这可能会导致安全风险。

RESTRICT和CASCADE标志为未来版本的MySQL保留。最后但并非最不重要的是，必须具有要删除的表的DROP权限。

MySQL DROP TABLE示例

我们将删除在使用CREATE TABLE语句创建表教程中创建的tasks表。 另外，删除一个不存在的表来练习SHOW WARNING语句。删除tasks表的语句和删除不存在的表nonexistent_table，如下：

DROP TABLE IF EXISTS tasks, nonexistent_table;

如果查看数据库，将看到tasks表已被删除。可以通过使用SHOW WARNING语句来检查MySQL由于不存在的表生成的”提示信息”，如下所示：

SHOW WARNINGS;

执行上面语句，得到以下结果 -

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1051 | Unknown table 'testdb.nonexistent_table' |
+-------+------+------------------------------------------+
1 row in set

MySQL DROP TABLE WITH LIKE

假设有许多表的名称在数据库中是以字符test开始，我们希望通过使用单个DROP TABLE语句删除所有这些表以节省时间。 不幸的是，MySQL不提供DROP TABLE LIKE语句，可以根据以下模式匹配来删除表：

DROP TABLE LIKE '%pattern%'

但是，有一些解决方法。 我们将在此讨论其中一个，以供您参考。为了演示，我们需要创建一些以字符串test*开头的表。

CREATE TABLE IF NOT EXISTS test1(
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS test2 LIKE test1;
CREATE TABLE IF NOT EXISTS test3 LIKE test1;
CREATE TABLE IF NOT EXISTS test4 LIKE test1;

使用相同的表结构创建了名为：test1，test2，test3和test4的四个表。假设您想要一次删除所有字符串test*开头的表，可以按照以下步骤：

首先，声明接受数据库模式的两个变量和要与表进行匹配的模式：

-- set table schema and pattern matching for tables
SET @schema = 'testdb';
SET @pattern = 'test%';

接下来，需要构建动态DROP TABLE语句：

-- build dynamic sql (DROP TABLE tbl1, tbl2...;)
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
INTO @droplike
FROM information_schema.tables
WHERE @schema = database()
AND table_name LIKE @pattern;

基本上，查询指示MySQL转到information_schema表，其中包含所有数据库中所有表的数据，并将与模式@pattern(test％)匹配的数据库@schema(testdb)中的所有表连接到前缀DROP TABLE。GROUP_CONCAT函数创建一个逗号分隔的表列表。

然后，可以显示动态SQL以验证其是否正常工作：

-- display the dynamic sql statement
SELECT @droplike;

执行上面语句，得到以下结果 -

mysql> SELECT @droplike;
+-----------------------------------------------------------------+
| @droplike                                                       |
+-----------------------------------------------------------------+
| DROP TABLE testdb.test1,testdb.test2,testdb.test3,testdb.test4; |
+-----------------------------------------------------------------+
1 row in set

可以看到它按预期工作。

之后，可以使用MySQL中的prepare语句执行语句如下：

-- execute dynamic sql
PREPARE stmt FROM @droplike;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

有关MySQL准备语句的更多信息，请查看MySQL准备语句教程。

把它们放在一起，如下所示 -

-- set table schema and pattern matching for tables
SET @schema = 'testdb';
SET @pattern = 'test%';

-- build dynamic sql (DROP TABLE tbl1, tbl2...;)
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';')
INTO @droplike
FROM information_schema.tables
WHERE @schema = database()
AND table_name LIKE @pattern;

-- display the dynamic sql statement
SELECT @droplike;

-- execute dynamic sql
PREPARE stmt FROM @dropcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

因此，如果要删除在数据库中具有特定模式的多个表，则只需使用上面的脚本来节省时间。 所有您需要做的是在@pattern和@schema变量中替换模式和数据库模式。 如果您经常需要处理此任务，则可以始终基于脚本开发存储过程，并重新使用此存储过程。

在本教程中，我们向您展示了如何使用DROP TABLE语句来删除特定数据库中的现有表。还讨论了一种解决方法，允许使用DROP TABLE语句根据模式匹配删除表。