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

This article Xiaobian for you to introduce in detail "mysql how to modify the index type", the content is detailed, the steps are clear, the details are handled properly, I hope this "mysql how to modify the index type" article can help you solve your doubts, following the editor's ideas slowly in-depth, together to learn new knowledge.

Mysql methods to modify the index type: 1, use the "DROP INDEX index name ON table name;" statement to delete the original specified index; 2, use the "CREATE index type keyword INDEX index name ON table name (column name [length])" statement to create an index with the same name to modify the type.

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

There is no direct instruction to modify the index in MySQL. In general, we need to delete the original index first, and then create an index with the same name as needed, so as to modify the index in disguise.

Mysql modifies the index type

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

Example:

Index information of tb_stu_info2 data table

As can be seen from the output: "Key_name: height"-the name of the index is "height", "Index_type: BTREE"-the type used by the index is "BTREE"

Delete index height in table tb_stu_info2

DROP INDEX height ON tb_stu_info2

2. Create an index with the same name

You can create an index on an existing table using a CREATE INDEX statement specifically designed to create an index, but this statement cannot create a primary key.

Syntax format:

CREATE INDEX ON ([] [ASC | DESC])

The syntax is as follows:

Specifies the index name Multiple indexes can be created in a table, but each index has a unique name in the table.

Specifies the name of the table to be indexed

Specifies the name of the column to create the index You can usually consider the columns that often appear in the JOIN clause and WHERE clause in the query statement as index columns.

Optional Specifies that the index is created using the length characters before the column. Creating an index using part of a column helps to reduce the size of the index file and save the space occupied by the index column. In some cases, only column prefixes can be indexed. The length of an index column has a maximum limit of 255bytes (the maximum limit for MyISAM and InnoDB tables is 1000 bytes). If the length of an index column exceeds this limit, it can only be indexed with the prefix of the column. In addition, columns of type BLOB or TEXT must also be indexed with a prefix.

ASC | DESC: optional. ASC specifies that the indexes are sorted in ascending order, and DESC specifies that the indexes are arranged in descending order. The default is ASC.

Example:

Create a normal index

CREATE INDEX height ON tb_stu_info2 (height (8))

The UNIQUE keyword is usually used when creating a unique index.

CREATE UNIQUE INDEX height ON tb_stu_info2 (height (8)); read here, this article "how to modify the index type of mysql" article has been introduced, want to master the knowledge of this article also need to practice and use in order to understand, if you want to know more related articles, welcome to follow the industry information channel.

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.