mysql索引0affected_MySQL 索引的类型

主键索引(PRIMARY KEY)

# 主键 = 唯一键索引 + 非空 + 只能设置一个字段

# 创建表的时候创建主键索引

mysql> create table test(id int not null auto_increment primary key comment '学号');

Query OK, 0 rows affected (0.04 sec)

mysql> create table test1(id int not null auto_increment,primary key(id));

Query OK, 0 rows affected (0.04 sec)

# 查看索引命令

mysql> show index from test;

mysql> desc test;

# 已经有表时添加主键索引

mysql> alter table student add primary key(id);

mysql> alter table student add primary key pri_id(id);

# 删除主键索引

mysql> alter table student DROP PRIMARY KEY;

唯一键索引(UNIQUE KEY)

# 唯一键 = 普通索引 + 列中每个值是唯一的

# 创建表的时候创建唯一键索引

mysql> create table test2(id int not null auto_increment unique key comment '学号');

Query OK, 0 rows affected (0.04 sec)

# 已经有表时添加唯一键索引

mysql> alter table student add unique key(name);

mysql> alter table student add unique key uni_key(name);

# 删除唯一键索引

mysql> alter table student drop index name;

mysql> alter table student drop index uni_key;

# 注意：在创建唯一键索引(或主键索引)时，选择的列不能有重复数据

判断一列能否做唯一建索引

1.查询数据总量

mysql> select count(name) from city;

2.去重查看该列数据总量

mysql> select count(distinct name) from city;

# 以上两个值相等则可以设置唯一建索引

如下案例则可以创建 name字段的唯一键索引：

# 1.查看列的总数据量

mysql> select count(name) from country;

+-------------+

| count(name) |

+-------------+

| 239 |

+-------------+

1 row in set (0.00 sec)

# 2.查看去重后数据量

mysql> select count(distinct(name)) from country;

+-----------------------+

| count(distinct(name)) |

+-----------------------+

| 239 |

+-----------------------+

1 row in set (0.00 sec)

# 3.创建唯一建索引

mysql> alter table country add unique key uni_key(name);

普通索引(INDEX)

# 为了方便查找记录

# 方式一

mysql> alter table city add index inx_name(name);

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 方式二

mysql> create index index_District on city(District);

注意，这两种称呼是对建立索引技巧的一种称呼，并非索引的类型。

# 删除索引(方式一，方式二)

mysql> alter table city drop index inx_name;

mysql> drop index index_District on city;

全文索引(FULLTEXT)

mysql> create table txt(id int,bookname varchar(12),wenzhang text,fulltext(wenzhang));

Query OK, 0 rows affected (0.20 sec)

mysql> select * from txt where match(wenzhang) against('查询的内容');

# 实例

mysql> create table text(id int,bookname varchar(12) charset utf8,wenzhang text charset utf8,fulltext(wenzhang));

Query OK, 0 rows affected (0.21 sec)

mysql> insert into text values(1,'红楼梦','上回书说到张飞长坂坡三打白骨精救出宋江');

Query OK, 1 row affected (0.01 sec)

mysql> select * from text;

+------+-----------+-----------------------------------------------------------+

| id | bookname | wenzhang |

+------+-----------+-----------------------------------------------------------+

| 1 | 红楼梦 | 上回书说到张飞长坂坡三打白骨精救出宋江 |

+------+-----------+-----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from text where match(wenzhang) against('上回书说到张飞长坂坡三打白骨精救出宋江');

+------+-----------+-----------------------------------------------------------+

| id | bookname | wenzhang |

+------+-----------+-----------------------------------------------------------+

| 1 | 红楼梦 | 上回书说到张飞长坂坡三打白骨精救出宋江 |

+------+-----------+-----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table city drop index wenzhang;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0