MySQL ALTER TABLE语句创建索引（非常详细，附带实例）

在已经存在的表里创建索引，MySQL 中可以使用 ALTER TABLE 语句。

ALTER TABLE创建索引的基本语法如下：

ALTER TABLE table_name  ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],…) [ASC | DESC]

ADD 关键字表示向表中添加索引。

【实例】在 book 表中的 bookname 字段上建立名为 BkNameIdx 的普通索引。
添加索引之前，使用 SHOW INDEX 语句查看指定表中创建的索引：

mysql> SHOW INDEX FROM book \G
*** 1. Row ***
     Table: book
  Non_unique: 1
   Key_name: year_publication
Seq_in_index: 1
Column_name: year_publication
  Collation: A
Cardinality: 0
   Sub_part: NULL
    Packed: NULL
      Null:
  Index_type: BTREE
    Comment:
Index_comment:

其中各个主要参数的含义为：

Table 表示创建索引的表。
Non_unique 表示索引非唯一，1 代表是非唯一索引，0 代表唯一索引。
Key_name 表示索引的名称。
Seq_in_index 表示该字段在索引中的位置，单列索引该值为 1，组合索引为每个字段在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Sub_part 表示索引的长度。
Null 表示该字段是否能为空值。
Index_type 表示索引类型。

可以看到，book 表中已经存在了一个索引，即前面已经定义的名称为 year_publication 索引，该索引为非唯一索引。

下面使用 ALTER TABLE 在 bookname 字段上添加索引，SQL 语句如下：

ALTER TABLE book ADD INDEX BkNameIdx( bookname(30) );

使用 SHOW INDEX 语句查看表中的索引：

mysql> SHOW INDEX FROM book \G
*** 1. Row ***
     Table: book
  Non_unique: 1
   Key_name: year_publication
Seq_in_index: 1
Column_name: year_publication
  Collation: A
Cardinality: 0
   Sub_part: NULL
    Packed: NULL
      Null:
  Index_type: BTREE
    Comment:
Index_comment:
*** 2. Row ***
     Table: book
  Non_unique: 1
   Key_name: BkNameIdx
Seq_in_index: 1
Column_name: bookname
  Collation: A
Cardinality: 0
   Sub_part: 30
    Packed: NULL
      Null:
  Index_type: BTREE
    Comment:
Index_comment:

可以看到，现在表中已经有了两个索引，一个是 year_publication，另一个为通过 ALTER TABLE 语句添加的、名称为 BkNameIdx 的索引，该索引为非唯一索引，长度为 30。

【实例】在 book 表的 bookId 字段上建立名称为 UniqidIdx 的唯一索引，SQL 语句如下：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

使用 SHOW INDEX 语句查看表中的索引：

mysql> SHOW INDEX FROM book \G
*** 1. Row ***
     Table: book
  Non_unique: 0
   Key_name: UniqidIdx
Seq_in_index: 1
Column_name: bookid
  Collation: A
Cardinality: 0
   Sub_part: NULL
    Packed: NULL
      Null:
  Index_type: BTREE
    Comment:
Index_comment:

可以看到 Non_unique 属性值为 0，表示名称为 UniqidIdx 的索引为唯一索引，创建唯一索引成功。

【实例】在 book 表的 comment 字段上建立单列索引，SQL 语句如下：

ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

使用 SHOW INDEX 语句查看表中的索引：

*** 3. Row ***
     Table: book
  Non_unique: 1
   Key_name: BkcmtIdx
Seq_in_index: 1
Column_name: comment
  Collation: A
Cardinality: 0
   Sub_part: 50
    Packed: NULL
      Null: YES
  Index_type: BTREE
    Comment:
Index_comment:

可以看到，语句执行之后在 book 表的 comment 字段上建立了名称为 BkcmtIdx 的索引，长度为 50，在查询时，只需要检索前 50 个字符。

【实例】在 book 表的 authors 和 info 字段上建立组合索引，SQL 语句如下：

ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(30),info(50) );

使用 SHOW INDEX 语句查看表中的索引：

mysql> SHOW INDEX FROM book \G
*** 4. Row ***
     Table: book
  Non_unique: 1
   Key_name: BkAuAndInfoIdx
Seq_in_index: 1
Column_name: authors
  Collation: A
Cardinality: 0
   Sub_part: 30
    Packed: NULL
      Null:
  Index_type: BTREE
    Comment:
Index_comment:
*** 5. Row ***
     Table: book
  Non_unique: 1
   Key_name: BkAuAndInfoIdx
Seq_in_index: 2
Column_name: info
  Collation: A
Cardinality: 0
   Sub_part: 50
    Packed: NULL
      Null: YES
  Index_type: BTREE
    Comment:
Index_comment:

可以看到名称为 BkAuAndInfoIdx 的索引由两个字段组成：

authors 字段长度为 30，在组合索引中的序号为 1，该字段不允许空值 NULL；
info 字段长度为 50，在组合索引中的序号为 2，该字段可以为空值 NULL。

【实例】创建表 t6，在 t6 表上使用 ALTER TABLE 创建全文索引。
首先创建表 t6，语句如下：

CREATE TABLE t6
(
id INT NOT NULL,
info CHAR(255)
) ENGINE=MyISAM;

注意，修改 ENGINE 参数为 MyISAM，MySQL 默认引擎 InnoDB 不支持全文索引。

使用 ALTER TABLE 语句在 info 字段上创建全文索引：

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

使用 SHOW INDEX 语句查看索引：

mysql> SHOW index from t6 \G
** 1. Row ***
    Table: t6
Non_unique: 1
  Key_name: infoFTIdx
Seq_in_index: 1
Column_name: info
  Collation: NULL
Cardinality: NULL
  Sub_part: NULL
    Packed: NULL
     Null: YES
Index_type: FULLTEXT
   Comment:
ndex_comment:

可以看到，t6 表中已经创建了名称为 infoFTIdx 的索引，该索引在 info 字段上创建，类型为 FULLTEXT，允许空值。

【实例】创建表 t7，在 t7 的空间数据类型字段 g 上创建名称为 spatIdx 的空间索引。

CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;

使用 ALTER TABLE 在表 t7 的 g 字段建立空间索引：

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

使用 SHOW INDEX 语句查看索引：

mysql> SHOW index from t7 \G
*** 1. Row ***
     Table: t7
  Non_unique: 1
   Key_name: spatIdx
Seq_in_index: 1
Column_name: g
  Collation: A
Cardinality: NULL
   Sub_part: 32
    Packed: NULL
      Null:
  Index_type: SPATIAL
    Comment:
Index_comment:

可以看到，t7 表的 g 字段上创建了名称为 spatIdx 的空间索引。

MySQL ALTER TABLE语句创建索引（非常详细，附带实例）

相关文章