Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-15   MySQL   瀏覽次數:

MySQL - 第二十四章 | 建立表(中)

主鍵再介紹

主鍵值必須唯一。即,表中的每個行必須具有唯一的主鍵值。如果主鍵使用單個列,則它的值必須唯一。如果使用多個列,則這些列的組合值必須唯一。
迄今為止我們看到的CREATE TABLE例子都是用單個列作為主鍵。其中主鍵用以下的類似的語句定義:

1
PRIMARY KEY (vend_id)

為建立由多個列組成的主鍵,應該以逗號分隔的列表給出各列名,如下所示:

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

orderitems表包含orders表中每個訂單的細節。每個訂單有多項物品,但每個訂單任何時候都只有1個第一項物品,1個第二項物品,如此等等。因此,訂單號(order_num列)和訂單物品(order_item列)的組合是唯一的,從而適合作為主鍵,其定義為:

1
PRIMARY KEY (order_num, order_item)

主鍵可以在建立表時定義,或者在建立表之後定義。

提示:主鍵和NULL值 主鍵為其值唯一標識表中每個行的列。主鍵中只能使用不允許NULL值的列。允許NULL值的列不能作為唯一標識。

使用AUTO_INCREMENT

customers和orders表。customers表中的顧客由列cust_id唯一標識,每個顧客有一個唯一編號。類似,orders表中的每訂單有一個唯一的訂單號,這個訂單號存儲在列order_num中。

這些編號除它們是唯一的以外沒有別的特殊意義。在增加一個新顧客或新訂單時,需要一個新的顧客ID或訂單號。這些編號可以任意,只要它們是唯一的即可。

顯然,使用的最簡單的編號是下一個編號,所謂下一個編號是大於當前最大編號的編號。例如,如果cust_id的最大編號為10005,則插入表中的下一個顧客可以具有等於10006的cust_id。

簡單嗎?不見得。你怎樣確定下一個要使用的值?當然,你可以使用SELECT語句得出最大的數(Max()函數),然後對它加1。但這樣做並不可靠(你需要找出一種辦法來保證,在你執行SELECT和INSERT兩條語句之間沒有其他人插入行,對於多用戶應用,這種情況是很有可能出現的),而且效率也不高(執行額外的MySQL操作肯定不是理想的辦法)。

這就是AUTO_INCREMENT發揮作用的時候了。

1
cust_id      int       NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT告訴MySQL,本列每當增加一行時自動增量。每次執行一個INSERT操作時,MySQL自動對該列增量(從而才有這個關鍵字AUTO_INCREMENT),給該列賦予下一個可用的值。這樣給每個行分配一個唯一的cust_id,從而可以用作主鍵值。

每個表只允許一個AUTO_INCREMENT列,而且它必須被索引(如,通過使它成為主鍵)。

說明:覆蓋AUTO_INCREMENT 如果一個列被指定為AUTO_INCREMENT,則它需要使用特殊的值嗎?你可以簡單地在INSERT語句中指定一個值,只要它是唯一的(至今尚未使用過)即可,該值將被用來替代自動生成的值。後續的增量將開始使用該手工插入的值。

提示:確定AUTO_INCREMENT值 讓MySQL生成(通過自動增量)主鍵的一個缺點是你不知道這些值都是誰。考慮這個場景:你正在增加一個新訂單。這要求在orders表中建立一行,然後在orderitms表中對訂購的每項物品建立一行。order_num在orderitems表中與訂單細節一起存儲。這就是為什麼orders表和orderitems表為相互關聯的表的原因。這顯然要求你在插入orders行之後,插入orderitems行之前知道生成的order_num。那麼,如何在使用AUTO_INCREMENT列時獲得這個值呢?可使用last_insert_id()函數獲得這個值,如下所示:

1
SELECT last_insert)id()

此語句返回最後一個AUTO_INCREMENT值,然後可以將它用於後續的MySQL語句。

指定預設值

如果在插入行時沒有給出值,MySQL允許指定此時使用的預設值。
預設值用CREATE TABLE語句的列定義中的DEFAULT關鍵字指定。

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

分析:這條語句建立包含組成訂單的各物品的orderitems表(訂單本身存儲在orders表中)。quantity列包含訂單中每項物品的數量。在此例子中,給該列的描述添加文本DEFAULT 1指示MySQL,在未給出數量的情況下使用數量1。

注意:不允許函數 與大多數DBMS不一樣,MySQL不允許使用函數作為預設值,它只支持常量。

提示:使用預設值而不是NULL值 許多資料庫開發人員使用預設值而不是NULL列,特別是對用於計算或資料分組的列更是如此。

引擎類型

迄今為止使用的CREATE TABLE語句全都以ENGINE=InnoDB語句結束。

與其他DBMS一樣,MySQL有一個具體管理和處理資料的內部引擎。在你使用CREATE TABLE語句時,該引擎具體建立表,而在你使用SELECT語句或進行其他資料庫處理時,該引擎在內部處理你的請求。多數時候,此引擎都隱藏在DBMS內,不需要過多關注它。

但MySQL與其他DBMS不一樣,它具有多種引擎。它打包多個引擎,這些引擎都隱藏在MySQL服務器內,全都能執行CREATE TABLE和SELECT等命令。

為什麼要發行多種引擎呢?因為它們具有各自不同的功能和特性,為不同的任務選擇正確的引擎能獲得良好的功能和靈活性。

當然,你完全可以忽略這些資料庫引擎。如果省略ENGINE=語句,則使用預設引擎(很可能是MyISAM),多數SQL語句都會預設使用它。但並不是所有語句都預設使用它,這就是為什麼ENGINE=語句很重要的原因。

以下是幾個需要知道的引擎:

  • InnoDB是一個可靠的事務處理引擎,它不支持全文本搜索;
  • MEMORY在功能等同於MyISAM,但由於資料存儲在內存(不是磁盤)中,速度很快(特別適合於臨時表);
  • MyISAM是一個性能極高的引擎,它支持全文本搜索,但不支持事務處理。

說明:更多知識 所支持引擎的完整列表(及它們之間的不同),請參閱 https://dev.mysql.com/doc/refman/8.0/en/

引擎類型可以混用。
使用MyISAM外,例都使用InnoDB。
原因有些表是希望支持事務處理(因此,使用InnoDB),但部分的表也需要支持全文本搜索(因此,使用MyISAM)。

注意:外鍵不能跨引擎 混用引擎類型有一個大缺陷。外鍵(用於 強制實施引用完整性)不能跨引擎,即使用一個引擎的表不能引用具有使用不同引擎的表的外鍵。

結語

主鍵與AUTO_INCREMENT的使用,最主要用於唯一標示,在查找資料或是整合資料的時候非常方便使用。預設值的使用非常重要!在對計算或是判斷基本上都會設定社預設值;
題外話,如果整張表裡面有NULL的資料時候,在SELECE的時候,會被分開成兩段處理,這樣效能上就會有差異(這段經驗由DBA同事告知的經驗),所以建議就是整張表能不要出現有NULL的值是最好的。
另外引擎的選擇,我覺得根據需求去選擇即可。

註:以上參考了
MySQL必知必会 MySQL Crash Course