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	外键约束

1)、唯一(upique)约束

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

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

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

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

//第一种方式
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);  //表后添加(修改表)

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、删除约束

    a.删除primary key

alter table 表名 drop primary key;

    b.删除index

alter table 表名 drop index index_name;

    c.删除外键约束

alter table 表名 drop foreign key FK_ID;

4、索引与约束的关系

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

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

Atas ialah kandungan terperinci MySql之索引与约束的用法及区别. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!