MySQL DROP COLUMN

简介：在本教程中，我们将向您展示如何使用MySQL DROP COLUMN语句从表中删除列。

MySQL DROP COLUMN语句简介

在某些情况下，您希望从表中删除一个或多个列。在这种情况下，您使用ALTER TABLE DROP COLUMN语句，如下所示：

ALTER TABLE table

DROP COLUMN column;

让我们更详细地研究一下这个语句：

首先，在ALTER TABLE子句后指定包含要删除的列的表。

其次，您将列的名称放在DROP COLUMN子句之后。

请注意，关键字COLUMN是可选的，因此您可以使用较短的语句，如下所示：

ALTER TABLE table

DROP column;

若要同时从表中删除多个列，请使用以下语法：

ALTER TABLE table

DROP COLUMN column_1,

DROP COLUMN column_2,

…;

在从表中删除列之前，您应记住一些重要的要点：

从表中删除列会使依赖于列的所有数据库对象(如存储过程，视图，触发器等)无效。例如，您可能有一个引用列的存储过程。删除列时，存储过程将变为无效。要修复它，您必须手动手动更改存储过程的代码。

必须同时更改依赖于已删除列的其他应用程序的代码，这需要时间和精力。

从大表中删除列可能会影响数据库的性能。

MySQL DROP COLUMN示例

首先，我们创建一个posts 表演示用。

CREATE TABLE posts (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

excerpt VARCHAR(400),

content TEXT,

created_at DATETIME,

updated_at DATETIME

);

其次，要删除excerpt列，请使用以下ALTER TABLE语句：

ALTER TABLE posts

DROP COLUMN excerpt;

第三，要同时删除两个列created_at和updated_at列，请使用以下语句：

ALTER TABLE posts

DROP COLUMN created_at,

DROP COLUMN updated_at;

MySQL删除一个带有外键列的示例

如果删除作为外键的列，MySQL将发出错误。让我们展示一下这个想法。

首先，创建一个名为categories的表：

CREATE TABLE categories (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(255)

);

其次，将category_id列添加到posts表中。

ALTER TABLE posts ADD COLUMN category_id INT NOT NULL;

第三，将category_id列作为引用categories表的id列的外键。

ALTER TABLE posts

ADD CONSTRAINT fk_cat

FOREIGN KEY (category_id)

REFERENCES categories(id) ON DELETE CASCADE;

第四，从posts表中删除category_id列。

ALTER TABLE posts

DROP COLUMN category_id;

MySQL发出错误消息：

ERROR 1553 (HY000): Cannot drop index 'fk_cat': needed in a foreign key constraint

要避免此错误，必须在删除列之前删除外键约束。

在本教程中，我们向您展示了如何使用MySQL DROP COLUMN语句从表中删除一个或多个列。

要删除现有表，请使用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会生成一个NOTE，可以使用该SHOW WARNINGS语句来检索该NOTE 。

DROP TABLE例子

创建一个用于测试表insurances：

CREATE TABLE insurances (

id INT AUTO_INCREMENT,

title VARCHAR(100) NOT NULL,

effectiveDate DATE NOT NULL,

duration INT NOT NULL,

amount DEC(10 , 2 ) NOT NULL,

PRIMARY KEY(id)

);

使用DROP TABLE删除insurances表：

DROP TABLE insurances;

MySQL DROP TABLE删除多个表

首先，创建两个名为CarAccessories和的表CarGadgets：

CREATE TABLE CarAccessories (

id INT AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

price DEC(10 , 2 ) NOT NULL,

PRIMARY KEY(id)

);

CREATE TABLE CarGadgets (

id INT AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

price DEC(10 , 2 ) NOT NULL,

PRIMARY KEY(id)

);

DROP TABLE语句删除两个表：

DROP TABLE CarAccessories, CarGadgets;

MySQL DROP TABLE删除不存在的表

尝试删除一个不存在的表：

DROP TABLE aliens;

MySQL发出以下错误：

ErrorCode:1051.Unknowntable'classicmodels.aliens'

如果IF EXISTS在DROP TABLE语句中使用该选项：

DROP TABLE IF EXISTS aliens;

MySQL发出警告：

0 row(s) affected, 1 warning(s): 1051 Unknown table 'classicmodels.aliens'

要显示警告，可以使用以下SHOW WARNINGS语句：

SHOW WARNINGS;

删表

DROP TABLE Syntax

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT| CASCADE]

可一次删除一张或多张表。需具有所删除表上的DROP权限。表定义文件和数据文件均被移除。表被删除后表上的用户权限不会被自动删除。参数里表中指定的表名不存在则报错，但对于存在的表仍会删除。可通过指定IF EXISTS阻止表不存在时引发的错误(此时对于不存在的表仅产生一个NOTE)。对于分区表，除了移除表定义，分区、数据外还移除与之关联的分区定义文件(.par)。在MySQL5.6中参数[RESTRICT | CASCADE]不做任何事情。[TEMPORARY] 关键字表明只删临时表，语句不会结束正在进行的事务(MySQL中DDL语句会隐式提交)，不会进行权限检查。

删库

DROP DATABASE Syntax

DROP {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 删除。

删除大表的注意事项

对于表的删除，因为InnoDB引擎会在table cache层面维护一个全局独占锁一直到DROP

TABLE完成为止，这样，对于表的其他操作会被HANG住。对于较大的表来说，DROP

TABLE操作可能需要很长的时间，因此需要一种有效的办法来提升大表的删除速度，以尽可能降低HANG住的时间。可以通过设置硬链接来达到此目的。

比如有一个样例表：

example_table

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

sudo ls -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26example_table.frm-rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd

该表有100G这么大，直接使用DROP TABLE来完成删表动作，那么这条语句要执行很长时间。此时便可以通过在该表对应的数据文件上设置硬链接来进行删除。

sudo ln /data/mysql/testdb/example_table.ibd /data/mysql/testdb/example_table.ibd.hdlksudo ls -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26example_table.frm-rw-rw---- 2 mysql mysql 100G Oct 28 13:26example_table.ibd-rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk

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

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

DROP TABLE example_table；

发现会很快的完成，查看对应的表定义和数据文件：

sudo ls -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk

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

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

for i in `seq 100 -1 1 ` ;do sleep 2;sudo truncate -s ${i}G /data/mysql/testdb/example_table.ibd.hdlk;done

sudo rm -rf /data/mysql/testdb/example_table.ibd.hdlk;

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

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

转自

MySQL DROP TABLE操作以及 DROP 大表时的注意事项 - CSDN博客 https://blog.csdn.net/zyz511919766/article/details/40539333