drop table if exists 多张表_mysql实验--DROP 大表时的注意事项

最新推荐文章于 2025-05-27 06:50:14 发布
最新推荐文章于 2025-05-27 06:50:14 发布
阅读量8.7k 收藏
点赞数
文章标签： drop table if exists 多张表 drop table if exists报错
本文介绍了在MySQL中删除大表时的注意事项，特别是使用DROP TABLE IF EXISTS命令可能导致的问题及解决方案。通过创建硬链接来加速大表的删除过程，以减少对其他操作的影响。此外，文章提供了删除大文件的策略，以降低磁盘IO开销。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

概述

我们很多时候都会去drop一些大表，特别是生产环境做操作时，这里主要提一些注意事项，仅供参考。

01

相关语法

1、删表

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

可一次删除一张或多张表。需具有所删除表上的DROP权限。表定义文件和数据文件均被移除。表被删除后表上的用户权限不会被自动删除。参数里表中指定的表名不存在则报错，但对于存在的表仍会删除。可通过指定IF EXISTS阻止表不存在时引发的错误(此时对于不存在的表仅产生一个NOTE)。

对于分区表，除了移除表定义，分区、数据外还移除与之关联的分区定义文件(.par)。

在MySQL5.6中参数[RESTRICT | CASCADE]不做任何事情。[TEMPORARY] 关键字表明只删临时表，语句不会结束正在进行的事务(MySQL中DDL语句会隐式提交)，不会进行权限检查。

2、删库

DROP DATABASE SyntaxDROP {DATABASE | SCHEMA} [IF EXISTS] db_name...

删除指定库中的表之后删除库。需具有库上的DROP权限。库被删除后库上存在的用户权限不会被自动删除。IF EXISTS用于阻止库名不存在时引起的错误。库被删除后默认库会被重置。若在使用了符号链接的库上执行DROP DATABASSE 连接和原始数据库都会被删除。命令返回被移除的表数量。

该命令会从指定的数据库目录中移除常规操作时MySQL自己产生的文件和目录，如：.BAK .DAT .HSH .MRG. MYD .MYI .TRG .TRN .db.frm .ibd .ndb .par，若存在db.opt也同样会删除。若数据库目录中存在其他非MySQL本身产生的文件或目录，则整个数据库目录不会被移除。此时，需手动清理剩余的文件并重新运行DROP DATABASE语句。

删除数据库并不会移除库中创建的临时表。临时表在SESSION结束时自动被清理或者显示的通过DROP TEMPORARY TABLE 删除。

02

删除大表的注意事项

对于表的删除，因为InnoDB引擎会在table cache层面维护一个全局独占锁一直到DROP TABLE完成为止，这样，对于表的其他操作会被HANG住。对于较大的表来说，DROP TABLE操作可能需要很长的时间，因此需要一种有效的办法来提升大表的删除速度，以尽可能降低HANG住的时间。这里可以通过设置硬链接来达到此目的。

这里用一个简单实验来测试一下：

1、创建测试表

表名：no_part_tab，1.8亿数据量，表大小5.4G。

delimiter //CREATE PROCEDURE load_no_part_tab()begindeclare v int default 0; while v < 1000000 do insert into no_part_tab values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); set v = v + 1; end while; end //mysql> delimiter ;mysql> call load_no_part_tab();mysql> insert into no_part_tab select * from no_part_tab;.....mysql> create table t2 as select * from no_part_tab nologging;
1155b98635e435fec15e5d0f5c9e716c.png

使用InnoDB引擎且指定innodb_file_per_table=ON时在数据目录中与该表对应的有如下两个文件，分别为表定义文件和数据文件。

ee94ddac1e2208dbd13ee613adb6be6d.png
27b2a79708577e12a1934a5d6093127d.png

2、drop table

直接使用DROP TABLE来完成删表动作，正常这条语句要执行很长时间。因为数据量只有5G，所以不是很明显。(这台服务器空间不够了，所以数据量没加上去..)

7ed427a5ba492315fd478d20c1775a8c.png

3、建立硬链接

此时便可以通过在该表对应的数据文件上设置硬链接来进行删除。

#ln /data/datafile/test/no_part_tab.MYD /data/datafile/test/no_part_tab.MYD.hdlk#ls -lh /data/datafile/test/
5f7de169b9606a99421774e7b681a6a0.png

发现多了一个sys_user.ibd.hdlk文件，且no_part_tab.MYD.hdlk和no_part_tab.MYD的innode均为2。也即当有多个文件名(如硬链接)指向同一innode时，这个innode的引用数大于1，此时，删除其中任何一个文件名都只会删除指向innode的指针而并不会直接删除物理文件块，因此会非常快，直至innode的引用计数等于1时才会真正删除对应的物理文件块，真正删除物理文件块时才会比较耗时。

4、drop table

在建立了硬链接后再执行DROP TABLE操作：

DROP TABLE no_part_tab;
a13c0288e22116d84d34540abf353780.png

可以看到基本没耗时，查看对应的表定义和数据文件：

49084e53e1d3b00c2561eef6b9cac55c.png

只剩下no_part_tab.MYD.hdlk，且innode的引用计数变为了1。也即刚才的DROP TABLE操作实施删除了物理文件的一个指针no_part_tab，因而非常快。

5、删除真正的物理文件

因为此时innode的引用计数已经变为了1，直接删除no_part_tab.MYD.hdlk便会真正的删除物理文件。但因为物理文件较大，删除大文件仍会引起较高的磁盘IO开销。因此可以使用少量逐次删除的方式来删除大的数据文件。truncate工具可以用于增加或缩减指定文件的尺寸，可以用于此目的：

#for i in `seq 6 -1 1 ` ;do sleep 2;sudo truncate -s ${i}G /data/datafile/test/no_part_tab.MYD.hdlk;done#rm -rf /data/datafile/test/no_part_tab.MYD.hdlk;

从6G开始，每次缩减1G，停2秒，继续，直到文件只剩1G，最后使用rm命令删除剩余的部分。

a94baff441037f82789973a82f00382c.png

如果是整个数据库的删除可以先删除其中较大的表，最后在执行DROP DATABASE删除整个库，对大表的删除可参见上面的方法。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下！

3d3233718ed6597f220eafb778338f95.png
Oracle实现drop table if exists
tttzzzqqq2018的博客
08-12 5412
【代码】Oracle实现drop table if exists。
表的增删查改（进阶）—MySQL
m0_61963422的博客
11-28 335
文章目录1.数据库约束 1.数据库约束
drop table if exists 多张表_显示屏幕是越大越好吗？看看这张表对眼睛有好处
weixin_39651816的博客
11-08 223
显示屏幕显示屏幕是数字/模拟信息与人交互的最重要设备，通常用英寸来衡量大小(屏幕对角线)。我们每天看视频，看文档、看表格都是通过显示屏幕来观看。大家觉得：显示器小了，文字就小，图像也小，眼睛看得累。那么是不是显示器越大越好呢？人眼观看角度人眼的视角极限大约为垂直方向150度，水平方向230度，如果在这个视角范围内都是屏幕，那么就会给我们一种身临其境的感觉。但这个角度太大，通常只有环幕电影才能达到这...
MySQL(40)如何使用DROP TABLE删除表？
qq_43012298的博客
05-27 755
DROP TABLE语句用于永久删除数据库中的表及其数据，操作不可逆。基本语法为DROP TABLE table_name，支持多表删除（逗号分隔）。使用IF EXISTS可避免删除不存在的表报错。示例演示了在company数据库中创建employees和departments表后，分别进行单表删除（DROP TABLE employees）和多表删除（DROP TABLE employees,departments）。对于存在外键约束的表，需先删除约束再执行DROP。文中提供了完整的创建-删除流程代码，强
DROP TABLE IF EXISTS” in Oracle and SQL Server
gaoshanwudi的专栏
03-18 2007
In MySQL it is pretty easy to drop a table if it exists already. In Oracle and Microsoft’s SQL Server it is a little more complicated. Today I want to present you the solutions for these two DBMS’.
mysql中的drop table if exists
热门推荐
少猿的博客
08-12 19万+
一般drop table if exists是数据库里面的，后面接表名如：drop table if exists xxx_book意思就是：如果数据库中存在xxx_book表，就把它从数据库中drop掉。 　　备份sql中一般都有这样的语句，如果是数据库中有这个表，先drop掉，然后create表，然后再进行数据插入。 ...
oracle的drop table if exists
SouthKing
12-17 3978
[code="sql"] 利用存储实现 create or replace procedure proc_dropifexists( p_table in varchar2 ) is v_count number(10); begin select count(*) into v_count from user_tables ...
MySQL表的增删改查（进阶）
TuttuYYDS的博客
05-23 397
MySQL表的增删改查(进阶) 数据库约束 表的设计 新增 查询 联合查询 多表查询练习题 外连接 自连接 子查询 合并查询
MySQL数据库 - MySQL 实例操作看了就上手
mohuanfenghuang的博客
12-05 1343
今天给伙伴们分享一下MySQL数据库 - MySQL 实例操作，希望看了有所收获。
qt mysql删除表_42.QT-QSqlQuery类操作SQLite数据库(创建、查询、删除、修改)详解
weixin_36123682的博客
02-03 1094
Qt 提供了 QtSql 模块来提供平台独立的基于 SQL 的数据库操作。这里我们所说的“平台独立”，既包括操作系统平台，也包括各个数据库平台,Qt支持以下几种数据库:QT自带SQLITE数据库,不需要再安装QTDS在Qt4.7起已经被移除1.QtSql要使用QtSql 模块的话,需要在.pro文件中添加这么一句：QT += sql2.QSqlDatabaseQSqlDatabase类提供了一个接...
高性能mysql读书笔记四-MySQL高级特性
liushangzaibeijing的博客
03-24 3万+
本篇博文是高性能Mysql一书中 mysql高级特性分析。主要介绍了分区表，视图、存储代码三大模块，详细内容包含：分区表概念，优劣,类型，原理、使用、示例等, 视图的概念，示例，存储代码主要包含 触发器、事件、存储过程、存储函数。本篇在介绍高级特性的时候，辅助以详尽的语法、示例，希望读者能有所收获。
DROP TABLE IF EXISTS
linshow26的博客
01-08 5686
DROP TABLE IF EXISTS `ci_sessions`; CREATE TABLE IF NOT EXISTS `ci_sessions` ( `session_id` VARCHAR(40) NOT NULL DEFAULT '0', `peopleid` INT(11) NOT NULL, `ip_address` VARCHAR(16) NOT...
Oracle 的drop table if exists功能
东风
11-27 2万+
Oracle创建表时，常遇到先删除后创建的情况，而它又没有drop table... if exists语法。为此可以使用user_objects数据字典和动态sql语句实现类似的功能，如下所示：create or replace procedure proc_dropifexists(    p_table in varchar2 ) is    v_count number
4、删除数据表（DORP TABLE语句）
you can you up up up的博客
10-10 7504
在 MySQL 数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。 在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。 下面我们来了解一下 MySQL 数据库中数据表的删除方法。 基本语法 使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下： DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...] 对语法格式的说明如下： 表名1, 表名2, 表名3 …表示要被删除的数据表的名称。DRO
数据库DROP TABLE IF EXISTS语句
qq_42946963的博客
10-28 1万+
DROP TABLE IF EXISTS database; 创建数据库sql语句
SqlServer数据库DROP TABLE IF EXISTS语句
cong_da_da的博客
08-17 5万+
近日碰到SqlServer数据库版本不一致，执行建表脚本时DROP TABLE IF  EXISTS语法的小问题，自己做个记录便于后续查看。 在SqlServer2016版本及更高版本，建表前判断表是否存在，可以使用： DROP TABLE IF EXISTS myschema.mytable; CREATE TABLE myschema.mytable( STUNAM varchar(2...
if exists drop table oracle,sql - drop table如果Oracle中存在该表（IF EXIST）[重复] - 堆栈内存溢出...
weixin_36213934的博客
04-02 1078
我有一个类似的问题-我需要一种无需修改即可重复DDL脚本的方法。 映像以下脚本：create table tab1(...);create table tab2(...);create table tab3{...}; /* create table tab4(...);因此，现在我们处于以下情况：已成功创建表“ tab1”和“ tab2”，缺少“ tab3”和“ tab4”。 因此，在修复了...
Oracle Drop Table If Exists
程序员码工的专栏
10-18 1万+
Sometimes we want to clean up Oracle database, such as to drop a table if there exists, and do nothing if table does not exist; such as: - drop table table if exists Unfortunately, there is no s
DROP table
最新发布
07-15
<think>嗯，用户这次的问题是关于数据库操作的，具体是询问DROP TABLE删除数据库表的相关知识。用户可能是个数据库管理员或开发人员，正在处理数据库维护工作。从问题本身来看，用户应该具备基础的SQL知识，但可能对DROP TABLE的具体用法和注意事项不太清楚。 我注意到用户提供的引用内容很有意思。引用[1]提到MySQL可以批量删除多个表，这正好解答了用户问题中的隐含需求——用户可能不仅想知道如何删除单张表，还想了解多表删除的操作。引用[2]则指出了删除大表时的性能问题，这个补充信息对用户应该很有价值，特别是如果用户需要维护大型数据库的话。 在组织回答时，我会先解释基本语法，因为这是用户明确问的。但考虑到实际应用场景，必须补充多表删除和注意事项这些关键点。特别是硬链接那个技巧，对处理大表删除很有帮助，虽然技术性较强，但值得介绍。 用户没有提供具体的数据库环境，不过引用中提到的都是MySQL，所以我会以MySQL为主要示例，同时说明其他数据库的通用性。考虑到用户可能是实际操作中遇到问题，我会强调备份的重要性——这个在引用里没提，但作为专业建议必须包含。 最后生成的问题要覆盖用户可能延伸的疑问：从基本操作到性能优化，再到替代方案。特别是TRUNCATE和DELETE的区别，这个在实际工作中经常被混淆，值得专门列出。</think>在 MySQL 中，`DROP TABLE` 语句用于**永久删除**数据库中的一张或多张表及其所有数据、结构（定义）、索引、触发器和权限。**操作不可逆，务必谨慎执行！** ### 基本语法 ```sql DROP TABLE [IF EXISTS] table_name; ``` * `DROP TABLE`: 关键字，表示删除表。 * `IF EXISTS`: **可选**。如果指定，则当表不存在时不会报错（返回一个警告而非错误）。**强烈建议使用**，避免因表不存在导致脚本中断。 * `table_name`: 要删除的表的名称。 ### 删除多张表（批量删除） 是的，`DROP TABLE` 可以一次删除多张表。只需将表名依次列出，用逗号分隔：[^1] ```sql DROP TABLE [IF EXISTS] table_name1, table_name2, table_name3, ...; ``` **示例：** ```sql -- 安全删除单张表 (如果表 'old_users' 存在) DROP TABLE IF EXISTS old_users; -- 安全删除多张表 (如果 'temp_data', 'backup_logs', 'staging_orders' 存在) DROP TABLE IF EXISTS temp_data, backup_logs, staging_orders; ``` ### ⚠️ 重要注意事项 1. **数据永久丢失：** `DROP TABLE` 会**立即且永久**删除表结构和其中的所有数据。**操作前务必确认！** 2. **权限：** 执行 `DROP TABLE` 需要用户对该表具有 `DROP` 权限。 3. **依赖对象：** 删除表会自动删除与之关联的索引、触发器、约束。如果该表被视图、存储过程或其他表的外键约束引用，删除操作**可能会失败**（除非指定了 `CASCADE`，但 MySQL 中 `DROP TABLE` 本身不支持 `CASCADE`，需先处理依赖）。[^2] 4. **事务与锁：** * `DROP TABLE` 操作**不能被回滚**（即使在事务中执行了 `BEGIN`，对于非事务性存储引擎如 MyISAM，或即使对于 InnoDB，`DROP TABLE` 也是隐式提交的）。 * 删除大表时，操作可能耗时较长，并会在 table cache 层面持有全局独占锁，阻塞其他对该表的操作（如查询），可能导致应用卡顿。[^2] 5. **大表删除优化：** 对于非常大的表（特别是使用 InnoDB 引擎），直接 `DROP TABLE` 可能导致服务短暂卡顿。一个优化技巧是先在文件系统层面为表数据文件（`.ibd`）创建一个**硬链接**，然后在数据库中执行 `DROP TABLE`。数据库操作完成后，再在操作系统层面删除这个硬链接文件，可以显著减少数据库持有锁的时间。[^2] ```bash # Linux 示例 (找到表对应的 .ibd 文件路径，假设为 /var/lib/mysql/dbname/large_table.ibd) ln /var/lib/mysql/dbname/large_table.ibd /tmp/hardlink_to_large_table.ibd ``` ```sql -- 在 MySQL 中执行 DROP TABLE dbname.large_table; -- 此时锁释放会很快 ``` ```bash # 然后在操作系统层面删除硬链接 (实际数据删除由 OS 异步完成) rm /tmp/hardlink_to_large_table.ibd ``` 6. **备份！备份！备份！：** 在执行任何 `DROP TABLE` 操作之前，**强烈建议对数据库或相关表进行完整备份**。可以使用 `mysqldump` 工具或物理备份方法。 ### 替代方案（谨慎选择） * `TRUNCATE TABLE table_name;` * **删除表内所有数据**，但保留表结构（列定义、索引等）。 * 比 `DELETE FROM table_name;`（不带 WHERE 子句）更快，因为它不逐行记录日志（对于 InnoDB，它实际上是删除并重建表）。 * 同样**不可回滚**（隐式提交），且会重置 AUTO_INCREMENT 计数器。 * 需要 `DROP` 权限。 * `DELETE FROM table_name [WHERE condition];` * 根据可选条件**逐行删除数据**，保留表结构。 * 会记录日志（可回滚，如果在事务内），因此比 `TRUNCATE` 慢，尤其对于大表。 * 需要 `DELETE` 权限。 * 不带 `WHERE` 子句会删除所有行，但**不释放表占用的存储空间**（InnoDB 中空间可被后续重用，但文件大小不会立即缩小）。 ### 总结 * 使用 `DROP TABLE [IF EXISTS] table_name;` 删除单张表。 * 使用 `DROP TABLE [IF EXISTS] table1, table2, ...;` 批量删除多张表。[^1] * **务必使用 `IF EXISTS` 防止因表不存在报错。** * **操作前必须备份！** 数据永久丢失。 * 删除大表时考虑硬链接技巧优化以减少锁持有时间。[^2] * 理解 `DROP TABLE`、`TRUNCATE TABLE` 和 `DELETE` 的区别，根据需求选择。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值