今天繼續(xù)學(xué)習(xí)《MySQL 必知必會》畜埋，今天是23章-30章的內(nèi)容。

Ch 23.使用存儲過程

所謂的存儲過程畴蒲，就是在需要針對多個表進行多條SQL語句處理的復(fù)雜場合悠鞍，將多條SQL語句保存成的一個集合。存儲過程相當(dāng)于一個SQL的集合函數(shù)模燥，封裝了若干條SQL語句咖祭。

1.存儲過程的優(yōu)缺點：

存儲過程主要有以下優(yōu)點：

• 簡單

  • 通過把處理封裝在容易使用的單元中，簡化復(fù)雜的操作蔫骂。
  • 由于不要求反復(fù)建立一系列處理步驟么翰，這保證了數(shù)據(jù)的完整性。

• 安全

  簡化對變動的管理辽旋。如果表名浩嫌、列名或業(yè)務(wù)邏輯（或別的內(nèi)容）有變化，只需要更改存儲過程的代碼戴已。使用它的人員甚至不需要知道這些變化固该。通過存儲過程可以限制對基礎(chǔ)數(shù)據(jù)的訪問，減少了數(shù)據(jù)訛傳糖儡。

• 高性能

  • 提高性能伐坏。因為使用存儲過程比使用單獨的SQL語句要快。
  • 存在一些只能用在單個請求中的MySQL元素和特性握联，存儲過程可以使用它們來編寫功能更強更靈活的代碼桦沉。

存儲過程的缺點也很明顯：編寫存儲過程要比編寫基本的SQL語句復(fù)雜很多；存儲過程也往往存在一定的限制金闽，比如只允許用戶使用存儲過程而不允許創(chuàng)建纯露。

2.使用存儲過程

調(diào)用存儲過程

MySQL 調(diào)用執(zhí)行過程使用的是 CALL關(guān)鍵字，如：

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);                    
#CALL 存儲過程名（參數(shù)列表）;

創(chuàng)建存儲過程

以下創(chuàng)建了一個簡單的無參的存儲過程：

CREATE PROCEDURE productpricing()
BEGIN
   SELECT Avg(prod_price) AS priceaverage
   FROM products;
END;

#CREATE PROCEDURE (參數(shù)列表)
#BEGIN
#   SQL 語句
#END;

注意：如果使用的是命令行版的MySQL代芜，存儲過程體中的SQL語句中的;會被作為分隔符使用埠褪，造成錯誤。解決措施是臨時臨時更改命令行版MySQL的語句分隔符：

#將分隔符替換為//
DELIMITER //

  CREATE PROCEDURE productpricing()
  BEGIN
     SELECT Avg(prod_price) AS priceaverage
     FROM products;
  END //

  DELIMITER ;
#將分隔符改回;

除了\符號外挤庇，其他的符號都可以作為命令行 MySQL 的分隔符钞速。

調(diào)用上述存儲過程：

CALL productpricing();

刪除存儲過程

刪除上述存儲過程：

DROP PROCEDURE productpricing；
#存儲過程名后面不需要帶()
#僅存在時刪除：DROP PROCEDURE IF EXISTS

使用參數(shù)

使用存儲過程時嫡秕，常常將結(jié)果存儲到某個變量里面渴语，因此需要在存儲過程中使用參數(shù)傳遞變量：

CREATE PROCEDURE productpricing(
   OUT pl DECIMAL(8,2),
   OUT ph DECIMAL(8,2),
   OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT Min(prod_price)
   INTO pl
   FROM products;
   SELECT Max(prod_price)
   INTO ph
   FROM products;
   SELECT Avg(prod_price)
   INTO pa
   FROM products;
END;

此存儲過程接受3個參數(shù)：pl存儲產(chǎn)品最低價格，ph存儲產(chǎn)品最高價格昆咽，pa存儲產(chǎn)品平均價格驾凶。每個參數(shù)必須具有指定的類型牙甫，這里使用十進制值。關(guān)鍵字OUT指出相應(yīng)的參數(shù)用來從存儲過程傳出一個值（返回給調(diào)用者）调违。MySQL支持IN（傳遞給存儲過程）窟哺、OUT（從存儲過程傳出，如這里所用）和INOUT（對存儲過程傳入和傳出）類型的參數(shù)翰萨。存儲過程的代碼位于BEGIN和END語句內(nèi)脏答，如前所見糕殉，它們是一系列SELECT語句亩鬼，用來檢索值，然后保存到相應(yīng)的變量（通過指定INTO關(guān)鍵字）阿蝶。

注意：使用變量時雳锋，尤其是對變量進行賦值時需要注意變量的數(shù)據(jù)類型。

調(diào)用上述存儲過程羡洁，并輸出結(jié)果：

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);
SELECT @priceaverage;                    

注意：所有MySQL變量都需要使用@玷过。

以下是一個使用 IN 和 OUT 變量的存儲過程：

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   OUT ototal DECIMAL(8,2)
)
BEGIN
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO ototal;
END;

調(diào)用上述存儲過程并輸出結(jié)果：

CALL ordertotal(20005,@total);
SELECT @total;

建立智能存儲過程

以下是聲明了一個復(fù)雜存儲過程：

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN

   -- Declare variable for total
   DECLARE total DECIMAL(8,2);
   -- Declare tax percentage
   DECLARE taxrate INT DEFAULT 6;

   -- Get the order total
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO total;

   -- Is this taxable?
   IF taxable THEN
      -- Yes, so add taxrate to the total
      SELECT total+(total/100*taxrate) INTO total;
   END IF;

   -- And finally, save to out variable
   SELECT total INTO ototal;

END;

注意以下幾點：

• 增加了注釋 --。在編寫復(fù)雜的存儲過程時筑煮，添加一定的注視非常重要辛蚊。
• 添加了另外一個參數(shù)taxable，它是一個布爾值真仲，傳入變量為0或1袋马。
• 用DECLARE語句定義了兩個局部變量。
• IF語句檢查taxable是否為真秸应，如果為真虑凛，則用另一SELECT語句增加營業(yè)稅到局部變量total。最后软啼，用另一SELECT語句將total（它增加或許不增加營業(yè)稅）保存到ototal桑谍。
• 添加了COMMENT輸出信息，相當(dāng)于日志的作用祸挪，其信息將在SHOW PROCEDURE STATUS的結(jié)果中顯示锣披。

調(diào)用上述存儲過程，并輸出結(jié)果：

CALL ordertotal(20005, 0, @total);
SELECT @total;

檢查存儲過程

查看存儲過程的創(chuàng)建語句：

SHOW CREATE PROCEDURE ordertotal;

輸出存儲過程的詳細(xì)信息：

SHOW PROCEDURE STATUS;

以上語句會輸出全部的存儲過程的信息贿条，可以通過以下方式進行過濾：

SHOW PROCEDURE STATUS LIKE 'ordertotal';

Ch 24.使用游標(biāo)

SQL 檢索語句返回的結(jié)果稱為結(jié)果集雹仿，之前的操作無法完成這樣的操作：獲取下一行，獲取前十行等等闪唆。這就要使用游標(biāo)了盅粪。游標(biāo)主要用于交互式應(yīng)用，其中用戶需要滾動屏幕上的數(shù)據(jù)悄蕾，并對數(shù)據(jù)進行瀏覽或做出更改票顾。不同于其他DBMS础浮，MySQL的游標(biāo)只能用于存儲過程。

1.使用游標(biāo)

使用游標(biāo)涉及幾個明確的步驟：

• 在能夠使用游標(biāo)前奠骄，必須聲明（定義）它豆同。這個過程實際上沒有檢索數(shù)據(jù)，它只是定義要使用的SELECT語句含鳞。
• 一旦聲明后影锈，必須打開游標(biāo)以供使用。這個過程用前面定義的SELECT語句把數(shù)據(jù)實際檢索出來蝉绷。
• 對于填有數(shù)據(jù)的游標(biāo)鸭廷，根據(jù)需要取出（檢索）各行。
• 在結(jié)束游標(biāo)使用時熔吗，必須關(guān)閉游標(biāo)辆床。

在聲明游標(biāo)后，可根據(jù)需要頻繁地打開和關(guān)閉游標(biāo)桅狠。在游標(biāo)打開后讼载，可根據(jù)需要頻繁地執(zhí)行取操作。

定義游標(biāo)

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT ordernum FROM orders;
END;

游標(biāo)的命名方式為DECLARE 游標(biāo)名 CURSOR中跌，需要注意的是咨堤，存儲過程執(zhí)行完畢后游標(biāo)就消失。

打開和關(guān)閉游標(biāo)

#打開游標(biāo)
OPEN ordernumbers;

#關(guān)閉游標(biāo)
CLOSE ordernumbers;

注意：只有打開游標(biāo)后才能使用它漩符，在游標(biāo)使用完畢后需要關(guān)閉它以釋放資源一喘。即使不手動關(guān)閉游標(biāo)，當(dāng)存儲過程執(zhí)行完畢游標(biāo)也會被 MySQL 關(guān)閉陨仅。

CREATE PROCEDURE processorders()
BEGIN
   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Open the cursor
   OPEN ordernumbers;

   -- Close the cursor
   CLOSE ordernumbers;

END;

使用游標(biāo)數(shù)據(jù)

在一個游標(biāo)被打開后津滞，可以使用FETCH語句分別訪問它的每一行。FETCH指定檢索什么數(shù)據(jù)（所需的列）灼伤，檢索出來的數(shù)據(jù)存儲在什么地方触徐。它還向前移動游標(biāo)中的內(nèi)部行指針，使下一條FETCH語句檢索下一行（不重復(fù)讀取同一行）狐赡。

第一個例子是利用FETCH檢索第一行

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE o INT;

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Open the cursor
   OPEN ordernumbers;

   -- Get order number
   --將檢索出的數(shù)據(jù)存放在局部變量o中
   FETCH ordernumbers INTO o;

   -- Close the cursor
   CLOSE ordernumbers;

END;

下一個例子是循環(huán)檢索數(shù)據(jù)撞鹉，從第一行到最后一行

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Declare continue handler
   -- SQLSTATE '02000'指的是未找到的錯誤，強行類比的話像是NullPointerException
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

注意：DECLARE語句的發(fā)布存在特定的次序颖侄。用DECLARE語句定義的局部變量必須在定義任意游標(biāo)或句柄之前定義鸟雏，而句柄必須在游標(biāo)之后定義。不遵守此順序?qū)a(chǎn)生錯誤消息览祖。
接下來是一個復(fù)雜的例子孝鹊，其中ordertotal是上一章定義的一個計算稅率的存儲過程：

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   DECLARE t DECIMAL(8,2);

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

   -- Create a table to store the results
   CREATE TABLE IF NOT EXISTS ordertotals
      (order_num INT, total DECIMAL(8,2));

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;

      -- Get the total for this order
      CALL ordertotal(o, 1, t);

      -- Insert order and total into ordertotals
      INSERT INTO ordertotals(order_num, total)
      VALUES(o, t);

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

在這個存儲過程中，將檢索出來的數(shù)據(jù)存儲到新表中展蒂。

Ch 25.使用觸發(fā)器

如果想使得某些語句在一些事件發(fā)生后自動執(zhí)行又活，如每當(dāng)訂購一個產(chǎn)品時苔咪，都從庫存數(shù)量中減去訂購的數(shù)量；無論何時刪除一行柳骄，都在某個存檔表中保留一個副本团赏。想要完成這樣的操作，就要使用觸發(fā)器耐薯。

觸發(fā)器是MySQL響應(yīng)以下任意語句而自動執(zhí)行的一組MySQL語句：

• DELETE舔清；
• INSERT；
• UPDATE曲初。

其他MySQL語句不支持觸發(fā)器体谒。

1.創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器需要提供以下信息：

• 唯一的觸發(fā)器名；
• 觸發(fā)器關(guān)聯(lián)的表复斥；
• 觸發(fā)器應(yīng)該響應(yīng)的活動（DELETE营密、INSERT或UPDATE）；
• 觸發(fā)器何時執(zhí)行（處理之前或之后）目锭。

需要注意的是，最好是保持每個數(shù)據(jù)庫的觸發(fā)器名唯一纷捞。另外痢虹，只有表支持觸發(fā)器，視圖不支持主儡、臨時表也不支持奖唯。

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

上述語句對 products 定義了一個觸發(fā)器，每當(dāng)對該表進行插入操作后糜值，對每個插入行顯示“Product added”消息丰捷。

MySQL 觸發(fā)器按每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發(fā)器寂汇。因此病往，每個表最多支持6個觸發(fā)器，即INSERT骄瓣、DELETE停巷、UPDATE的執(zhí)行前和執(zhí)行后。

注意：在MySQL中榕栏，如果BEFORE觸發(fā)器失敗畔勤，則不會執(zhí)行請求的操作；如果BEFORE觸發(fā)器失敗或者語句本身失敗扒磁，也不會執(zhí)行AFTER觸發(fā)器操作庆揪。

2.刪除觸發(fā)器

DROP TRIGGER newproduct;

3.使用觸發(fā)器

INSERT觸發(fā)器

INSERT觸發(fā)器在INSERT語句執(zhí)行之前或之后執(zhí)行。需要知道以下幾點：

• 在INSERT觸發(fā)器代碼內(nèi)妨托，可引用一個名為NEW的虛擬表缸榛，訪問被插入的行检访；
• 在BEFORE INSERT觸發(fā)器中，NEW中的值也可以被更新（允許更改被插入的值）仔掸；
• 對于AUTO_INCREMENT列脆贵，NEW在INSERT執(zhí)行之前包含0，在INSERT執(zhí)行之后包含新的自動生成值起暮。

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

上述語句定義了一個 neworder 的觸發(fā)器卖氨，在對orders表進行插入操作后對于每個插入行顯示該新插入記錄的order_num屬性值。

#運行如下語句
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);

#可得到如下輸出
+-----------+
| order_num |
+-----------+
|     20010 |
+-----------+

注意：BEFORE觸發(fā)器多用于數(shù)據(jù)的驗證和凈化负懦，而AFTER觸發(fā)器多用于顯示提示信息筒捺。

DELETE觸發(fā)器

DELETE觸發(fā)器在DELETE語句執(zhí)行之前或之后執(zhí)行。需要知道以下兩點：

• 在DELETE觸發(fā)器代碼內(nèi)纸厉，你可以引用一個名為OLD的虛擬表系吭，訪問被刪除的行；
• OLD中的值全都是只讀的颗品，不能更新肯尺。

下面的例子演示使用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;
#觸發(fā)器體可以使用BEGIN和END包裹多條SQL語句。

根據(jù)前文所提到的躯枢，如果BEFORE觸發(fā)器執(zhí)行失敗则吟，則說明存檔失敗，因此不會執(zhí)行刪除操作锄蹂。

UPDATE觸發(fā)器

UPDATE觸發(fā)器在UPDATE語句執(zhí)行之前或之后執(zhí)行氓仲。需要知道以下幾點：

• 在UPDATE觸發(fā)器代碼中，你可以引用一個名為OLD的虛擬表訪問以前（UPDATE語句前）的值得糜，引用一個名為NEW的虛擬表訪問新更新的值敬扛；
• 在BEFORE UPDATE觸發(fā)器中，NEW中的值可能也被更新（允許更改將要用于UPDATE語句中的值）朝抖；
• OLD中的值全都是只讀的啥箭，不能更新。

下面的例子保證州名縮寫總是大寫（不管UPDATE語句中給出的是大寫還是小寫）：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

注意事項

以下是原書中所寫的觸發(fā)器注意事項：

• 與其他DBMS相比槽棍，MySQL5中支持的觸發(fā)器相當(dāng)初級捉蚤。未來的MySQL版本中有一些改進和增強觸發(fā)器支持的計劃。
• 創(chuàng)建觸發(fā)器可能需要特殊的安全訪問權(quán)限炼七，但是缆巧，觸發(fā)器的執(zhí)行是自動的。如果INSERT豌拙、UPDATE或DELETE語句能夠執(zhí)行陕悬，則相關(guān)的觸發(fā)器也能執(zhí)行。
• 應(yīng)該用觸發(fā)器來保證數(shù)據(jù)的一致性（大小寫按傅、格式等）捉超。在觸發(fā)器中執(zhí)行這種類型的處理的優(yōu)點是它總是進行這種處理胧卤，而且是透明地進行，與客戶機應(yīng)用無關(guān)拼岳。
• 觸發(fā)器的一種非常有意義的使用是創(chuàng)建審計跟蹤枝誊。使用觸發(fā)器，把更改（如果需要惜纸，甚至還有之前和之后的狀態(tài)）記錄到另一個表非常容易叶撒。
• 遺憾的是，MySQL觸發(fā)器中不支持CALL語句耐版。這表示不能從觸發(fā)器內(nèi)調(diào)用存儲過程祠够。所需的存儲過程代碼需要復(fù)制到觸發(fā)器內(nèi)。

Ch 26.管理事務(wù)處理

正如前文所講的粪牲，并非所有的數(shù)據(jù)庫引擎都支持事務(wù)處理古瓤，MySQL最常用的引擎中，InnoDB支持事務(wù)而MyISAM不支持腺阳。

事務(wù)處理（transactionprocessing）可以用來維護數(shù)據(jù)庫的完整性落君，它保證成批的MySQL操作要么完全執(zhí)行，要么完全不執(zhí)行舌狗，以保證數(shù)據(jù)庫不包含不完整的操作結(jié)果叽奥。如果沒有錯誤發(fā)生，整組語句提交給（寫到）數(shù)據(jù)庫表痛侍。如果發(fā)生錯誤魔市，則進行回退（撤銷）以恢復(fù)數(shù)據(jù)庫到某個已知且安全的狀態(tài)主届。

首先定義幾個概念：

• 事務(wù)（transaction）指一組SQL語句君丁；
• 回退（rollback）指撤銷指定SQL語句的過程；
• 提交（commit）指將未存儲的SQL語句結(jié)果寫入數(shù)據(jù)庫表将宪；
• 保留點（savepoint）指事務(wù)處理中設(shè)置的臨時占位符（place-holder）绘闷，你可以對它發(fā)布回退（與回退整個事務(wù)處理不同）。

1.控制事務(wù)處理

管理事務(wù)處理的關(guān)鍵在于將SQL語句組分解為邏輯塊较坛，并明確規(guī)定數(shù)據(jù)何時應(yīng)該回退印蔗，何時不應(yīng)該回退。
MySQL使用下面的語句來標(biāo)識事務(wù)的開始：

START TRANSACTION

使用ROLLBACK

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

上述語句開啟事務(wù)后將ordertotals表中的記錄全部刪除丑勤，然后回滾事務(wù)华嘹，將數(shù)據(jù)庫狀態(tài)恢復(fù)至剛剛開啟事務(wù)時。

注意：只能回退INSERT法竞、UPDATE耙厚、DELETE語句强挫，SELECT語句回退沒有意義。不能回退CREATE和DROP薛躬，雖然可以在事務(wù)處理塊中使用這兩種語句锋拖，但是執(zhí)行回退他們不會被撤銷。

使用COMMIT

一般的MySQL語句都是直接針對數(shù)據(jù)庫表執(zhí)行和編寫的妨猩。這就是所謂的隱含提交（implicitcommit）捎泻，即提交（寫或保存）操作是自動進行的。但是诡曙，在事務(wù)處理塊中臀叙，提交不會隱含地進行。為進行明確的提交价卤，使用COMMIT語句劝萤。

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

最后的COMMIT語句僅在不出錯時寫出更改。如果第一條DELETE起作用慎璧，但第二條失敗床嫌，則DELETE不會提交（實際上，它是被自動撤銷的）胸私。

注意：當(dāng)COMMIT或ROLLBACK語句執(zhí)行后厌处，事務(wù)會自動關(guān)閉（將來的更改會隱含提交）。

使用保留點

更復(fù)雜的事務(wù)處理可能需要部分提交或回退岁疼，為了支持回退部分事務(wù)處理阔涉，必須能在事務(wù)處理塊中合適的位置放置占位符。這樣捷绒，如果需要回退瑰排，可以回退到某個占位符。這些占位符稱為保留點暖侨。為了創(chuàng)建占位符椭住，可如下使用SAVEPOINT語句：

SAVEPOINT delete1;

#回退到保留點
ROLLBACK TO delete1;

注意：在進行復(fù)雜的事務(wù)操作時，可以盡可能多地設(shè)置保留點字逗，這樣可以靈活回退京郑。保留點在事務(wù)完成后會自動釋放，但也可以使用RELEASE SAVEPOINT明確地釋放保留點葫掉。

2.更改默認(rèn)的自動提交

默認(rèn)的MySQL行為是自動提交所有更改些举。換句話說，任何時候你執(zhí)行一條MySQL語句挖息，該語句實際上都是針對表執(zhí)行的金拒，而且所做的更改立即生效。為指示MySQL不自動提交更改，需要使用以下語句：

SET autocommit=0;

注意：autocommit標(biāo)志是針對每個連接而不是服務(wù)器的绪抛。

Ch 27.全球化和本地化

1.使用字符集和校正順序

查看MySQL所支持字符集的完整列表：

SHOW CHARACTER SET;

查看所支持的校正順序的完整列表：

SHOW COLLATION;

確定所使用的字符集和校正順序：

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

在創(chuàng)建表時指定字符集和校正順序：

CREATE TABLE mytable
(
   columnn1   INT,
   columnn2   VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

一般资铡，MySQL如下確定使用什么樣的字符集和校對:

• 如果指定CHARACTER SET和COLLATE兩者，則使用這些值幢码。
• 如果只指定CHARACTER SET笤休，則使用此字符集及其默認(rèn)的校對（如SHOW CHARACTER SET的結(jié)果中所示）。
• 如果既不指定CHARACTER SET症副，也不指定COLLATE店雅，則使用數(shù)據(jù)庫默認(rèn)。

MySQL還允許對每個列設(shè)置字符集和校正順序：

CREATE TABLE mytable
(
   columnn1   INT,
   columnn2   VARCHAR(10),
   column3    VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;

校對在對用ORDER BY子句檢索出來的數(shù)據(jù)排序時起重要的作用贞铣。如果你需要用與創(chuàng)建表時不同的校對順序排序特定的SELECT語句闹啦，可以在SELECT語句自身中進行：

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

Ch 28.安全管理

1.訪問控制

MySQL服務(wù)器的安全基礎(chǔ)是：用戶應(yīng)該對他們需要的數(shù)據(jù)具有適當(dāng)?shù)脑L問權(quán)，既不能多也不能少辕坝。

MySQL默認(rèn)數(shù)據(jù)庫的用戶root擁有對數(shù)據(jù)庫的絕對管理權(quán)限窍奋，應(yīng)該嚴(yán)肅對待root登錄的使用。僅在絕對需要時使用它（或許在你不能登錄其他管理賬號時使用）酱畅。不應(yīng)該在日常的MySQL操作中使用root琳袄。

2.管理用戶

MySQL用戶賬號和信息存儲在名為mysql的MySQL數(shù)據(jù)庫中》乃幔可以在其中的user表中查詢到所有的用戶：

USE mysql;
SELECT user FROM user;

創(chuàng)建用戶

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

重命名用戶

RENAME USER ben TO bforta;

刪除用戶

DROP USER bforta;

設(shè)置訪問權(quán)限

新創(chuàng)建的用戶沒有任何權(quán)限窖逗，只能登錄服務(wù)器，不能操作任何表和數(shù)據(jù)庫餐蔬。

查看權(quán)限：

SHOW GRANTS FOR bforta;

#輸出為
+-------------------------------------------------+
| Grants for bforta@%                             |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'              |
+-------------------------------------------------+

ON*.*表示在任意數(shù)據(jù)庫和任意表上對任何東西沒有權(quán)限碎紊。

另外，“MySQL的權(quán)限用用戶名和主機名結(jié)合定義樊诺，即user@host矮慕。如果不指定主機名，則使用默認(rèn)的主機名%（授予用戶訪問權(quán)限而不管主機名）啄骇。

可以使用 GRANT 語句授予用戶權(quán)限，

• 要授予的權(quán)限瘟斜；
• 被授予訪問權(quán)限的數(shù)據(jù)庫或表缸夹；
• 用戶名。

以下例子給出GRANT的用法：

GRANT SELECT ON crashcourse.* TO beforta;

這條語句授予beforta用戶對crashcourse數(shù)據(jù)庫中所有的表上使用SELECT語句的權(quán)限螺句。

可以使用REVOKE語句來撤銷GRANT授予的權(quán)限：

REVOKE SELECT ON crashcourse.* FROM beforta

GRANT和REVOKE可在幾個層次上控制訪問權(quán)限：

• 整個服務(wù)器虽惭，使用GRANT ALL和REVOKE ALL；
• 整個數(shù)據(jù)庫蛇尚，使用ON database.*芽唇；
• 特定的表，使用ON database.table；
• 特定的列匆笤；
• 特定的存儲過程研侣。

以下是具體權(quán)限內(nèi)容：

WX20200620-145326@2x.png

可通過列出各權(quán)限并用逗號分隔，將多條GRANT語句串在一起炮捧，如下所示：

GRANT SELECT, INSERT ON crashcourse.* TO beforta;

更改口令

可以使用下列語句更改口令：

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

新口令必須傳遞到Password()函數(shù)進行加密庶诡。

Ch 29.數(shù)據(jù)庫維護

1.備份數(shù)據(jù)

對數(shù)據(jù)庫進行備份主要有以下幾種方式：

• 在MySQL命令行使用mysqldump將所有數(shù)據(jù)庫內(nèi)容存儲到某個外部文件中；
• 在MySQL命令行使用mysqlhotcopy從一個數(shù)據(jù)庫復(fù)制所有數(shù)據(jù)（并非所有的數(shù)據(jù)庫引擎都支持）咆课；
• 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE轉(zhuǎn)儲所有數(shù)據(jù)到某個外部文件末誓。數(shù)據(jù)可以用RESTORE TABLE來復(fù)原。

注意：為了保證所有數(shù)據(jù)被寫到磁盤（包括索引數(shù)據(jù)）书蚪，可能需要在進行備份前使用FLUSH TABLES語句喇澡。

2.進行數(shù)據(jù)庫維護

應(yīng)該知道以下語句：

ANALYZE TABLE，用來檢查表鍵是否正確殊校。ANALYZE TABLE返回如下所示的狀態(tài)信息：

ANALYZE TABLE orders;

CHECK TABLE用來針對許多問題對表進行檢查晴玖。CHECK TABLE支持一系列的用于MyISAM引擎表的方式。CHANGED檢查自最后一次檢查以來改動過的表箩艺。EXTENDED執(zhí)行最徹底的檢查窜醉，F(xiàn)AST只檢查未正常關(guān)閉的表，MEDIUM檢查所有被刪除的鏈接并進行鍵檢驗艺谆，QUICK只進行快速掃描榨惰。如下所示，CHECK TABLE發(fā)現(xiàn)和修復(fù)問題：

CHECK TABLE orders, orderitems;

3.診斷啟動問題

在排除系統(tǒng)啟動問題時静汤，首先應(yīng)該盡量用手動啟動服務(wù)器琅催。MySQL服務(wù)器自身通過在命令行上執(zhí)行mysqld啟動。下面是幾個重要的mysqld命令行選項：

• --help顯示幫助——一個選項列表虫给；
• --safe-mode裝載減去某些最佳配置的服務(wù)器藤抡；
• --verbose顯示全文本消息（為獲得更詳細(xì)的幫助消息與--help聯(lián)合使用）；
• --version顯示版本信息然后退出抹估。

4.查看日志文件

MySQL維護管理員依賴的一系列日志文件缠黍。主要的日志文件有以下幾種：

• 錯誤日志。它包含啟動和關(guān)閉問題以及任意關(guān)鍵錯誤的細(xì)節(jié)药蜻。此日志通常名為hostname.err瓷式，位于data目錄中。此日志名可用--log-error命令行選項更改语泽。
• 查詢?nèi)罩久车洹Ｋ涗浰蠱ySQL活動，在診斷問題時非常有用踱卵。此日志文件可能會很快地變得非常大廊驼，因此不應(yīng)該長期使用它。此日志通常名為hostname.log，位于data目錄中妒挎。此名字可以用--log命令行選項更改绳锅。
• 二進制日志。它記錄更新過數(shù)據(jù)（或者可能更新過數(shù)據(jù)）的所有語句饥漫。此日志通常名為hostname-bin榨呆，位于data目錄內(nèi)。此名字可以用--log-bin命令行選項更改庸队。注意积蜻，這個日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志彻消。
• 緩慢查詢?nèi)罩靖筒稹ｎ櫭剂x，此日志記錄執(zhí)行緩慢的任何查詢宾尚。這個日志在確定數(shù)據(jù)庫何處需要優(yōu)化很有用丙笋。此日志通常名為hostname-slow.log，位于data目錄中煌贴。此名字可以用--log-slow-queries命令行選項更改御板。

Ch 30.改善性能

數(shù)據(jù)庫管理員把他們生命中的相當(dāng)一部份時間花在了調(diào)整、試驗以改善DBMS性能之上牛郑。在診斷應(yīng)用的滯緩現(xiàn)象和性能問題時怠肋，性能不良的數(shù)據(jù)庫（以及數(shù)據(jù)庫查詢）通常是最常見的禍因。

回顧之前的章節(jié)淹朋，總結(jié)了一下的幾個性能Tips：

• 首先笙各，MySQL（與所有DBMS一樣）具有特定的硬件建議。在學(xué)習(xí)和研究MySQL時础芍，使用任何舊的計算機作為服務(wù)器都可以杈抢。但對用于生產(chǎn)的服務(wù)器來說，應(yīng)該堅持遵循這些硬件建議仑性。
• 一般來說惶楼，關(guān)鍵的生產(chǎn)DBMS應(yīng)該運行在自己的專用服務(wù)器上。
• MySQL是用一系列的默認(rèn)設(shè)置預(yù)先配置的诊杆，從這些設(shè)置開始通常是很好的鲫懒。但過一段時間后你可能需要調(diào)整內(nèi)存分配、緩沖區(qū)大小等刽辙。（為查看當(dāng)前設(shè)置，可使用SHOW VARIABLES;和SHOW STATUS;甲献。）
• MySQL是一個多用戶多線程的DBMS宰缤，換言之，它經(jīng)常同時執(zhí)行多個任務(wù)。如果這些任務(wù)中的某一個執(zhí)行緩慢慨灭，則所有請求都會執(zhí)行緩慢朦乏。如果你遇到顯著的性能不良，可使用SHOW PROCESS LIST顯示所有活動進程（以及它們的線程ID和執(zhí)行時間）氧骤。你還可以用KILL命令終結(jié)某個特定的進程（使用這個命令需要作為管理員登錄）呻疹。
• 總是有不止一種方法編寫同一條SELECT語句。應(yīng)該試驗聯(lián)結(jié)筹陵、并刽锤、子查詢等，找出最佳的方法朦佩。
• 使用EXPLAIN語句讓MySQL解釋它將如何執(zhí)行一條SELECT語句并思。
• 一般來說，存儲過程執(zhí)行得比一條一條地執(zhí)行其中的各條MySQL語句快语稠。
• 應(yīng)該總是使用正確的數(shù)據(jù)類型宋彼。
• 決不要檢索比需求還要多的數(shù)據(jù)。換言之仙畦，不要用SELECT*（除非你真正需要每個列）输涕。
• 有的操作（包括INSERT）支持一個可選的DELAYED關(guān)鍵字，如果使用它慨畸，將把控制立即返回給調(diào)用程序莱坎，并且一旦有可能就實際執(zhí)行該操作。
• 在導(dǎo)入數(shù)據(jù)時先口，應(yīng)該關(guān)閉自動提交型奥。你可能還想刪除索引（包括FULLTEXT索引），然后在導(dǎo)入完成后再重建它們碉京。
• 必須索引數(shù)據(jù)庫表以改善數(shù)據(jù)檢索的性能厢汹。確定索引什么不是一件微不足道的任務(wù)，需要分析使用的SELECT語句以找出重復(fù)的WHERE和ORDER BY子句谐宙。如果一個簡單的WHERE子句返回結(jié)果所花的時間太長烫葬，則可以斷定其中使用的列（或幾個列）就是需要索引的對象。
• 你的SELECT語句中有一系列復(fù)雜的OR條件嗎凡蜻？通過使用多條SELECT語句和連接它們的UNION語句搭综，你能看到極大的性能改進。
• 索引改善數(shù)據(jù)檢索的性能划栓，但損害數(shù)據(jù)插入兑巾、刪除和更新的性能。如果你有一些表忠荞，它們收集數(shù)據(jù)且不經(jīng)常被搜索蒋歌，則在有必要之前不要索引它們帅掘。（索引可根據(jù)需要添加和刪除。）
• LIKE很慢堂油。一般來說修档，最好是使用FULLTEXT而不是LIKE。
• 數(shù)據(jù)庫是不斷變化的實體府框。一組優(yōu)化良好的表一會兒后可能就面目全非了吱窝。由于表的使用和內(nèi)容的更改，理想的優(yōu)化和配置也會改變迫靖。
• 最重要的規(guī)則就是院峡，每條規(guī)則在某些條件下都會被打破。

以上差不多就是《MySQL 必知必會》這本書的全部內(nèi)容了袜香，這本書深入淺出對MySQL數(shù)據(jù)庫進行了一個整體的介紹撕予。但是我們的高性能SQL的計劃才剛剛開始。

未完待續(xù)······