小惠珠哦

这个屌丝很懒，什么也没留下！

热门标签

MySQL——索引（二）创建索引（3）使用 ALTER TABLE 语句在已经存在的表上创建索引

作者：小惠珠哦 | 2024-08-13 07:36:07

踩

在已经存在的表中创建索引，除了可以使用CREATE INDEX 语句外，还可以使用ALTER TABLE 语句。使用 ALTER TABLE 语句在已经存在的表上创建索引的语法格式如下所示:


ALTER TABLE 表名 ADD [UNIQUE｜FULLTEXT｜SPATIAL] INDEX
                 索引名(字段名[(长度)][ASCIDESC])

在上述语法格式中，UNIQUE、FULLTEXT 和 SPATIAL 都是可选参数，分别用于表示唯一性索引、全文索引和空间索引，ADD 表示向表中添加字段。接下来，同样以 book 表为例，对不同类型的索引进行详细讲解。为了使 book 表不包含任何索引，首先删除表 book，SQL语句如下:


mysql> DROP TABLE book;
Query OK, 0 rows affected (0.01 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
    -> );
Query OK, 0 rows affected (0.02 sec)

创建好数据表 book 后，就可以使用 ALTER TABLE 语句在已存在的数据表中创建索引了，具体如下

1)创建普通索引

例如，在表中的 bookid 字段上创建名称为 index_id 的普通索引，SQL 语句如下:


mysql> ALTER TABLE book ADD INDEX index_id(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,
  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> ALTER TABLE book ADD UNIQUE uniqueidx(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,
  UNIQUE KEY `uniqueidx` (`bookid`),
  KEY `index_id` (`bookid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

3)创建单列索引

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


mysql> ALTER TABLE book ADD INDEX singleidx(comment(50));
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 `index_id` (`bookid`),
  KEY `singleidx` (`comment`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

4)创建多列索引

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


mysql> ALTER TABLE book ADD INDEX multidx (authors(20),info(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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


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 `index_id` (`bookid`),
  KEY `singleidx` (`comment`(50)),
  KEY `multidx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

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

5)创建全文索引

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


mysql> DROP TABLE book;
Query OK, 0 rows affected (0.01 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.00 sec)

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


mysql> ALTER TABLE book ADD FULLTEXT INDEX fulltextidx(info);
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,
  FULLTEXT KEY `fulltextidx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

6)创建空间索引

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


mysql> create table t8(
    -> space GEOMETRY NOT NULL
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

使用 ALTER TABLE 语句在 book 表的 space 字段上创建名称为 spatidx 的空间索引，SQL语句如下所示:


mysql> ALTER TABLE t8 ADD SPATIAL INDEX spatidx(space);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

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


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

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小惠珠哦/article/detail/973835

​MySQL——索引（二）创建索引（3）使用 ALTER TABLE 语句在已经存在的表上创建索引

1)创建普通索引

2)创建唯一性索引

3)创建单列索引

4)创建多列索引

5)创建全文索引

6)创建空间索引

MySQL——索引（二）创建索引（3）使用 ALTER TABLE 语句在已经存在的表上创建索引