MySQL——数据库和表的基本操作（六）索引（2）创建索引②使用 CREATE INDEX 语句在已经存在的表上创建索引

Code repairman

已于 2024-08-29 17:14:16 修改

阅读量1.1k

点赞数 26

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-08-08 09:09:09 首次发布

本文链接：https://blog.csdn.net/W_Fe5/article/details/141016927

版权

MySQL 专栏收录该内容

97 篇文章 1 订阅

订阅专栏

若想在一个已经存在的表上创建索引，可以使用 CREATE INDEX 语句，CREATEINDEX语句创建索引的具体语法格式如下所示:

CREATE [UNIQUEIFULLTEXTISPATIAL]INDEX 索引名
ON 表名(字段名[(长度)J[ASCIDESC]);

在上述语法格式中，UNIQUE、FULLTEXT 和 SPATIAL都是可选参数，分别用于表示唯一性索引、全文索引和空间索引;INDEX用于指明字段为索引。

为了便于学习如何使用CREATE INDEX语句在已经存在的表上创建索引，接下来，创建一个 book 表，该表中没有建立任何索引，创建 book 表的 SQL语句如下所示

mysql> create table book(
    -> bookid INT NOT NULL,
    -> bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL,
    -> info VARCHAR(255) NULL,
    -> comment VARCHAR(255) NULL,
    -> publicyear YEAR NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

创建好数据表 book 后，下面通过具体的案例为读者演示如何使用 CREAT INDEX语句在已存在的数据表中创建索引，具体如下。

1)创建普通索引

例如，在 book 表中的 bookid 字段上建立一个名称为 index_id 的普通索引，SQL 语句如下所示:

mysql> CREATE INDEX index_id ON book(bookid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

上述 SQL 语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:

mysql> show create table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year NOT NULL,
  KEY `index_id` (`bookid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从上述结果可以看出，book 表中的 bookid 字段上已经建立了一个名称为 index_id的普通索引。

2)创建唯一性索引

例如，在 book 表中的 bookid 字段上建立一个名称为 uniqueidx 的唯一性索引，SQL 语句如下所示:

mysql> CREATE UNIQUE INDEX uniqueidx ON book(bookid);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

上述 SQL 语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:

mysql> show create table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从上述结果可以看出，book 表中的 bookid 字段上已经建立了一个名称为 uniqueids的唯一性索引。

3)创建单列索引

例如，在 book 表中的 comment 字段上建立一个名称为 singleidx 的单列索引SQL语句如下所示:

CREATE INDEX singleidx ON book(comment);

上述 SQL 语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:

mysql> CREATE INDEX singleidx ON book(comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

从上述结果可以看出，book 表中的 comment 字段上已经建立了一个名称为singleidx 的单列索引。

4)创建多列索引

例如，在 book 表中的 authors 和 info 字段上建立一个名称为 mulitidx 的多列索引，SQL 语句如下所示:

CREATE INDEX mulitidx ON book(authors(20),info(20));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

上述SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:

mysql> show create table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year NOT NULL,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `singleidx` (`comment`),
  KEY `mulitidx` (`authors`(20),`info`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从上述结果可以看出，book 表中的 authors 和 info 字段上已经建立了一个名称为mulitidx的多列索引。

5)创建全文索引

例如，删除表 book，重新创建表 book，在表中的 info 字段上创建全文索引.首先删除表 book，SQL 语句如下:

mysql> DROP TABLE book;
Query OK, 0 rows affected (0.00 sec)

然后重新创建表 book，SQL 语句如下:

mysql> create table book(
    -> bookid INT NOT NULL,
    -> bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL,
    -> info VARCHAR(255) NULL,
    -> comment VARCHAR(255) NULL,
    -> publicyear YEAR NOT NULL
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

使用 CREATE INDEX语句在 book 表的 info 字段上创建名称为 fulltextidx 的全文索引，SQL语句如下:

mysql> CREATE FULLTEXT INDEX fulltextidx ON book(info);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

为了验证全文索引 fulltextidx 是否创建成功，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:

mysql> show create table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `publicyear` year NOT NULL,
  FULLTEXT KEY `fulltextidx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从上述结果可以看出，book 表中的 info 字段上已经建立了一个名称为 fulltextidy的全文索引。

6)创建空间索引

例如，创建表 t7，在表中的g字段上创建名称为 spatidx 的空间索引首先创建数据表 t7，SQL语句如下:

mysql> create table t7(
    -> g GEOMETRY NOT NULL
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

使用 CREATE INDEX 语句在 t7 表的g字段上创建名称为 spatidx 的空间索引SQL语句如下:

mysql> CREATE SPATIAL INDEX spatidx ON t7(g);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

为了验证空间索引spatidx是否创建成功，使用 SHOW CREATE TABLE 语句查看表的结构，结果如下所示:

mysql> show create table t7 \G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

从上述结果可以看出，book 表中的g字段上已经建立了一个名称为 spatidx 的空间索引。