How to modify index in mysql

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.

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 <表名>

The syntax description is as follows:

• ec07977447c078c9bd9029f2820ceeb7: The name of the index to be deleted.

• 722e3d59fd24604761db25f00f9b264f: 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 [<索引名>] [<索引类型>] (<列名>,…)

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 [<索引类型>] (<列名>,…)

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] [<索引名>] [<索引类型>] (<列名>,…)

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 [<索引名>] (<列名>,…)

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` );

[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!