Mysql 简明教程

MySQL - Drop Index

MySQL 数据库中的 DROP 语句用于删除现有的数据库对象，例如表、索引、视图或过程。每当我们对任何数据库对象（如索引）使用 DROP 语句时，它都会与关联数据一起永久删除它们。

The DROP statement in MySQL database is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, like indexes, it will remove them permanently along with their associated data.

因此，我们可以使用两个不同的 SQL DROP 查询从数据库表中删除任何索引。

Therefore, we can drop any index from a database table using two different SQL DROP queries.

The MySQL DROP INDEX Statement

MySQL 中的 DROP INDEX 语句用于从表中删除索引。

The DROP INDEX statement in MySQL is used to delete an index from a table.

Syntax

以下是使用 DROP INDEX 语句删除索引的语法 -

Following is the syntax to drop an index using DROP INDEX statement −

DROP INDEX index_name ON table_name;

Example

在此示例中，我们首先创建一个新表 CUSTOMERS ，并使用以下 CREATE TABLE 查询向其一个列 (AGE) 添加索引 -

In this example, we first create a new table CUSTOMERS and adding an index to one of its columns (AGE) using the following CREATE TABLE query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID),
   INDEX(AGE)
);

现在，在 CUSTOMERS 表上创建另一个索引。我们在此处使用 CREATE INDEX 语句 -

Now, create another index on CUSTOMERS table. We are using CREATE INDEX statement here −

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

DROP INDEX Query −

DROP INDEX Query −

然后，使用以下查询删除上面创建的索引。

Then, use the following query to drop the index created above.

DROP INDEX NAME_INDEX ON CUSTOMERS;

Verification

要验证是否已删除索引，请使用以下 DESC 查询显示表定义 -

To verify if the index has been dropped, display the table definition using DESC query below −

DESC CUSTOMERS;

正如我们从下表中看到的，NAME 列上的索引已删除。

As we can see in the following table, the index on NAME column is dropped.

The MySQL ALTER…​ DROP Statement

ALTER DROP 语句也可以用于在 MySQL 表中删除索引。这只是 DROP INDEX 语句的替代方案，因此它仅适用于表上存在的索引。

The ALTER DROP statement can also be used to drop an index in a MySQL table. This is just an alternative to the DROP INDEX statement, so it only works with the index that exists on a table.

Syntax

以下是 SQL 中 DROP INDEX IF EXISTS 的语法：

Following is the syntax of the DROP INDEX IF EXISTS in SQL −

ALTER TABLE table_name DROP INDEX index_name;

Example

我们来看另一个示例，使用 ALTER…​ DROP 命令删除 CUSTOMERS 表中的索引，如下所示 -

Let us see another example to drop the index from the CUSTOMERS table using the ALTER…​ DROP command as shown below −

ALTER TABLE CUSTOMERS DROP INDEX AGE;

Verification

要验证是否已删除 AGE 列上的索引，请使用以下 DESC 查询显示表定义 -

To verify if the index on AGE column has been dropped, display the table definition using DESC query below −

DESC CUSTOMERS;

正如我们从下表中看到的，NAME 列上的索引已删除。

As we can see in the following table, the index on NAME column is dropped.

Dropping PRIMARY KEY or UNIQUE Constraint

MySQL 中的 DROP INDEX 语句通常不会删除 PRIMARY KEY 或 UNIQUE 约束之类的索引。要删除与这些约束关联的索引，我们需要使用 ALTER TABLE DROP 命令。

The DROP INDEX statement in MySQL does not usually drop indexes like PRIMARY KEY or UNIQUE constraints. To drop indexes associated with these constraints, we need to use the ALTER TABLE DROP command.

Syntax

以下是语法 -

Following is the syntax −

ALTER TABLE table_name DROP constraint_name;

Example

在此示例中，我们使用以下查询删除 CUSTOMERS 表的 ID 列上存在的 PRIMARY KEY 约束 -

In this example, we are using the following query to drop the PRIMARY KEY constraint present on the ID column of CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Verification

要验证是否已从表中删除主键约束，请使用 DESC 命令描述“temp”表，如下所示 -

To verify whether the primary key constraint is dropped from the table, describe the 'temp' table using DESC command as follows −

DESC CUSTOMERS;

现在，主键约束终于被删除了！请看下表 -

The PRIMARY KEY constraint is finally dropped! Look at the table below −

Dropping an Index Using a Client Program

我们已经了解了如何使用 SQL 查询从 MySQL 数据库中删除索引。除此之外，我们还可以使用其他客户端程序来在 MySQL 数据库中执行删除索引操作。

We have seen how to drop an index from a MySQL database using SQL queries. In addition to it, we can also use other client programs to perform the drop index operation in the MySQL database.

Syntax

以下是使用各种编程语言从 MySQL 数据库中删除索引的语法 -

Following are the syntaxes to drop an index from a MySQL database using various programming languages −

Example

以下是该操作在各种编程语言中的实现 −

Following are the implementations of this operation in various programming languages −