MySQL索引创建/删除

创建索引

1. 建表时创建

方式一
CREATE TABLE IF NOT EXISTS examination_info (
	id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
	exam_id INT(11) UNIQUE NOT NULL COMMENT '试卷ID',
	tag varchar(32) COMMENT '类别标签',
	difficulty varchar(8) COMMENT '难度',
	duration int NOT NULL COMMENT '时长',
	release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

方式二
CREATE TABLE IF NOT EXISTS examination_info (
	id INT(11)  AUTO_INCREMENT COMMENT '自增ID',
	exam_id INT(11) NOT NULL COMMENT '试卷ID',
	tag varchar(32) COMMENT '类别标签',
	difficulty varchar(8) COMMENT '难度',
	duration int NOT NULL COMMENT '时长',
	release_time datetime COMMENT '发布时间'
	KEY tag_index (tag),
	PRIMARY KEY(id),
	UNIQUE KEY(exam_id)
)CHARACTER SET utf8 COLLATE utf8_bin;

普通索引
CREATE INDEX idx_duration ON examination_info(duration);
唯一索引
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
全文索引
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

普通索引
ALTER TABLE examination_info ADD INDEX idx_duration(duration);
唯一索引
ALTER TABLE examination_info ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);
全文索引
ALTER TABLE examination_info ADD FULLTEXT INDEX full_idx_tag(tag);

删除索引

唯一索引
ALTER TABLE examination_info DROP KEY uniq_idx_exam_id;
普通索引
ALTER TABLE examination_info DROP KEY full_idx_tag;