在MySQL中, 有四种类型的索引,
主键, 唯一索引, 全文索引和普通索引
主键(Primary Key)就是值唯一并且没有值为NULL的域的索引
NULL
#创建表, 并设置主键
mysql> create table pk_test(
-> f1 int not NULL,
-> primary key(f1));
#已存在的表上创建主键, 语法: alter table tablename add primary key(fieldname1 [, fieldname2])
mysql> alter table customer modify id int not null, add primary key(id);普通索引容许重复的值
#创建普通索引语法 mysql> create table(fieldname columntype, fieldname2 columntype, index[indexname](fieldname1 [,fieldname2...]));
#创建后添加索引: alter table tablename add index[indexname](fieldname1 [,fieldname2...]);
mysql> alter table sales add index(value);全文索引
#创建全文索引语法 mysql> create table(fieldname columntype, fieldname2 columntype, fulltext[indexname](fieldname1 [,fieldname2...]));
mysql> insert into ft2 values
-> ('Waiting for the barbarians'),
-> ('In the Heart of the Country'),
-> ('The master of Patersbury'),
-> ('Writing and Being'),
-> ('Heart of the Beast');
-> ('Heart of the Beest'),
-> ('The beginning and the End'),
-> ('Master Master'),
-> ('A barBarian at my door');
Query OK, 4 rows affected (0.00 sec)
#创建表后再添加全文索引:alter table tablename add fulltext[indexname](fieldname1 [,fieldname2...]);
mysql> create table ft(f1 varchar(255), f2 text, f3 blob, f4 int);
mysql> alter table ft add fulltext(f1, f2);全文索引的用法
文本域查找与大小写无关
# match()匹配属性, against()匹配值
mysql> select * from ft2 where match(f1) against ('master');唯一索引除了不容许有重复的记录外, 与普通索引一样
#创建普通索引语法 mysql> create table(fieldname columntype, fieldname2 columntype, unique(fieldname1 [,fieldname2...]));
mysql> create table ui_test(f1 int, f2 int, unique(f1));
mysql> insert into ui_test values(1, 2);
#f1域不能包含重复值
mysql> insert into ui_test values(1, 3);
ERROR 1062 (23000): Duplicate entry '1' for key 'f1'
#创建后添加索引: alter table tablename add unique[indexname](fieldname1 [,fieldname2...]);#删除主键
alter table tablename drop primary key;
#删除普通, 唯一, 全文索引, 需要制定索引名
alter table tablename frop index indexname;
#显示全部索引名
show keys from tablename;