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

主鍵再介紹

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

1	PRIMARY KEY (vend_id)

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

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關鍵字指定。

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的值是最好的。
另外引擎的選擇，我覺得根據需求去選擇即可。