Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to add, delete, modify and query the mysql index, which has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

In mysql, you can use the "CREATE INDEX" statement to add the index; use the "SHOW INDEX" statement to view the index; use the "DROP INDEX" statement to delete the index; and modify the index by deleting the original index, and then create an index with the same name as needed.

Creation of mysql Index

Creating an index refers to creating an index on one or more columns of a table, which can improve the speed of access to the table. Creating indexes is important for the efficient operation of the MySQL database.

Basic grammar

MySQL provides three ways to create an index:

1) use the CREATE INDEX statement

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 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.

2) use CREATE TABLE statement

Indexes can also be created at the same time as the table (CREATE TABLE) is created. Add the following statement to the CREATE TABLE statement. Syntax format:

CONSTRAINT PRIMARY KEY [index type] (,...)

Add this statement to the CREATE TABLE statement to indicate that the primary key of the table is created while the new table is created.

Syntax format:

KEY | INDEX [] [] (, …)

Adding this statement to the CREATE TABLE statement means that the index of the new table is created at the same time.

Syntax format:

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

Add this statement to the CREATE TABLE statement to create a unique index of the table while the new table is created.

Syntax format:

FOREIGN KEY

Add this statement to the CREATE TABLE statement to indicate that the foreign key for the new table is created at the same time.

When you use a CREATE TABLE statement to define column options, you can create a primary key by adding PRIMARY KEY directly after a column definition. When the primary key is a multi-column index composed of multiple columns, this method cannot be used, but can only be used to add a PRIMARY KRY (,...) at the end of the statement. Clause in the way it is implemented.

3) use ALTER TABLE statement

The CREATE INDEX statement can create an index on an existing table, and 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.

View of mysql index

After the index is created, you can use the SQL statement to view the existing indexes. In MySQL, you can use the SHOW INDEX statement to view the indexes created in the table.

The syntax format for viewing the index is as follows:

SHOW INDEX FROM [FROM]

The syntax is as follows:

Specifies the name of the data table to view the index

Specifies the database in which the data table to view the index is located, which can be omitted For example, the SHOW INDEX FROM student FROM test; statement means to view the index of the student data table in the test database.

Example

The SQL statement and the result of the run are shown below.

Mysql > SHOW INDEX FROM tb_stu_info2\ gateway * 1. Row * * Table: tb_stu_info2 Non_unique: 0 Key_name: height Seq_in_index: 1 Column_name: height Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment:Index_comment:1 row in set (0.03 sec)

Modification and deletion of mysql index

To delete an index is to delete an index that already exists in the table. Unused indexes are recommended for deletion because they slow down the update speed of the table and affect the performance of the database. For such an index, it should be deleted.

In MySQL, you can modify the index by deleting the original index and then creating an index with the same name as needed.

Basic grammar

When an index is no longer needed, you can use a DROP INDEX statement or an ALTER TABLE statement to delete the index.

1) use the DROP INDEX statement

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) use ALTER TABLE statement

According to the syntax of the ALTER TABLE statement, it can also be used to delete an index. The specific way to use it is to specify part of the syntax of the ALTER TABLE statement as one of the following clauses.

DROP PRIMARY KEY: means to delete the primary key in the table. A table has only one primary key, and the primary key is also an index.

DROP INDEX index_name: means to delete an index named index_name.

DROP FOREIGN KEY fk_symbol: means to delete a foreign key.

Note: if the deleted column is part of the index, the column will also be deleted from the index when the column is deleted; if all the columns that make up the index are deleted, the entire index will be deleted.

Delete index

[example 1] Delete the index in the table tb_stu_info, enter the SQL statement and the execution result is shown below.

Mysql > DROP INDEX height-> ON tb_stu_info Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_stu_info\ Graph * 1. Row * * Table: tb_stu_infoCreate Table: CREATE TABLE `tb_stu_ info` (`id`int (11) NOT NULL, `name` char (45) DEFAULT NULL `dept_ id` int (11) DEFAULT NULL, `age` int (11) DEFAULT NULL, `height` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=gb23121 row in set (0.00 sec)

[example 2] Delete the index named id in the table tb_stu_info2, and the SQL statement entered and the execution result are shown below.

Mysql > ALTER TABLE tb_stu_info2-> DROP INDEX height Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_stu_info2\ Graph * 1. Row * * Table: tb_stu_info2Create Table: CREATE TABLE `tb_stu_ info2` (`id` int (11) NOT NULL, `name` char (45) DEFAULT NULL `dept_ id` int (11) DEFAULT NULL, `age` int (11) DEFAULT NULL, `height` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=gb23121 row in set (0.00 sec) Thank you for reading this article carefully I hope the editor can share the operation content of how to add, delete, modify and check the mysql index. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.