​MySQL——索引（三）创建索引（2）使用 CREATE INDEX 语句在已经存在的表上创建索引

        若想在一个已经存在的表上创建索引，可以使用 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 的空间索引。