MYSQL索引使用

创建普通索引

CREATE TABLE test()
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);

创建唯一索引

UNIQUE KEY
ALTER TABLE test4 ADD INDEX in_username(username);
CREATE UNIQUE INDEX uni_username ON test(username);

创建全文索引

CREATE TABLE test1(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNQIUE,
userDesc VARCHAR(20) NOT NULL ,
FULLTEXT INDEX full_userDesc(userDesc)
);

DROP INDEX full_userDesc ON test6;

创建多列索引

CREATE TABLE test()
id TINYINT UNSIGNED AUTO_INCREMENT,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2(test1,test2)

;

创建空间索引

CREATE TABLE test(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
);

删除索引

DROP INDEX in_id ON test;
ALTER TABLE test DROP INDEX uni_card;

在已经存在的时候创建索引

CREATE INDEX in_id ON test4(id);
ALTER TABLE test4 ADD INDEX in_username(username);
CREATE UNIQUE INDEX uni_username ON test(username);