第二十五章 使用觸發器

1 觸發器

MySQL語句在需要時被執行，儲存過程也是如此，但是如果想要某條語句在事件發生時自動執行，如：
1，每當增加一個顧客到某個資料庫表時，都檢查其電話號碼格式是否正確，州的縮寫是否為大寫
2，每當訂購一個產品時，都從庫存數量中減去訂購的數量
3，無論何時刪除一行，都在某個存檔表中保留一個副本

上述例子的共同之處是它們都需要在某個表發生變化時自動處理，這確切的講就是觸發器。觸發器是MySQL相應delete，insert，update而自動執行的一條MySQL語句，只有這3條語句支援觸發器

2 建立觸發器

建立觸發器時，需要給出4條資訊：
1，唯一的觸發器名
2，觸發器關聯的表
3，觸發器應該響應的活動(delete，insert，update)
4，觸發器何時執行(處理前或處理後)

需要保證每個資料庫的觸發器名唯一

觸發器使用create trigger語句建立：

create trigger newproduct after insert on products
for each row select 'product added';

create trigger用來建立一個名為newproduct的新觸發器，觸發器可在一個操作發生之前或之後執行，after insert用來表明觸發器將在insert語句成功執行後執行，這個觸發器還指定了for each row，因此程式碼對每個插入行執行，文字productnadded將對每個插入的行顯示一次

只有表才能使用觸發器，檢視和臨時表不支援使用觸發器

觸發器按每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發器，因此一個表最多支援6個觸發器，單一觸發器不能和多個事件或多個表關聯，所以如果需要一個對insert和update操作執行的觸發器，則應該定義兩個觸發器

3 刪除觸發器

刪除一個觸發器，可以使用drop trigger語句：

drop trigger newproduct;

觸發器不能更新或覆蓋，為了修改一個觸發器，必須先刪除它，然後再重新建立

4 使用觸發器

(1) insert觸發器

insert觸發器再insert語句執行之前或之後發生，需要明白幾點

1，在insert觸發器程式碼中，可引用一個名為new的虛擬表，訪問被插入的行
2，在before insert觸發器中，new中的值可以被更新(允許更改被插入的行)
3，對於auto_increment列，new在insert執行之前為0，在insert執行後包含新的自動生成值

create trigger neworder after insert on orders
for each row select new.order_num;

此程式碼建立一個名為neworder的觸發器
它按照after insert on orders執行，在插入一個新訂單到orders表時，MySQL生成一個新訂單號並儲存到order_num中，觸發器從new.order_num中取得這個值並返回它，此觸發器必須按照after insert執行，因此在before insert執行之前，新order_num還沒有生成，對於orders的每次插入使用這個觸發器講總是返回新的訂單號

通常講before用於資料驗證和淨化(先保證處理的資料是需要的資料)

(2) delete觸發器

delete觸發器在delete語句執行之前或執行後執行

1，在delete觸發器程式碼內，可引用一個名為old的虛擬表，訪問被刪除的行
2，old中的值都是隻讀的，不能更新

create trigger deleteorder before delete on orders
for each row
begin
	insert into archive_orders(order_num, order_date, cust_id)
	values(old.order_num,old.order_date,old.cust_id);
end;

上述deleteorder觸發器在任意行被刪除前，使用一條insert語句將old中的值(要刪除的訂單)儲存到一個名為archive的存檔表中

觸發器中也可以使用多條begin end語句，它的好處是使觸發器能容納多條SQL語句

(3) update觸發器

update觸發器在update語句執行前或執行後執行

1，在update觸發器程式碼中，可以引用一個名為old的虛擬表訪問以前(執行update語句前)的值，可以引用一個名為new的虛擬表訪問更新後(執行update語句後)的值
2，在before update觸發器中，new中的值也可能被更新(允許更改將要用於update語句中的值)
3，old中的值全都是隻讀的，不可更新

上述語句完成了保證了州名的縮寫總是大寫，顯然任何資料淨化都需要在update之前完成

5 進一步瞭解觸發器

1，建立觸發器可能需要特殊的安全訪問許可權，但是觸發器的執行是自動的，如果insert，update，delete語句能夠執行，則相關的觸發器也能執行

2，應該用觸發器來保證資料的一致性(大小寫，格式統一)，在觸發器中執行這種型別的處理是它總是進行這種處理，而且是透明地進行，與客戶機無關

3，觸發器的一種非常有意義的作用是建立審計跟蹤，使用觸發器，可以把更改的行記錄到另一個表中(如果需要，甚至還有之前和之後的狀態)

第二十六章 管理事物處理

1 事物處理

並非所有的引擎都支援事務處理，MySQL支援幾種基本的資料庫引擎，MyISAM和InnoDB是兩種最常用的引擎，但MyISAM不支援事物處理，而InnoDB支援，如果你的應用中需要事物處理，則一定要選擇正確的引擎來建立表

事物處理可以用來維護資料庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼完全不執行

回顧前面的orders表，訂單儲存在orders和orderitems兩個表中，orders儲存實際的訂單，而orderitems儲存訂單的各項物品，這兩個表用主鍵的唯一ID相關聯，這兩個表又與包含客戶和產品資訊的其它表相關聯

給系統新增新訂單的過程如下：
1，檢查資料庫中是否存在相應的客戶，如果不存在則新增他
2，檢索客戶的ID
3，新增一行到orders表中，把它和客戶ID關聯
4，檢索orders表中賦予的新訂單ID
5，對於訂購的每個物品在orderitems表中新增一行，通過檢索出來的ID把它與orders表相關聯

現在假如由於某種資料庫故障(如超出磁碟空間，安全限制，表鎖)等阻止了這個過程，資料庫中的資料會出現幾種情況

1，如果故障發生在新增了客戶後，orders表新增之前，不會有什麼問題，某些客戶沒有訂單是合法的，在排錯後重新執行此過程時，所插入的客戶記錄將被檢索和使用，可以有效地從故障發生的地方開始繼續執行

2，如果故障發生在orders行新增之後，orderitems行新增之前，會導致資料庫中出現一個空訂單

3，如果系統在新增orderitems行中出現錯誤，結果是資料庫中存在不完整的訂單

為了避免這些問題的出現，就需要使用事物處理，事物處理是一種機制，用來管理成批的MySQL操作，以保證資料庫不包含不完整的操作結果，利用事物處理，可以保證一組操作不會中途停止，它們作為整體一起執行，或者全部不執行

如果沒有錯誤發生，整租語句提交給資料庫表，如果發生了錯誤，則進行回退以恢復資料庫到某個已經且安全的狀態

使用事物處理的幾個術語：
1，事物：指一組SQL語句
2，回退：指撤銷指定SQL語句的過程
3，提交：指將未儲存的SQL語句結果寫入資料庫表
4，保留點：指事物處理中設定的臨時佔位符，可以對它釋出回退(與回退整個事物處理不同)

2 控制事物處理

管理事物處理的關鍵在於，將SQL語句分解為邏輯塊，並明確規定資料何時應該回退，何時不應該回退

MySQL使用以下語句標誌事物的開始：

start transaction

(1) 使用rollback

MySQL的rollback命令用來回退(撤銷)MySQL語句

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

首先執行一條select語句顯示該表不為空，然後開始一個事物處理，用一條delete語句刪除ordertotals中的所有行，另一條select語句驗證ordertotals已經為空，rollback語句回退start transaction之後的所有語句，最後一條select語句顯示該表不為空

rollback語句只能在一個事物處理內使用

事物處理用來管理insert，update，delete語句，不能回退select語句。不能回退create或drop操作，事物處理塊中可以使用這兩條語句，但如果執行回退，它們不會被撤銷

(2) 使用commit

一般的MySQL語句但是直接對資料庫表執行和編寫的，這就是所謂的隱含提交，即提交操作是自動進行的，但是事物處理過程中，提交不會隱含地進行，需要使用commit語句進行明確地提交

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

上述例子涉及到了兩個表，所以使用事務處理塊來保證訂單不被部分刪除，最後的commit語句不僅在不出錯時寫出更改，如果第一條delete起作用，但第二條失敗，則delete不會提交(實際上它被自動撤銷)

當commit或rollback執行後，事物會自動關閉，將來的更改會隱含提交

(3) 使用保留點

簡單的rollback和commit語句可以寫入或撤銷整個事物處理，但只能處理簡單的事物處理，更復雜的事物處理可能需要部分提交或回退

為了支援部分提交或回退，必須能在事物處理塊中找到合適的位置放置佔位符，這樣當需要回退時，可以回退到某個佔位符，這些佔位符稱為保留點

建立保留點，可使用：

savepoint delete1;

每個保留點都有它的唯一名字，以便在回退時，MySQL知道要回退到何處

rollback delete1；

保留點越多越好，因為保留點越多，就能越靈活地進行回退

保留點在事物處理完成後自動釋放

(4) 更改預設的提交行為

預設的MySQL行為是自動提交所有更改，即任何時候執行一條MySQL語句，該語句實際上都是針對表執行的，而且所做的更改立刻生效，為指示MySQL不自動提交，需要使用：

set autocommit = 0；

autocommit標誌決定是否自動提交更改，不管有沒有commit語句。設定autocommit為0(假)指示MySQL不自動提交更改，直到autocommit被設定為1

第二十七章 全球化和本地化

1 字符集和校對順序

資料庫被用來儲存和檢索資料，不同的語言和字符集需要以不同的方式儲存和檢索，因此MySQL需要適應不同的字符集，適應不同的排序和檢索資料的方法

幾個重要術語：
1，字符集：為字母和符號的集合
2，編碼：為某個字符集成員的內部表示
3，校對：為規定字元如何比較的指令

在MySQL的正常資料庫活動(select，insert等)中，不需要操心太多。使用何種字符集和校對的決定在伺服器，資料庫和表級進行

2 使用字符集和校對順序

MySQL支援眾多的字符集，為檢視所支援的字符集完整列表

show character set;

這條語句顯示所有可用的字符集以及每個字符集的描述和預設校對

為了檢視所支援的完整校對列表

show collation;

該語句顯示所有可用校對，以及它們適用的字符集

通常系統管理在安裝時定義一個預設字符集和校對。此處，也可以在建立資料庫時，指定預設的字符集和校對

為了確認所用的字符集和校對，可以使用：

show variables like 'character%';
show variables like 'collation%';

實際上字符集很少是伺服器或資料庫範圍的設定。不同的表，甚至不同的列都可能需要不同的字符集，而且兩者都可以在建立表時指定

為了給表指定字符集和校對，可以使用帶子句的create table

create table mytable
(
	columnn1 int,
	columnn2 varchar(10)
) default character set hebrew
collate hebrew_general_ci;

此語句建立了一個包含兩個列的表，並且指定一個字符集和一個校對順序，上述的例子指定了character set 和collate兩者，一般MySQL如下確定使用什麼樣的字符集和校對
1，如果指定了character set和collate兩者，則使用這些值
2，如果只指定character set，則使用此字符集及其預設的校對
3，如果不指定character，也不指定collate，則使用資料庫預設

除了能指定字符集和校對的表範圍外，MySQL還允許對每個列設定它們：

create table mytable
(
	columnn1 int,
	columnn2 varchar(10),
	columnn3 varchar(10) character set latin1 
		collate latin1_general_ci;
) default character set hebrew
collate hebrew_general_ci;

這裡對整個表以及一個特定的列指定了character set 和collate

校對在對用order by子句檢索出來的資料排序時起重要作用，如果需要用與建立表不同的校對順序排序特定的select語句時，可以在select語句自身中進行

select * 
from customers
order by lastname, firstname collate iatin1_general_cs;

此select語句使用collate指定一個備用的校對順序

可以使用cast()或convert()函式進行串和字符集之間的轉換

第二十八章 安全管理

1 訪問控制

MySQL伺服器的安全基礎是：使用者應該對他們需要的資料具有適當的訪問權，既不能多也不能少，即使用者不能對過多的資料具有過多的訪問權

考慮以下情況：
1，多數使用者只需要對錶進行讀寫，但少數使用者需要建立刪除表
2，某些使用者需要讀表，但可能不需要更新表
3，允許某些使用者新增資料，但不允許他們刪除資料
4，管理員可能需要處理使用者賬號的許可權
5，根據使用者登入的地點限制某些功能的訪問

通過上述的思考，即需要給使用者提供他們所需的訪問權，且僅提供他們所需的訪問權，這就是訪問控制，管理訪問控制需要建立和管理使用者賬號

訪問控制的目的不僅僅是防止使用者的惡意企圖，通過保證使用者不能執行他們不該執行的語句，訪問控制有助於避免操作不當導致的資料庫混亂

迄今為止對資料庫的操作都是提高root進行的，但是在日常工作中，絕不能使用root，應該建立一系列的賬號，有的使用者管理，有的供使用者使用，有的供開發維護人員使用等等

2 管理使用者

MySQL使用者賬號和資訊儲存在名為mysql的MySQL資料庫中，一般不需要直接訪問mysql資料庫和表，但有時需要直接訪問

直接訪問的時機之一是需要獲得所有使用者賬號列表：

mysql資料庫中有一個名為user的表，它包含所有的使用者賬號，user表中有一個名為user的列，它儲存使用者登入名

(1) 建立使用者賬號

可以使用create user語句建立一個新使用者賬號：

建立時需要給出新使用者名稱和口令，identified指定的口令為純文字，MySQL將在儲存到user表之前對其加密

除了使用create user外，還可以使用grant語句建立新使用者，或直接插入行到user中來新增新使用者

為了重新命名一個使用者賬號，可使用 rename user語句

(2) 刪除使用者賬號

刪除一個使用者賬號，可使用drop user語句：

(3) 設定訪問許可權

在建立使用者賬號後，必須接著分配訪問許可權，新建立的使用者賬號沒有訪問許可權，他們能登入MySQL，但不能看到資料，不能執行任何資料庫操作

為了看到賦予使用者賬號的許可權，可使用show grant for語句：

輸出結果顯示有一個許可權 usage on，usage表示根本沒有許可權，此結果表示在任意資料庫和任意表上對任何東西沒有許可權

MySQL的許可權使用者名稱和主機名結合定義，如果不使用主機名，則使用預設的主機名

為了設定使用者許可權，使用grant語句需要注意:
1，要授予的許可權
2，被授予訪問許可權的資料庫或表
3，使用者名稱

此grant語句允許使用者在crashcourse資料庫的所有表上使用select，使用者由此得到了crashcourse資料庫中所有表的只讀訪問許可權

每個grant新增或更新使用者的一個許可權，MySQL讀取所有的授權，並根據它們確定許可權

grant的反操作是revokr，用它來撤銷特定的許可權：

使用revoke時，被撤銷的許可權必須存在，否則會出錯

grant和revoke可在幾個層次上控制訪問許可權：
1，整個伺服器，使用grant all和revoke all;
2，整個資料庫，使用on database.*;
3，特定的表，使用on database.table;
4，特定的行
5，特定的儲存過程

使用grant和revoke可以對使用者對資料庫的訪問做出有效的控制

(4) 更改口令

更改使用者口令，可以使用set password語句：

set password還可以用來設定自己的口令，在不指定使用者名稱時，set password更新當前登入使用者的口令

set password = password('123456');