8.2 创建索引

8.2.1. 创建表的时候创建索引

基础语法：

CREATE TABLE 表名 [列名 属性]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (列名[索引长度]) [ASC|DESC]

备注：[UNIQUE|FULLTEXT|SPATIAL] 可选参数，表示唯一索引，全文索引和空间索引；

​ [INDEX|KEY] 两者作用相同，用来创建索引；

​ [ASC|DESC] 用于指定升序还是降序的索引值存储

1. 创建普通索引

   CREATE TABLE test (
       id        int(8) not null,
       name    VARCHAR(50) not null,
       author    VARCHAR(50) not null,
       num        VARCHAR(50) not null,
       INDEX(num)
   );

2. 创建唯一索引

   CREATE TABLE test (
       id        int(8) not null,
       name    VARCHAR(50) not null,
       author    VARCHAR(50) not null,
       num        VARCHAR(50) not null,
       UNIQUE INDEX UniqIndex(id)
   );

3. 创建单列索引

   CREATE TABLE test (
       id        int(8) not null,
       name    VARCHAR(50) not null,
       author    VARCHAR(50) not null,
       num        VARCHAR(50) not null,
       INDEX SingleIdx(name(50))
   );

4. 创建组合索引

   CREATE TABLE test (
       id        int(8) not null,
       name    VARCHAR(50) not null,
       author    VARCHAR(50) not null,
       num        VARCHAR(50) not null,
       INDEX MultiIdx(id, name, num(50))
   );

   注意：索引使用的规则是最左前缀，强行按照sql中的顺序进行存放，查询时候只能用id、id 和name， 全都有的三种方式去查询使用，否则不生效

5. 创建全文索引

   CREATE TABLE test (
       id        int(8) not null,
       name    VARCHAR(50) not null,
       author    VARCHAR(50) not null,
       num        VARCHAR(50) not null,
       FULLTEXT INDEX FullTestIdx(name(50))
   ) ENGINE = MyISAM;

   注意：因为mysql默认引擎是InnoDB，所以要在这里声明

6. 创建空间索引

   CREATE TABLE test (
       id        int(8) not null,
       name    VARCHAR(50) not null,
       author    VARCHAR(50) not null,
       num        VARCHAR(50) not null,
       SPATIAL INDEX FullTestIdx(name)
   ) ENGINE = MyISAM;

   注意：索引字段的非空要求，存储引擎的变更

8.2.2 在已存在的表上创建索引

使用ALTER TABLE或CREATE TABLE语句在表上添加索引

1. 使用ALTER TABLE语句创建索引

   ALTER TABLE 表名 ADD
   [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (列名[索引长度]) [ASC|DESC]

   1. 创建普通索引

      ALTER TABLE test ADD INDEX nameIdx(name(30));

   2. 创建唯一索引

      ALTER TABLE test ADD UNIQUE INDEX UniqIdIdx(id);

   3. 创建单列索引

      ALTER TABLE test ADD INDEX nameIdx(num);

   4. 创建组合索引

      ALTER TABLE test ADD INDEX nameIdx(id, num(30));

   5. 创建默认全文索引

      ALTER TABLE test ADD INDEX InfoIdx(name);

      注意：表需要时MyISAM存储引擎

   6. 创建空间索引

      ALTER TABLE test ADD SPATIAL nameIdx(name);

      注意：表需要时MyISAM存储引擎，对应字段非空限制

2. 使用CREATE INDEX创建索引

   基础语法：

   CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名] ON 表名(列名[索引长度]) [ASC|DESC]

   1. 创建普通索引

      CREATE INDEX nameIdx ON test(name(30));

   2. 创建唯一索引

      CREATE UNIQUE INDEX nameIdx ON test(name(30));

   3. 创建单列索引

      CREATE INDEX nameIdx ON test(name(30));

   4. 创建组合索引

      CREATE INDEX nameIdx ON test(id, num);

   5. 创建默认全文索引

      CREATE FULLTEXT INDEX nameIdx ON test(name(30));

      注意：表需要时MyISAM存储引擎

   6. 创建空间索引

      CREATE SPATIAL INDEX nameIdx ON test(name(30));

      注意：表需要时MyISAM存储引擎，对应字段非空限制