创建索引

三种创建索引的方式：

• CREATE TABLE语句，创建表时创建索引；
• ALTER TABLE语句，在已存在的表上创建索引；
• CREATE INDEX语句，在已存在的表上添加索引；

基本语法格式：

CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name]
(col_name [length])
[ASC | DESC]

UNIQUE FULLTEXT SPATIAL分别表示：唯一索引、全文索引、空间索引；

创建表时创建索引

普通索引

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,
  year_publication YEAR NOT NULL,
  INDEX(year_publication)
);

唯一索引

为id列创建了一个名为UniqIdx的唯一索引

CREATE TABLE t1
(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  UNIQUE INDEX UniqIdx(id)
);

单列索引

在id列上建立一个名为SingleIdx的单列索引，索引长度为20

CREATE TABLE t2
(
  id INT NOT NULL,
  name CHAR(50) NULL,
  INDEX SingleIdx(name(20))
);

组合索引

创建了一个组合索引，组合索引遵循最左前缀。索引查询时只能查询(id,) / (id, name) / (id, name, age)

CREATE TABLE t3
(
  id INT(11) NOT NULL,
  name CHAR(30) NOT NULL,
  age INT(11) NOT NULL,
  info VARCHAR(255),
  INDEX MultiIdx(id, name, age)
);

全文索引

只有MyISAM引擎支持全文索引，并且只为CHAR、VARCHAR、TEXT创建；

CREATE TABLE t4
(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR(255),
  FULLTEXT INDEX FullTxtIdx(info)
)ENGINE=MyISAM;

使用ALTER TABLE table_name ADD 创建索引

可以创建唯一索引、全文索引、单列索引、组合索引等；

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

示例：

# 简单索引
ALTER TABLE t1
ADD INDEX idxforcol(col1)

# 全文索引
ALTER TABLE t1
ADD FULLTEXT idxforcol(col2)

# 唯一索引
ALTER TABLE t1
ADD UNIQUE INDEX idxforcol(col3)

# 组合索引
ALTER TABLE t1
ADD INDEX idxforcol(col1, col2, col3)

使用CRETAE INDEX创建索引

基本语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC|DESC]

示例：

# 简单索引
CREATE INDEX idxforcol
ON t1(col1)

# 全文索引
CREATE FULLTEXT INDEX idxforcol
ON t1(col2)

# 唯一索引
CREATE UNIQUE INDEX idxforcol
ON t1(col3)

# 组合索引
CREATE INDEX idxforcol
ON t1(col1, col2, col3)

删除索引

• ALTER
• DROP

ALTER 删除索引

ALTER TABLE table_name DROP INDEX index_name;

DROP删除索引

DROP INDEX index_name ON table_name;