MySql之索引与约束的用法及区别

1、什么是索引？

索引是帮助MySQL高效获取数据的数据结构。

数据库在保存数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。索引可以大大提高MySQL的检索速度。

在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique和Index。

a.创建索引：

create table 表名(
    id int not null,
    name varchar(10) not null,
    index(name(length))      //使用name字段作为索引
);

b.显示索引：

show index from 表名;

c.删除索引：

alter table 表名 drop index name;

2、什么是约束？

约束就是保证数据的完整性和一致性，根据约束的字段数目的多少，约束又分为列级约束和表级约束。

列级约束：针对某一个字段来使用。

表级约束：针对两个或两个以上的字段使用。

约束类型	释义
not null	非空约束
primary key	主键约束
unique key	唯一约束
default	默认约束
foreign key	外键约束

约束类型

释义

not null

非空约束

primary key

主键约束

unique key

唯一约束

default

默认约束

foreign key

外键约束

1)、唯一(upique)约束

unique约束唯一标识数据库表中的每条记录。

unique和primary key约束均为列提供了唯一性的保证。

primary key被自动定义为unique约束。

//第一种方式
create table persons(
  id_p int not null,
  address varchar(255),
  city varchar(255),
  phone varchar(11) unique    //定义字段的同时，定义约束  
);

//第二种方式
create table persons(
    id_p int not null,
    address varchar(255),
    city varchar(255),
    phone varchar(11),
    unique phone(phone)      //单独一行命令，定义约束
);

//第三种方式
alter table persons add unique city(city);  //表后添加(修改表)

注：每个表都可以有多个unique约束，但是每个表只能有一个primary key约束。

2)、默认(default)约束

create table persons(
    id tinyint primary key auto_increment,
    name varchar(30),
    sex enum('m','w')default 'm'   //定义sex默认值为'm'
);

用于约束对应列中的值的默认值(除非默认为空值，否则不可插入空值)。

3)、主键(primary key)约束

每张数据表只能存在一个主键，主键保证记录的唯一性，主键自动为not null(同时作为表的索引)。

alter table 表名 add primary key (字段名);

为没有主键的表添加主键

4)、外键(foreign key)约束

• 外键约束是为了保持数据一致性，完整性，实现一对一或多对多关系。

• 子表（具有外键列的表）和父表（子表所参照的表），存储引擎只能为innoDB。
  外键列和参照列必须具有相似的数据类型。
  如果是数字类型，数字的长度是否有符号位必须相同
• 字符类型的长度则可以不同

外键列和参照列必须创建索引（如果外键列不存在索引的话，MySQL将自动创建索引）。

//先创建父表，子表才能建外键，父表和子表必须都是innodb引擎。

//city父表
create table city(
    id tinyint primary key,
    name varchar(10) not null
)engine=INNODB;

//students子表
create table students(
  id tinyint primary key auto increment,
  //定义字段时同时定义
  city tinyint,  //外键字段类型要与主表相同
  foreign key(city) references city(id),  //city字段作为外键，引用city表中的id
  )engine=INNODB;
  
  //主表的数据可以修改，但不能删除
  //删除city中的记录
  delete from city where id=1;
  
  //创建外键以后，再删除city记录，就会报错
  ERROR 1451(23000):Cannot delete or update a parent row:
  a foreign key constraint fails(`hxsd`.`students`,
  CONSTRAINT `students ibfk 1` FOREIGN KEY(`city`) REFERENCES `city`(`id`)

3、删除约束

alter table 表名 drop primary key;

    a.删除primary key

alter table 表名 drop index index_name;

    b.删除index

alter table 表名 drop foreign key FK_ID;

    c.删除外键约束

4、索引与约束的关系

索引是面向数据库本身的，用于查询优化等操作，约束则更多的是业务上的关系。

通常，创建唯一约束就自动获取唯一索引，是因为数据库认为对数据库进行唯一检查时，如果该字段上有索引会很快，所以创建唯一约束就默认创建唯一索引。同样，常见的主键即是唯一性的约束，也是个索引。但对于not null这样的约束，数据库是不会创建索引的。

以上是MySql之索引与约束的用法及区别的详细内容。更多信息请关注PHP中文网其他相关文章！