MySQL索引创建和删除

本文主要是介绍MySQL索引创建和删除，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

索引分类

唯一索引：索引列的值必须唯一，但是允许有多个null值
单值索引：一个索引只包含单个列，一个表中可以有多个单值索引
符合索引：一个索引包含多个列

创建索引

首先创建一张测试表

CREATE TABLE if not exists `test` (
    `id` BIGINT NOT NULL COMMENT 'id',
    `username` VARCHAR(100) NOT NULL COMMENT '用户名',
    `age` TINYINT not null COMMENT '年龄',
    `gender` TINYINT(1) DEFAULT 0 COMMENT '性别：0-男，1-女',
    `deleted` TINYINT(1) DEFAULT 0 COMMENT '0-未删除，1-已删除',
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '测试表';

唯一索引：给username字段创建唯一索引

CREATE UNIQUE INDEX uk_test_username ON test(username); 

单值索引：给deleted字段创建单值索引

`CREATE INDEX index_test_deleted ON test(deleted);`

复合索引：给age 和 gender 字段创建复合索引

`create INDEX index_test_age_gender ON test(age,gender);`

查看索引

show index from tableName;

mysql> show index from test;
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test  |          0 | PRIMARY               |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test  |          0 | uk_test_username      |            1 | username    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test  |          1 | index_test_deleted    |            1 | deleted     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| test  |          1 | index_test_age_gender |            1 | age         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| test  |          1 | index_test_age_gender |            2 | gender      | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)

删除索引

DROP INDEX indexName ON tableName;

DROP index uk_test_username on test;

这篇关于MySQL索引创建和删除的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！