Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to modify the index in mysql". In the daily operation, I believe many people have doubts about how to modify the index in mysql. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to modify the index in mysql". Next, please follow the editor to study!

The method of mysql to modify the index: 1. Delete the original index using the statement "DROP INDEX index name ON table name"; 2. Use "ALTER TABLE table name ADD INDEX index name [index type] (column name, …)" Statement to 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 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.

Syntax format:

DROP INDEX ON

The syntax is as follows:

The name of the index to delete

Specifies the name of the table in which 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 an index to an existing table while using the ALTER TABLE statement to modify the table. To do this, add one or more of the following grammatical elements to the ALTER TABLE statement.

Syntax format:

ADD INDEX [] [] (,...)

Adding this syntax component to the ALTER TABLE statement means that the table is indexed while it is modified.

Syntax format:

ADD PRIMARY KEY [] (,...)

Adding this syntax component to the ALTER TABLE statement means that the primary key is added to the table while it is modified.

Syntax format:

ADD UNIQUE [INDEX | KEY] [] [] (,...)

Adding this syntax component to the ALTER TABLE statement means that a unique index is added to the table while it is modified.

Syntax format:

ADD FOREIGN KEY [] (,...)

Adding this syntax component to the ALTER TABLE statement means that foreign keys are added to the table while it is modified.

An example of an index modified by mysql:

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 (`username`). At this point, the study on "how to modify the mysql index" is over. I hope to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.