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這樣的約束，資料庫是不會建立索引的。

以上是MySql之索引與約束的用法及區別的詳細內容。更多資訊請關注PHP中文網其他相關文章！