How to modify index in mysql-Mysql Tutorial-php.cn

Mysql method to modify the index: 1. Use the "DROP INDEX index name ON table name" statement to delete the original index; 2. Use "ALTER TABLE table name ADD INDEX index name [index type] (column name,... )" statement can create an index with the same name as needed.

How to modify index in mysql

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, the index cannot be modified directly. You can modify the index by deleting the original index and then creating an index with the same name as needed.

1. Delete the original index

When the index is no longer needed, you can use the DROP INDEX statement to delete the index.

Grammar format:

DROP INDEX <索引名> ON <表名>

Copy after login

The syntax description is as follows:

: The name of the index to be deleted.

: Specify the table name where the index is located.

#2. Create an index with the same name

The ALTER TABLE statement can create an index on an existing table. You can add indexes to an existing table while modifying the table using the ALTER TABLE statement. The specific method is to add one or more of the following syntax components to the ALTER TABLE statement.

Syntax format:

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

Copy after login

Add this syntax component in the ALTER TABLE statement to add an index to the table while modifying the table.

Syntax format:

ADD PRIMARY KEY [<索引类型>] (<列名>,…)

Copy after login

Add this syntax component in the ALTER TABLE statement to add a primary key to the table while modifying the table.

Syntax format:

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

Copy after login

Add this syntax component in the ALTER TABLE statement to add a unique index to the table while modifying the table.

Syntax format:

ADD FOREIGN KEY [<索引名>] (<列名>,…)

Copy after login

Add this syntax component in the ALTER TABLE statement to add a foreign key to the table while modifying the table.

Mysql example of modifying the index:

Modify the login_name_index index to a single unique index;

DROP INDEX login_name_index ON `user`; 
ALTER TABLE `user` ADD UNIQUE login_name_index ( `login_name` );

Copy after login

[Related recommendations: mysql video tutorial 】

The above is the detailed content of How to modify index in mysql. For more information, please follow other related articles on the PHP Chinese website!

Related labels：

mysql

source：php.cn

Previous article：How to query the table structure in mysql Next article：How to query field value in mysql

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn