mysql索引创建

索引类别

UNIQUE:唯一性索引

FULLTEXT:全文性索引

SPATIAL:空间索引

 

创建普通索引

CREATE TABLE t_user1(id INT ,

                     userName VARCHAR(20),

                     PASSWORD VARCHAR(20),

                     INDEX (userName)

                      );

                     

 

创建唯一性索引

CREATE TABLE t_user2(id INT ,

                     userName VARCHAR(20),

                     PASSWORD VARCHAR(20),

                     UNIQUEINDEX index_userName(userName)

                     );

                   

创建多列索引

CREATE TABLE t_user3(id INT ,

                     userName VARCHAR(20),

                     PASSWORD VARCHAR(20),

                     INDEX index_userName_password别名(userName,PASSWORD)

                      );

                     

创建普通索引

CREATE   INDEXindex_userName ON t_user4(userName);

 

创建唯一性索引

CREATE   UNIQUEINDEX index_userName ON t_user4(userName);

 

创建多列索引

CREATE INDEX index_userName_password ON t_user4(userName,PASSWORD);

 

插入索引

ALTER TABLE t_user5 ADD INDEXindex_userName(userName);

插入唯一索引

ALTER TABLE t_user5 ADD UNIQUE INDEXindex_userName(userName);

插入多列索引

ALTER TABLE t_user5 ADD INDEXindex_userName_password(userName,PASSWORD);

删除索引

DROP INDEX index_userName ON t_user5;

 

DROP INDEX index_userName_password ONt_user5;