12月阅读周·MySQL数据库入门:使用CREATE INDEX 语句在已经存在的表上创建索引篇

举报
叶一一 发表于 2024/12/25 09:32:33 2024/12/25
【摘要】 背景去年下半年,我在微信书架里加入了许多技术书籍,各种类别的都有,断断续续的读了一部分。没有计划的阅读,收效甚微。新年伊始,我准备尝试一下其他方式,比如阅读周。每月抽出1~2个非连续周,完整阅读一本书籍。这个“玩法”虽然常见且板正,但是有效,已经坚持阅读十一个月。已读完书籍:《架构简洁之道》、《深入浅出的Node.js》、《你不知道的JavaScript(上卷)》、《你不知道的JavaScr...

背景

去年下半年,我在微信书架里加入了许多技术书籍,各种类别的都有,断断续续的读了一部分。

没有计划的阅读,收效甚微。

新年伊始,我准备尝试一下其他方式,比如阅读周。每月抽出1~2个非连续周,完整阅读一本书籍。

这个“玩法”虽然常见且板正,但是有效,已经坚持阅读十一个月。

已读完书籍《架构简洁之道》、《深入浅出的Node.js》、《你不知道的JavaScript(上卷)》、《你不知道的JavaScript(中卷)》、《你不知道的JavaScript(下卷)》、《数据结构与算法JavaScript描述》、《WebKit技术内幕》、《前端架构:从入门到微前端》、《秒懂算法:用常识解读数据结构与算法》、《JavaScript权威指南》、《JavaScript异步编程设计快速响应的网络应用》、《编写可测试的JavaScript代码

当前阅读周书籍MySQL数据库入门

使用CREATE INDEX 语句在已经存在的表上创建索引

若想在一个已经存在的表上创建索引,可以使用CREATE INDEX语句,CREATE INDEX语句创建索引的具体语法格式如下所示:

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

在上述语法格式中,UNIQUE、FULLTEXT和SPATIAL都是可选参数,分别用于表示唯一性索引、全文索引和空间索引;INDEX用于指明字段为索引。为了便于读者学习如何使用CREATE INDEX语句在已经存在的表上创建索引,接下来,创建一个book表,该表中没有建立任何索引,创建book表的SQL语句如下所示:

C

REATE 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
);

创建好数据表book后,下面通过具体的案例为读者演示如何使用CREAT INDEX语句在已存在的数据表中创建索引,具体如下。

创建普通索引

【例1-1】 在book表中的bookid字段上建立一个名称为index_id的普通索引,SQL语句如下所示:

CREATE INDEX index_id ON book(bookid);

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

mysql>SHOW CREATE TABLE book \G
*************************** 1. row ***************************
          Table: book
Create Table: CREATE TABLE 'book' (
  'bookid' int(11) NOT NULL,
  'bookname' varchar(255) COLLATE utf8_bin NOT NULL,
  'authors' varchar(255) COLLATE utf8_bin NOT NULL,
  'info' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'comment' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'publicyear' year(4) NOT NULL,
  KEY 'index_id' ('bookid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

从上述结果可以看出,book表中的bookid字段上已经建立了一个名称为index_id的普通索引。

创建唯一性索引

【例1-2】 在book表中的bookid字段上建立一个名称为uniqueidx的唯一性索引,SQL语句如下所示:

CREATE UNIQUE INDEX uniqueidx ON book(bookid);

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

mysql>SHOW CREATE TABLE book \G
*************************** 1. row ***************************
          Table: book
Create Table: CREATE TABLE 'book' (
  'bookid' int(11) NOT NULL,
  'bookname' varchar(255) COLLATE utf8_bin NOT NULL,
  'authors' varchar(255) COLLATE utf8_bin NOT NULL,
  'info' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'comment' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'publicyear' year(4) NOT NULL,
  UNIQUE KEY 'uniqueidx' ('bookid'),
  KEY 'index_id' ('bookid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

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

创建单列索引

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

CREATE INDEX singleidx ON book(comment);

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

mysql>SHOW CREATE TABLE book \G
*************************** 1. row ***************************
        Table: book
Create Table: CREATE TABLE 'book' (
  'bookid' int(11) NOT NULL,
  'bookname' varchar(255) COLLATE utf8_bin NOT NULL,
  'authors' varchar(255) COLLATE utf8_bin NOT NULL,
  'info' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'comment' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'publicyear' year(4) NOT NULL,
  UNIQUE KEY 'uniqueidx' ('bookid'),
  KEY 'index_id' ('bookid'),
  KEY 'singleidx' ('comment')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

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

创建多列索引

【例1-4】 在book表中的authors和info字段上建立一个名称为mulitidx的多列索引,SQL语句如下所示:

CREATE INDEX mulitidx ON book(authors(20),info(20));

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

mysql>SHOW CREATE TABLE book \G
*************************** 1. row ***************************
        Table: book
Create Table: CREATE TABLE 'book' (
  'bookid' int(11) NOT NULL,
  'bookname' varchar(255) COLLATE utf8_bin NOT NULL,
  'authors' varchar(255) COLLATE utf8_bin NOT NULL,
  'info' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'comment' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'publicyear' year(4) NOT NULL,
  UNIQUE KEY 'uniqueidx' ('bookid'),
  KEY 'index_id' ('bookid'),
  KEY 'singleidx' ('comment'),
  KEY 'mulitidx' ('authors' (20), 'info' (20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

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

创建全文索引

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

DROP TABLE book;

然后重新创建表book,SQL语句如下:

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;

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

CREATE FULLTEXT INDEX fulltextidx ON book(info);

为了验证全文索引fulltextidx是否创建成功,使用SHOW CREATE TABLE语句查看表的结构,结果如下所示:

mysql>SHOW CREATE TABLE book \G
*************************** 1. row ***************************
        Table: book
Create Table: CREATE TABLE 'book' (
  'bookid' int(11) NOT NULL,
  'bookname' varchar(255) COLLATE utf8_bin NOT NULL,
  'authors' varchar(255) COLLATE utf8_bin NOT NULL,
  'info' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'comment' varchar(255) COLLATE utf8_bin DEFAULT NULL,
  'publicyear' year(4) NOT NULL,
  FULLTEXT KEY 'fulltextidx' ('info')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

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

创建空间索引

【例1-6】 创建表t7,在表中的g字段上创建名称为spatidx的空间索引。首先创建数据表t7,SQL语句如下:

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

使用CREATE INDEX语句在t7表的g字段上创建名称为spatidx的空间索引,SQL语句如下:

CREATE SPATIAL INDEX spatidx ON t7(g);

为了验证空间索引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=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

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

总结

若想在一个已经存在的表上创建索引,可以使用CREATE INDEX语句。


作者介绍
非职业「传道授业解惑」的开发者叶一一。
《趣学前端》、《CSS畅想》等系列作者。华夏美食、国漫、古风重度爱好者,刑侦、无限流小说初级玩家。
如果看完文章有所收获,欢迎点赞👍 | 收藏️ | 留言📝

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。