MySQL資料庫（主鍵、索引、外鍵、觸發器...）

標籤：

主鍵：

　　能夠唯一標識表中某一行的屬性或屬性群組。一個表只能有一個主鍵，但可以有多個候選索引。主鍵常常與外鍵構成參照完整性條件約束，防止出現資料不一致。主鍵可以保證記錄的唯一和主鍵域非空,資料庫管理系統對於主鍵自動產生唯一索引，所以主鍵也是一個特殊的索引。

索引：

　　是用來快速地尋找那些具有特定值的記錄。主要是為了檢索的方便，是為了加快訪問速度， 按一定的規則建立的，一般起到排序作用。

　　唯一性索引：這種索引和前面的“普通索引”基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須唯一。

　　注:當你的應用程式進行SQL查詢速度很慢時，應該想想是否可以建索引。

 

　　在資料庫表中，對欄位建立索引可以大大提高查詢速度。假如我們建立了一個 mytable表：

　　CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我們隨機向裡面插入了10000條記錄，其中有一條：5555, admin。

　　在尋找username="admin"的記錄 SELECT * FROM mytable WHERE username=‘admin‘;時，如果在username上已經建立了索引，MySQL無須任何掃描，即準確可找到該記錄。相反，MySQL會掃描所有記錄，即要查詢10000條記錄。

　　索引分單列索引和複合式索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是複合式索引。複合式索引，即一個索包含多個列。

• 普通索引：這是最基本的索引，它沒有任何限制
• 唯一索引：它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是複合式索引，則列值的組合必須唯一。
• 主鍵索引：它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時建立主鍵索引。
• 複合式索引：CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );  為了進一步榨取MySQL的效率，就要考慮建立複合式索引。就是將 name, city, age建到一個索引裡。

選擇和合適的資料類型：

• 通常情況下，越小的資料類型通常更好，越小的資料類型通常在磁碟、記憶體和CPU緩衝中都需要更少的空間，處理起來更快。
• 簡單的資料類型更好，整型資料比起字元，處理開銷更小，因為字串的比較更複雜。在MySQL中，應該用內建的日期和時間資料類型，而不是用字串來儲存時間；以及用整數資料型別儲存IP地址。
• 盡量避免NULL：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的列很難進行查詢最佳化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。

建立索引的時機：

　　一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。

外鍵：

　　是用於建立和加強兩個表資料之間的連結的一列或多列。外鍵約束主要用來維護兩個表之間資料的一致性。簡言之，表的外鍵就是另一表的主鍵，外鍵將兩表聯絡起來。一般情況下，要刪除一張表中的主鍵必須首先要確保其它表中的沒有相同外鍵（即該表中的主鍵沒有一個外鍵和它相關聯）。

MySQL中建立外鍵：

刪除時、更新時：

• RESTRICT：限制，指的是如果字表引用父表的某個欄位的值，那麼不允許直接刪除父表的該值。
• NO ACTION：無參照完整性關係，有了也不生效。
• CASCADE：級聯，刪除父表的某條記錄，字表中引用該值的記錄會被刪除。
• SET NULL：在父表上update/delete記錄時，將子表上匹配記錄的列設為null 。

觸發器：

　　觸發器是一種與表操作有關的資料庫物件，當觸發器所在表上出現指定事件時，將調用該對象，即表的操作事件觸發表上的觸發器的執行。

以Navicat for MySQL為例說明觸發器的使用：

　　

• 名：使用者自訂一個觸發器的名字
• 觸發：這裡有兩個選項（Before&After）說明是在執行語句之前還是在之後操作
• 插入、更新、刪除

可以建立6種觸發器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一個限制是不能同時在一個表上建立2個相同類型的觸發器，因此在一個表上最多建立6個觸發器。

INSERT 型觸發器：插入某一行時啟用觸發器，可能通過 INSERT、LOAD DATA、REPLACE 語句觸發；
UPDATE 型觸發器：更改某一行時啟用觸發器，可能通過 UPDATE 語句觸發；
DELETE 型觸發器：刪除某一行時啟用觸發器，可能通過 DELETE、REPLACE 語句觸發。

BEGIN … END 詳解
在MySQL中，BEGIN … END 語句的文法為：

BEGIN
[statement_list]
END
其中，statement_list 代表一個或多個語句的列表，列表內的每條語句都必須用分號（;）來結尾。
而在MySQL中，分號是語句結束的標識符，遇到分號表示該段語句已經結束，MySQL可以開始執行了。因此，解譯器遇到statement_list 中的分號後就開始執行，然後會報出錯誤，因為沒有找到和 BEGIN 匹配的 END。

這時就會用到 DELIMITER 命令（DELIMITER 是定界符，分隔字元的意思），它是一條命令，不需要語句結束標識，文法為：
DELIMITER new_delemiter
new_delemiter 可以設為1個或多個長度的符號，預設的是分號（;），我們可以把它修改為其他符號，如$：
DELIMITER $
在這之後的語句，以分號結束，解譯器不會有什麼反應，只有遇到了$，才認為是語句結束。注意，使用完之後，我們還應該記得把它給修改回來。

下面舉例說明一個完整的資料庫觸發器例子：

假設系統中有兩個表：
班級表 class(班級號 classID, 班內學生數 stuCount)
學生表 student(學號 stuID, 所屬班級號 classID)
要建立觸發器來使班級表中的班內學生數隨著學生的添加自動更新，代碼如下：

DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;

變數詳解
MySQL 中使用 DECLARE 來定義一局部變數，該變數只能在 BEGIN … END 複合陳述式中使用，並且應該定義在複合陳述式的開頭，

即其它語句之前，文法如下：

DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 為變數名稱，同 SQL 陳述式一樣，變數名不區分大小寫；type 為 MySQL 支援的任何資料類型；可以同時定義多個同類型的變數，用逗號隔開；變數初始值為 NULL，如果需要，可以使用 DEFAULT 子句提供預設值，值可以被指定為一個運算式。

對變數賦值採用 SET 語句，文法為：

SET var_name = expr [,var_name = expr] ...

NEW 與 OLD 詳解

上述樣本中使用了NEW關鍵字，和 MS SQL Server 中的 INSERTED 和 DELETED 類似，MySQL 中定義了 NEW 和 OLD，用來表示

觸發器的所在表中，觸發了觸發器的那一行資料。
具體地：
在 INSERT 型觸發器中，NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新資料；
在 UPDATE 型觸發器中，OLD 用來表示將要或已經被修改的原資料，NEW 用來表示將要或已經修改為的新資料；
在 DELETE 型觸發器中，OLD 用來表示將要或已經被刪除的原資料；
使用方法： NEW.columnName （columnName 為相應資料表某一列名）
另外，OLD 是唯讀，而 NEW 則可以在觸發器中使用 SET 賦值，這樣不會再次觸發觸發器，造成迴圈調用（如每插入一個學生前，都在其學號前加“2013”）。

查看觸發器

和查看資料庫（show databases;）查看錶格（show tables;）一樣，查看觸發器的文法如下：

SHOW TRIGGERS [FROM schema_name];
其中，schema_name 即 Schema 的名稱，在 MySQL 中 Schema 和 Database 是一樣的，也就是說，可以指定資料庫名，這樣就

不必先“USE database_name;”了。

刪除觸發器

和刪除資料庫、刪除表格一樣，刪除觸發器的文法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

觸發器的執行順序

我們建立的資料庫一般都是 InnoDB 資料庫，其上建立的表是事務性表，也就是事務安全的。這時，若SQL語句或觸發器執行失敗，MySQL 會復原事務，有：

①如果 BEFORE 觸發器執行失敗，SQL 無法正確執行。
②SQL 執行失敗時，AFTER 型觸發器不會觸發。
③AFTER 類型的觸發器執行失敗，SQL 會復原。

 

 

MySQL資料庫（主鍵、索引、外鍵、觸發器...）