12月阅读周·MySQL数据库入门:使用CREATE INDEX 语句在已经存在的表上创建索引篇
背景
去年下半年,我在微信书架里加入了许多技术书籍,各种类别的都有,断断续续的读了一部分。
没有计划的阅读,收效甚微。
新年伊始,我准备尝试一下其他方式,比如阅读周。每月抽出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畅想》等系列作者。华夏美食、国漫、古风重度爱好者,刑侦、无限流小说初级玩家。
如果看完文章有所收获,欢迎点赞👍 | 收藏⭐️ | 留言📝。
- 点赞
- 收藏
- 关注作者
评论(0)