How to implement addition, deletion, modification and query of mysql index?

In mysql, you can use the "CREATE INDEX" statement to add an 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. Then create an index with the same name as needed to modify the index.

(Recommended tutorial: mysql video tutorial)

mysql index creation

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

Basic syntax

MySQL provides three methods of creating indexes:

1) Use the CREATE INDEX statement

You can use the CREATE INDEX statement specifically for creating indexes to create an index on an existing table, but this statement cannot create a primary key.

Syntax format:

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

The syntax description is as follows:

• ec07977447c078c9bd9029f2820ceeb7: Specify the index name. A table can create multiple indexes, but each index has a unique name in the table.

• 722e3d59fd24604761db25f00f9b264f: Specify the name of the table to create an index.

• 4856a503a57d954e03d677c32e973360: Specify the column name to create an index. You can usually consider using columns that frequently appear in the JOIN clause and WHERE clause in the query statement as index columns.

• 93d887edaff759fd3eed38159bc15ea1: Optional. Specifies that the length characters preceding the column are used to create the index. Creating an index using part of a column can help reduce the size of the index file and save the space occupied by the index columns. In some cases, only the prefix of a column can be indexed. The length of an index column has a maximum limit of 255 bytes (1000 bytes for MyISAM and InnoDB tables). If the length of an index column exceeds this limit, it can only be indexed using the column's prefix. In addition, columns of type BLOB or TEXT must also use prefix indexes.

• ASC|DESC: Optional. ASC specifies that the index is sorted in ascending order, DESC specifies that the index is sorted in descending order, and the default is ASC.

2) Use the CREATE TABLE statement

The index can also be created while creating the table (CREATE TABLE). Add the following statements to the CREATE TABLE statement. Syntax format:

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

Add this statement in the CREATE TABLE statement to create the primary key of the table when creating a new table.

Syntax format:

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

Add this statement in the CREATE TABLE statement to create an index for the table while creating a new table.

Syntax format:

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

Add this statement in the CREATE TABLE statement to create a unique index for the table while creating a new table.

Syntax format:

FOREIGN KEY <索引名> <列名>

Add this statement in the CREATE TABLE statement to create a foreign key to the table while creating a new table.

When using the 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. It can only be implemented by adding a PRIMARY KRY (e147dec42e83e3b84883e9b9d2f9cc02,...) clause at the end of the statement. .

3) Use the ALTER TABLE statement

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

View mysql index

After the index creation is completed, you can use SQL statements to view the existing index. In MySQL, you can use the SHOW INDEX statement to view the indexes created on a table.

The syntax format for viewing the index is as follows:

SHOW INDEX FROM <表名> [ FROM <数据库名>]

The syntax description is as follows:

• ##722e3d59fd24604761db25f00f9b264f: Specify the data table to be viewed for the index name.

• 51ede4c6d43f3b2169203bd49746727d: Specify the database where the data table that needs to be viewed 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

SQL statements and running results are as follows.

mysql> SHOW INDEX FROM tb_stu_info2\G
*************************** 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)

Mysql index modification and deletion

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

基本语法

当不再需要索引时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句来对索引进行删除。

1) 使用 DROP INDEX 语句

语法格式：

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

语法说明如下：

• b493f009a433abb929a361542484fbbf：要删除的索引名。

• a26d98d33123a70024fa8ba5642906c6：指定该索引所在的表名。

2) 使用 ALTER TABLE 语句

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

• DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。

• DROP INDEX index_name：表示删除名称为 index_name 的索引。

• DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

删除索引

【实例 1】删除表 tb_stu_info 中的索引，输入的 SQL 语句和执行结果如下所示。

mysql> DROP INDEX height
    -> ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create 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=gb2312
1 row in set (0.00 sec)

【实例 2】删除表 tb_stu_info2 中名称为 id 的索引，输入的 SQL 语句和执行结果如下所示。

mysql> ALTER TABLE tb_stu_info2
    -> DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info2\G
*************************** 1. row ***************************
       Table: tb_stu_info2
Create 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=gb2312
1 row in set (0.00 sec)

The above is the detailed content of How to implement addition, deletion, modification and query of mysql index?. For more information, please follow other related articles on the PHP Chinese website!