MySQL 5.0 新特性

Introduction 簡介 MySQL 5.0 新特性教程是為需要了解5.0版本新特性的MySQL老用戶而寫的。簡單的來說是介紹了“存儲過程、觸發(fā)器、視圖、信息架構(gòu)視圖”,我打算每天一部分,來 連載這個教程.如果你想看一次看完P(guān)DF版本的教程,請到down.phpv.net下載.在此感謝譯者陳朋奕的努力. 希望這本書能像內(nèi)行專家那樣與您進(jìn)行對話，用簡單的問題、例子讓你學(xué)到需要的知識。為了達(dá)到這樣的目的，我會從每一個細(xì)節(jié)開始慢慢的為大家建立概念，最后會給大家展示較大的實用例，在學(xué)習(xí)之前也許大家會認(rèn)為這個用例很難，但是只要跟著課程去學(xué)，相信很快就能掌握。[@more@]MySQL 5.0 新特性教程 第一部分:第一講

Conventions and Styles 約定和編程風(fēng)格

每次我想要演示實際代碼時，我會對mysql客戶端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來與普通文本不一樣。
在這里舉個例子：mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)
如果實例比較大，則需要在某些行和段落間加注釋，同時我會用將“<--”符號放在頁面的右邊以表示強(qiáng)調(diào)。
例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//Query OK, 0 rows affected (0.00 sec)
有時候我會將例子中的"mysql>"和"->"這些系統(tǒng)顯示去 掉，你可以直接將代碼復(fù)制到mysql客戶端程序中（如果你現(xiàn)在所讀的 不是電子版的，可以在mysql.com網(wǎng)站下載相關(guān)腳本）所以的例子都已經(jīng)在Suse 9.2 Linux、Mysql 5.0.3公共版上測試通過。
在您閱讀本書的時候，Mysql已經(jīng)有更高的版本，同時能支持更多OS了，包括Windows，Sparc，HP-UX。因此這里的例子將能正常的運(yùn)行在您的電腦上。但如果運(yùn)行仍然出現(xiàn)故障，可以咨詢你認(rèn)識的資深Mysql用戶，以得到長久的支持和幫助。
A Definition and an Example 定義及實例
定義及實例存儲過程是一種存儲在書庫中的程序（就像正規(guī)語言里的子程序一樣）， 準(zhǔn)確的來說，MySQL支持的“routines（例程）”有兩種： 一是我們說的存儲過程，二是在其他SQL語句中可以返回值的函數(shù)（使用起來和Mysql預(yù)裝載的函數(shù)一樣，如pi()）。我在本書里面會更經(jīng)常使用存儲過 程，因為這是我們過去的習(xí)慣，相信大家也會接受。

一個存儲過程包括名字，參數(shù)列表，以及可以包括很多SQL語句的SQL語句集。
在這里對局部變量，異常處理，循環(huán)控制和IF條件句有新的語法定義。
下面是一個包括存儲過程的實例聲明：（譯注：為了方便閱讀，此后的程序不添任何中文注釋）

CREATE PROCEDURE procedure1 /* name存儲過程名*/

(IN parameter1 INTEGER) /* parameters參數(shù)*/

BEGIN /* start of block語句塊頭*/

DECLARE variable1 CHAR(10); /* variables變量聲明*/

IF parameter1 = 17 THEN /* start of IF IF條件開始*/

SET variable1 = 'birds'; /* assignment賦值*/

ELSE

SET variable1 = 'beasts'; /* assignment賦值*/

END IF; /* end of IF IF結(jié)束*/

INSERT INTO table1 VALUES (variable1);/* statement SQL語句*/

END /* end of block語句塊結(jié)束*/

下面我將會介紹你可以利用存儲過程做的工作的所有細(xì)節(jié)。同時我們將介紹新的數(shù)據(jù)庫對象—觸發(fā)器，因為觸發(fā)器和存儲過程的關(guān)聯(lián)是必然的。

Why Stored Procedures 為什么要用存儲過程
由于存儲過程對于MySQL來說是新的功能，很自然的在使用時你需要更加注意。
畢竟，在此之前沒有任何人使用過，也沒有很多大量的有經(jīng)驗的用戶來帶你走他們走過的路。然而你應(yīng)該開始考慮把現(xiàn)有程序（可能在服務(wù)器應(yīng)用程序中，用戶自定義函數(shù)（UDF）中，或是腳本中）轉(zhuǎn)移到存儲過程中來。這樣做不需要原因，你不得不去做。

因為存儲過程是已經(jīng)被認(rèn)證的技術(shù)！雖然在Mysql中它是新的，但是相同功能的函數(shù)在其他DBMS中早已存在，而它們的語法往是相同的。因此你可以從其他人那里獲得這些概念，也有很多你可以咨詢或者雇用的經(jīng)驗用戶，還有許多第三方的文檔可供你閱讀。

存 儲過程會使系統(tǒng)運(yùn)行更快！雖然我們暫時不能在Mysql上證明這個優(yōu)勢，用戶得到的體驗也不一樣。我們可以說的就是Mysql服務(wù)器在緩存機(jī)制上做了改 進(jìn)，就像Preparedstatements（預(yù)處理語句）所做的那樣。由于沒有編譯器，因此SQL存儲過程不會像外部語言（如C）編寫的程序運(yùn)行起來 那么快。但是提升速度的主要方法卻在于能否降低網(wǎng)絡(luò)信息流量。如果你需要處理的是需要檢查、循環(huán)、多語句但沒有用戶交互的重復(fù)性任務(wù)，你就可以使用保存在 服務(wù)器上的存儲過程來完成。這樣在執(zhí)行任務(wù)的每一步時服務(wù)器和客戶端之間就沒那么多的信息來往了。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

所 以存儲過程是可復(fù)用的組件！想象一下如果你改變了主機(jī)的語言，這對存儲過程不會產(chǎn)生影響，因為它是數(shù)據(jù)庫邏輯而不是應(yīng)用程序。存儲過程是可以移植的！當(dāng)你 用SQL編寫存儲過程時，你就知道它可以運(yùn)行在Mysql支持的任何平臺上，不需要你額外添加運(yùn)行環(huán)境包，也不需要為程序在操作系統(tǒng)中執(zhí)行設(shè)置許可，或者 為你的不同型號的電腦存儲過程將被保存！如果你編寫好了一個程序，例如顯示銀行事物處理中的支票撤消，那想要了解支票的人就可以找到你的程序。

它會以源代碼的形式保存在數(shù)據(jù)庫中。這將使數(shù)據(jù)和處理數(shù)據(jù)的進(jìn)程有意義的關(guān)聯(lián)這可能跟你在課上聽到的規(guī)劃論中說的一樣。存儲過程可以遷移！

Mysql完全支持SQL 2003標(biāo)準(zhǔn)。某些數(shù)據(jù)庫（如DB2、Mimer）同樣支持。但也有部分不支持的，如Oracle、SQL Server不支持。我們將會給予足夠幫助和工具，使為其他DBMS編寫的代碼能更容易轉(zhuǎn)移到Mysql上。
Setting up with MySQL 5.0 設(shè)置并開始MySQL 5.0服務(wù)

通過
mysql_fix_privilege_tables
或者
~/mysql-5.0/scripts/mysql_install_db
來開始MySQL服務(wù)
作為我們練習(xí)的準(zhǔn)備工作的一部分，我假定MySQL 5.0已經(jīng)安裝。如果沒有數(shù)據(jù)庫管理員為你安裝好數(shù)據(jù)庫以及其他軟件，你就需要自己去安裝了。不過你很容易忘掉一件事，那就是你需要有一個名為mysql.proc的表。
在安裝了最新版本后，你必須運(yùn)行
mysql_fix_privilege_tables
或者
mysql_install_db
（只需要運(yùn)行其中一個就夠了）——不然存儲過程將不能工作。我同時啟用在root身份后運(yùn)行一個非正式的SQL腳本，如下：
mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql

Starting the MySQL Client 啟動MySQL客戶端

這是我啟動mysql客戶端的方式。你也許會使用其他方式，如果你使用的是二進(jìn)制版本或者是Windows系統(tǒng)的電腦，你可能會在其他子目錄下運(yùn)行以下程序：
easy@phpv:~> /usr/local/mysql/bin/mysql --user=root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
在演示中，我將會展示以root身份登陸后的mysql客戶端返回的結(jié)果，這樣意味著我有極大的特權(quán)。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

Check for the Correct Version 核對版本
為了確認(rèn)使用的MySQL的版本是正確的，我們要查詢版本。我有兩種方法確認(rèn)我使用的是5.0版本：
SHOW VARIABLES LIKE 'version';
or
SELECT VERSION();

例如：
mysql> SHOW VARIABLES LIKE 'version';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| version | 5.0.3-alpha-debug |
+---------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT VERSION();
+-------------------+
| VERSION() |
+-------------------+
| 5.0.3-alpha-debug |
+-------------------+
1 row in set (0.00 sec)
當(dāng)看見數(shù)字'5.0.x' 后就可以確認(rèn)存儲過程能夠在這個客戶端上正常工作。
The Sample "Database" 示例數(shù)據(jù)庫
現(xiàn)在要做的第一件事是創(chuàng)建一個新的數(shù)據(jù)庫然后設(shè)定為默認(rèn)數(shù)據(jù)庫實現(xiàn)這個步驟的SQL
語句如下：
CREATE DATABASE db5;
USE db5;
例如：
mysql> CREATE DATABASE db5;
Query OK, 1 row affected (0.00 sec)
mysql> USE db5;
Database changed
在這里要避免使用有重要數(shù)據(jù)的實際的數(shù)據(jù)庫然后我們創(chuàng)建一個簡單的工作表。
實現(xiàn)這個步驟的SQL
語句如下：
mysql> CREATE DATABASE db5;
Query OK, 1 row affected (0.01 sec)
mysql> USE db5;
Database changed
mysql> CREATE TABLE t (s1 INT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t VALUES (5);
Query OK, 1 row affected (0.00 sec)
你會發(fā)現(xiàn)我只在表中插入了一列。這樣做的原因是我要保持表的簡單，因為在這里并不需要展示查詢數(shù)據(jù)的技巧，而是教授存儲過程，不需要使用大的數(shù)據(jù)表，因為它本身已經(jīng)夠復(fù)雜了。
這就是示例數(shù)據(jù)庫，我們將從這個名字為t的只包含一列的表開始Pick a Delimiter 選擇分隔符
現(xiàn)在我們需要一個分隔符，實現(xiàn)這個步驟的SQL語句如下：
DELIMITER //
例如：
mysql> DELIMITER //
分 隔符是你通知mysql客戶端你已經(jīng)完成輸入一個SQL語句的字符或字符串符號。一直以來我們都使用分號“；”，但在存儲過程中，這會產(chǎn)生不少問題，因為 存儲過程中有許多語句，所以每一個都需要一個分號因此你需要選擇一個不太可能出現(xiàn)在你的語句或程序中的字符串作為分隔符。我曾用過雙斜杠“//”，也有人 用豎線“|”。我曾見過在DB2程序中使用“@”符號的，但我不喜歡這樣。你可以根據(jù)自己的喜好來選擇，但是在這個課程中為了更容易理解，你最好選擇跟我 一樣。如果以后要恢復(fù)使用“;”（分號）作為分隔符，輸入下面語句就可以了：
"DELIMITER ;//".
CREATE PROCEDURE Example 創(chuàng)建程序?qū)嵗?br /> CREATE PROCEDURE p1 () SELECT * FROM t; //
也許這是你使用Mysql創(chuàng)建的第一個存儲過程。假如是這樣的話，最好在你的日記中記下這個重要的里程碑。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
SQL語句存儲過程的第一部分是“CREATE PROCEDURE”：
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
第二部分是過程名，上面新存儲過程的名字是p1。
Digression: Legal Identifiers 題外話：合法標(biāo)識符的問題
存儲過程名對大小寫不敏感，因此‘P1’和‘p1’是同一個名字，在同一個數(shù)據(jù)庫中你將不能給兩個存儲過程取相同的名字，因為這樣將會導(dǎo)致重載。某些DBMS允許重載（Oracle支持），但是MySQL不支持（譯者話：希望以后會支持吧。）。

你可以采取“數(shù)據(jù)庫名.存儲過程名”這樣的折中方法，如“db5.p1”。存儲過程名可以分開，它可以包括空格符，其長度限制為64個字符，但注意不要使用MySQL內(nèi)建函數(shù)的名字，如果這樣做了，在調(diào)用時將會出現(xiàn)下面的情況：
mysql> CALL pi();
Error 1064 (42000): You have a syntax error.
mysql> CALL pi ();
Error 1305 (42000): PROCEDURE does not exist.

在上面的第一個例子里，我調(diào)用的是一個名字叫pi的函數(shù)，但你必須在調(diào)用的函數(shù)名后加上空格，就像第二個例子那樣。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
其中“（）”是“參數(shù)列表”。
CREATE PROCEDURE
語句的第三部分是參數(shù)列表。通常需要在括號內(nèi)添加參數(shù)。例子中的存儲過程沒有參數(shù)，因此參數(shù)列表是空的—所以我只需要鍵入空括號，然而這是必須的。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
"SELECT * FROM t;"
是存儲過程的主體。
然后到了語句的最后一個部分了，它是存儲過程的主體，是一般的SQL語句。過程體中語句
"SELECT * FROM t;"
包含一個分號，如果后面有語句結(jié)束符號（//）時可以不寫這個分號。
如果你還記得我把這部分叫做程序的主體將會是件好事，因為（body）這個詞是大家使用的技術(shù)上的術(shù)語。通常我們不會將SELECT語句用在存儲過程中，這里只是為了演示。所以使用這樣的語句，能在調(diào)用時更好的看出程序是否正常工作。
ySQL 5.0 新特性教程 第一部分存儲過程:第二講
Why MySQL Statements are Legal in a Procedure Body
什么MySQL語句在存儲過程體中是合法的？

什么樣的SQL語句在Mysql存儲過程中才是合法的呢？你可以創(chuàng)建一個包含INSERT, UPDATE,DELETE, SELECT, DROP, CREATE, REPLACE等的語句。你唯一需要記住的是如果代碼中包含MySQL擴(kuò)充功能，那么代碼將不能移植。在標(biāo)準(zhǔn)SQL語句中：任何數(shù)據(jù)庫定義語言都是合法 的，如：
CREATE PROCEDURE p () DELETE FROM t; //
SET、COMMIT以及ROLLBACK
也是合法的，如：
CREATE PROCEDURE p () SET @x = 5; //
MySQL的附加功能：任何數(shù)據(jù)操作語言的語句都將合法。
CREATE PROCEDURE p () DROP TABLE t; //
MySQL擴(kuò)充功能：直接的SELECT也是合法的：
CREATE PROCEDURE p () SELECT 'a'; //
順便提一下，我將存儲過程中包括DDL語句的功能稱為MySQL附加功能的原因是在SQL標(biāo)準(zhǔn)中把這個定義為非核心的，即可選組件。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

在過程體中有一個約束，就是不能有對例程或表操作的數(shù)據(jù)庫操作語句。例如下面的例子就是非法的：
CREATE PROCEDURE p1 ()
CREATE PROCEDURE p2 () DELETE FROM t; //
下面這些對MySQL 5.0來說全新的語句，過程體中是非法的?lt;BR>CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION,
DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER.
不過你可以使用
"CREATE PROCEDURE db5.p1 () DROP DATABASE db5//"
，但是類似
"USE database"
語句也是非法的，因為MySQL假定默認(rèn)數(shù)據(jù)庫就是過程的工作場所。
Call the Procedure 調(diào)用存儲過程
1.
現(xiàn)在我們就可以調(diào)用一個存儲過程了，你所需要輸入的全部就是CALL和你過程名以及一個括號再一次強(qiáng)調(diào)，括號是必須的當(dāng)你調(diào)用例子里面的p1過程時，結(jié)果是屏幕返回了t表的內(nèi)容
mysql> CALL p1() //
+------+
| s1 |
+------+
| 5 |
+------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
因為過程中的語句是
"SELECT * FROM t;"

2. Let me say that again, another way.
其他實現(xiàn)方式
mysql> CALL p1() //
和下面語句的執(zhí)行效果一樣：
mysql> SELECT * FROM t; //
所以，你調(diào)用p1過程就相當(dāng)于你執(zhí)行了下面語句：
"SELECT * FROM t;".
好了，主要的知識點(diǎn)"創(chuàng)建和調(diào)用過程方法"已經(jīng)清楚了。我希望你能對自己說這相當(dāng)簡單。但是很快我們就有一系列的練習(xí)，每次都加一條子句，或者改變已經(jīng)存在的子句。那樣在寫復(fù)雜部件前我們將會有很多可用的子句。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Characteristics Clauses 特征子句
1.

CREATE PROCEDURE p2 ()
LANGUAGE SQL <--
NOT DETERMINISTIC <--
SQL SECURITY DEFINER <--
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t //

這里我給出的是一些能反映存儲過程特性的子句。子句內(nèi)容在括號之后，主體之前。這些子句都是可選的，他們有什么作用呢？
2.

CREATE PROCEDURE p2 ()
LANGUAGE SQL <--
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //

很 好，這個LANGUAGE SQL子句是沒有作用的。僅是為了說明下面過程的主體使用SQL語言編寫。這條是系統(tǒng)默認(rèn)的，但你在這里聲明是有用的，因為某些DBMS（IBM的 DB2）需要它，如果你關(guān)注DB2的兼容問題最好還是用上。此外，今后可能會出現(xiàn)除SQL外的其他語言支持的存儲過程。
3.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC <--
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //

下 一個子句，NOT DETERMINISTIC，是傳遞給系統(tǒng)的信息。這里一個確定過程的定義就是那些每次輸入一樣輸出也一樣的程序。在這個案例中，既然主體中含有 SELECT語句，那返回肯定是未知的因此我們稱其NOT DETERMINISTIC。但是MySQL內(nèi)置的優(yōu)化程序不會注意這個，至少在現(xiàn)在不注意。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
4.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER <--
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //

下一個子句是SQL SECURITY，可以定義為SQL SECURITY DEFINER或SQL SECURITY INVOKER。
這就進(jìn)入了權(quán)限控制的領(lǐng)域了，當(dāng)然我們在后面將會有測試權(quán)限的例子。
SQL SECURITY DEFINER
意味著在調(diào)用時檢查創(chuàng)建過程用戶的權(quán)限（另一個選項是SQLSECURITY INVOKER）。
現(xiàn)在而言，使用
SQL SECURITY DEFINER
指令告訴MySQL服務(wù)器檢查創(chuàng)建過程的用戶就可以了，當(dāng)過程已經(jīng)被調(diào)用，就不檢查執(zhí)行調(diào)用過程的用戶了。而另一個選項（INVOKER）
則是告訴服務(wù)器在這一步仍然要檢查調(diào)用者的權(quán)限。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t //

COMMENT 'A procedure'
是一個可選的注釋說明。
最后，注釋子句會跟過程定義存儲在一起。這個沒有固定的標(biāo)準(zhǔn)，我在文中會指出沒有固定標(biāo)準(zhǔn)的語句，不過幸運(yùn)的是這些在我們標(biāo)準(zhǔn)的SQL中很少。
6.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
SELECT CURRENT_DATE, RAND() FROM t //

上面過程跟下面語句是等效的：

CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t //

特征子句也有默認(rèn)值，如果省略了就相當(dāng)于：

LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''.

Digressions一些題外話轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

Digression:
調(diào)用p2()//的結(jié)果
mysql> call p2() //
+--------------+-----------------+
| CURRENT_DATE | RAND() |
+--------------+-----------------+
| 2004-11-09 | 0.7822275075896 |
+--------------+-----------------+
1 row in set (0.26 sec)
Query OK, 0 rows affected (0.26 sec)
當(dāng)調(diào)用過程p2時，一個SELECT語句被執(zhí)行返回我們期望獲得的隨機(jī)數(shù)。
Digression: sql_mode unchanging
不會改變的

sql_mode
mysql> set sql_mode='ansi' //
mysql> create procedure p3()select'a'||'b'//
mysql> set sql_mode=''//
mysql> call p3()//
+------------+
| 'a' || 'b' |
+------------+
| ab |
+------------+

MySQL在過程創(chuàng)建時會自動保持運(yùn)行環(huán)境。例如：我們需要使用兩條豎線來連接字符串但是這只有在sql mode為ansi的時候才合法。如果我們將sql mode改為non-ansi，不用擔(dān)心，它仍然能工作，只要它第一次使用時能正常工作。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

Exercise 練習(xí)

Question
問題
如果你不介意練習(xí)一下的話，試能否不看后面的答案就能處理這些請求。
創(chuàng)建一個過程，顯示`Hello world`。用大約5秒時間去思考這個問題，既然你已經(jīng)學(xué)到了這里，這個應(yīng)該很簡單。當(dāng)你思考問題的時候，我們再隨機(jī)選擇一些剛才講過的東西復(fù)習(xí)：
DETERMINISTIC
（確定性）子句是反映輸出和輸入依賴特性的子句…調(diào)用過程使用CALL過程名（參數(shù)列表）方式。好了，我猜時間也到了。

Answer
答案
好的，答案就是在過程體中包含
"SELECT 'Hello, world'"
語句
MySQL

mysql> CREATE PROCEDURE p4 () SELECT 'Hello, world' //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p4()//
+--------------+
| Hello, world |
+--------------+
| Hello, world |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Parameters 參數(shù)

讓我們更進(jìn)一步的研究怎么在存儲過程中定義參數(shù)
1. CREATE PROCEDURE p5
() ...
2. CREATE PROCEDURE p5
([IN] name data-type) ...
3. CREATE PROCEDURE p5
(OUT name data-type) ...
4. CREATE PROCEDURE p5
(INOUT name data-type) ...

回憶一下前面講過的參數(shù)列表必須在存儲過程名后的括號中。上面的第一個例子中的參數(shù)列表是空的，第二個例子中有一個輸入?yún)?shù)。這里的詞IN可選，因為默認(rèn)參數(shù)為IN（input）。
第三個例子中有一個輸出參數(shù)，第四個例子中有一個參數(shù)，既能作為輸入也可以作為輸出。
IN example 輸入的例子
mysql> CREATE PROCEDURE p5(p INT) SET @x = p //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p5(12345)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+-------+
| @x |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)

這個IN的例子演示的是有輸入?yún)?shù)的過程。在過程體中我將會話變量x設(shè)定為參數(shù)p的值。然后調(diào)用過程，將12345傳入?yún)?shù)p。選擇顯示會話變量@x，證明我們已經(jīng)將參數(shù)值12345傳入。
OUT example 輸出的例子
mysql> CREATE PROCEDURE p6 (OUT p INT)
-> SET p = -5 //
mysql> CALL p6(@y)//
mysql> SELECT @y//
+------+
| @y |
+------+
| -5 |
+------+

這是另一個例子。這次的p是輸出參數(shù)，然后在過程調(diào)用中將p的值傳入會話變量@y中。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
在過程體中，我們給參數(shù)賦值-5，在調(diào)用后我們可以看出，OUT是告訴DBMS值是從過程中傳出的。
同樣我們可以用語句
"SET @y = -5;".
來達(dá)到同樣的效果

Compound Statements 復(fù)合語句

現(xiàn)在我們展開的詳細(xì)分析一下過程體：

CREATE PROCEDURE p7 ()
BEGIN
SET @a = 5;
SET @b = 5;
INSERT INTO t VALUES (@a);
SELECT s1 * @a FROM t WHERE s1 >= @b;
END; // /* I won't CALL this.
這個語句將不會被調(diào)用
*/

完 成過程體的構(gòu)造就是BEGIN/END塊。這個BEGIN/END語句塊和Pascal語言中的BEGIN/END是基本相同的，和C語言的框架是很相似 的。我們可以使用塊去封裝多條語句。在這個例子中，我們使用了多條設(shè)定會話變量的語句，然后完成了一些insert和select語句。如果你的過程體中 有多條語句，那么你就需要BEGIN/END塊了。BEGIN/END塊也被稱為復(fù)合語句，在這里你可以進(jìn)行變量定義和流程控制。

第三講:新SQL語句,Loops 循環(huán)語句

The New SQL Statements 新SQL語句
Variables 變量
在復(fù)合語句中聲明變量的指令是DECLARE。
(1) Example with two DECLARE statements
兩個DECLARE語句的例子
CREATE PROCEDURE p8 ()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 5;
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; // /* I won't CALL this */

在過程中定義的變量并不是真正的定義，你只是在BEGIN/END塊內(nèi)定義了而已（譯注：也就是形參）。
注意這些變量和會話變量不一樣，不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。
變量一旦聲明，你就能在任何能使用會話變量、文字、列名的地方使用。

(2) Example with no DEFAULT clause and SET statement
沒有默認(rèn)子句和設(shè)定語句的例子
CREATE PROCEDURE p9 ()
BEGIN
DECLARE a INT /* there is no DEFAULT clause */;
DECLARE b INT /* there is no DEFAULT clause */;
SET a = 5; /* there is a SET statement */
SET b = 5; /* there is a SET statement */
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; // /* I won't CALL this */
有很多初始化變量的方法。如果沒有默認(rèn)的子句，那么變量的初始值為NULL。你可以在任何時候使用SET語句給變量賦值。

(3) Example with DEFAULT clause
含有DEFAULT子句的例子
CREATE PROCEDURE p10 ()
BEGIN
DECLARE a, b INT DEFAULT 5;
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; //
我們在這里做了一些改變，但是結(jié)果還是一樣的。在這里使用了DEFAULT子句來設(shè)定初
始值，這就不需要把DECLARE和SET語句的實現(xiàn)分開了。
(4) Example of CALL
調(diào)用的例子
mysql> CALL p10() //
+--------+
| s1 * a |
+--------+
| 25 |
| 25 |
+--------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
結(jié)果顯示了過程能正常工作

(5) Scope
作用域
CREATE PROCEDURE p11 ()
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'outer';
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'inner';
SELECT x1;
END;
SELECT x1;
END; //
現(xiàn)在我們來討論一下作用域的問題。例子中有嵌套 的BEGIN/END塊，當(dāng)然這是合法的。同時包含兩個變量，名字都 是x1，這樣也是合法的。內(nèi)部的變量在其作用域內(nèi)享有更高的優(yōu)先權(quán)。當(dāng)執(zhí)行到END語句時，內(nèi)部變量消失，此時已經(jīng)在其作用域外，變量不再可見了，因此在 存儲過程外再也不能找到這個聲明了的變量，但是你可以通過OUT參數(shù)或者將其值指派 給會話變量來保存其值。

調(diào)用作用域例子的過程：
mysql> CALL p11()//
+-------+
| x1 |
+-------+
| inner |
+-------+
+-------+
| x1 |
+-------+
| outer |
+-------+
我們看到的結(jié)果時第一個SELECT語句檢索到最內(nèi)層的變量，第二個檢索到第二層的變量

Conditions and IF-THEN-ELSE 條件式和IF-THEN-ELSE
1.
現(xiàn)在我們可以寫一些包含條件式的例子：
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
這里是一個包含IF語句的過程。里面有兩個IF語句，一個是IF語句END IF，另一個是IF語句ELSE語句END IF。我們可以在這里使用復(fù)雜的過程，但我會盡量使其簡單讓你能更容易弄清楚。
2.
CALL p12 (0) //
我們調(diào)用這個過程，傳入值為0，這樣parameter1的值將為0。

3.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1; <--
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
這里變量variable1被賦值為parameter1加1的值，所以執(zhí)行后變量variable1為1。
4.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN <--
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
因為變量variable1值為1，因此條件"if variable1 = 0"為假，
IF
……
END IF
被跳過，沒有被執(zhí)行。
5.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN <--
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //

到第二個IF條件，判斷結(jié)果為真，于是中間語句被執(zhí)行了
6.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1; <--
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
因為參數(shù)parameter1值等于0，UPDATE語句被執(zhí)行。如果parameter1值為NULL，則下一條
UPDATE
語句將被執(zhí)行現(xiàn)在表t中有兩行，他們都包含值5，所以如果我們調(diào)用p12，兩行的值會變成6。
7.
mysql> CALL p12(0)//
Query OK, 2 rows affected (0.28 sec)
mysql> SELECT * FROM t//
+------+
| s1 |
+------+
| 6 |
| 6 |
+------+
2 rows in set (0.01 sec)
結(jié)果也是我們所期望的那樣。
CASE 指令
1.
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (17);
WHEN 1 THEN INSERT INTO t VALUES (18);
ELSE INSERT INTO t VALUES (19);
END CASE;
END; //
如果需要進(jìn)行更多條件真假的判斷我們可以使用CASE語句。CASE語句使用和IF一樣簡單。
我們可以參考上面的例子：
2.
mysql> CALL p13(1)//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t//
+------+
| s1 |
+------+
| 6 |
| 6 |
| 19 |
+------+
3 rows in set (0.00 sec)
執(zhí)行過程后，傳入值1，如上面例子，值19被插入到表t中。
Question
問題
問題: CALL p13(NULL) //的作用是什么？
另一個：這個CALL語句做了那些動作？
你可以通過執(zhí)行后觀察SELECT做了什么，也可以根據(jù)代碼判斷，在5秒內(nèi)做出。
Answer
答案
mysql> CALL p13(NULL)//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t//
+------+
| s1 |
+------+
| 6 |
| 6 |
| 19 |
| 19 |
+------+
4 rows in set (0.00 sec)
答案是當(dāng)你調(diào)用p13時，MySQL插入了另一條包含數(shù)值19的記錄。原因是變量variable1的值為NULL，CASE語句的ELSE部分就被執(zhí)行了。希望這對大家有意義。如果你回答不出來，沒有問題，我們可以繼續(xù)向下走。

Loops 循環(huán)語句
WHILE ... END WHILE
LOOP ... END LOOP
REPEAT ... END REPEAT
GOTO
下面我們將會創(chuàng)建一些循環(huán)。我們有三種標(biāo)準(zhǔn)的循環(huán)方式：
WHILE循環(huán)，LOOP循環(huán)以及REPEAT循環(huán)。還有一種非標(biāo)準(zhǔn)的循環(huán)方式：GO TO（譯者語：最好不要用吧，用了就使流程混亂）。
WHILE ... END WHILE
CREATE PROCEDURE p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //
這是WHILE循環(huán)的方式。我很喜歡這種方式，它跟IF語句相似，因此不需要掌握很多新的語法。這里的INSERT和SET語句在WHILE和END WHILE之間，當(dāng)變量v大于5的時候循環(huán)將會退出。使用
"SET v = 0;"
語句使為了防止一個常見的錯誤，如果沒有初始化，默認(rèn)變量值為NULL，而NULL和任何值操作結(jié)果都為NULL。
WHILE ... END WHILE example
mysql> CALL p14()//
Query OK, 1 row affected (0.00 sec)
以上就是調(diào)用過程p14的結(jié)果不用關(guān)注系統(tǒng)返回是"one row affected"還是"five rows affected"，因為這里的計數(shù)只對最后一個INSERT動作進(jìn)行計數(shù)。

WHILE ... END WHILE example: CALL
mysql> select * from t; //
+------+
| s1 |
+------+
....
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+------+
9 rows in set (0.00 sec)
調(diào)用后可以看到程序向數(shù)據(jù)庫中插入了5行。
REPEAT ... END REPEAT
CREATE PROCEDURE p15 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 5
END REPEAT;
END; //
這是一個REPEAT循環(huán)的例子，功能和前面WHILE循環(huán)一樣。區(qū)別在于它在執(zhí)行后檢查結(jié)果，而WHILE則是執(zhí)行前檢查。（譯者語：可能等同于DO WHILE吧）

REPEAT ... END REPEAT: look at the UNTIL： UNTIL的作用
CREATE PROCEDURE p15 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 5 <--
END REPEAT;
END; //
注意到UNTIL語句后面沒有分號，在這里可以不寫分號，當(dāng)然你加上額外的分號更好。

REPEAT ... END REPEAT: calling ：調(diào)用
mysql> CALL p15()//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(*) FROM t//
+----------+
| COUNT(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
我們可以看到調(diào)用p15過程后又插入了5行記錄

LOOP ... END LOOP
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
以上是LOOP循環(huán)的例子。
LOOP循環(huán)不需要初始條件，這點(diǎn)和WHILE循環(huán)相似，同時它又和REPEAT循環(huán)一樣也不需要結(jié)束條件。

LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循環(huán)
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN <--
LEAVE loop_label;
END IF;
END LOOP;
END; //
在循環(huán)內(nèi)部加入IF語句，在IF語句中包含LEAVE語句。這里L(fēng)EAVE語句的意義是離開循環(huán)。
LEAVE的語法是LEAVE加循環(huán)語句標(biāo)號，關(guān)于循環(huán)語句的標(biāo)號問題我會在后面進(jìn)一步講解。
LOOP ... END LOOP: calling ：調(diào)用
mysql> CALL p16()//
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(*) FROM t//
+----------+
| COUNT(*) |
+----------+
| 19 |
+----------+
1 row in set (0.00 sec)
調(diào)用過程p16后，結(jié)果是另5行被插入表t中。

Labels 標(biāo)號
CREATE PROCEDURE p17 ()
label_1: BEGIN
label_2: WHILE 0 = 1 DO LEAVE label_2; END
WHILE;
label_3: REPEAT LEAVE label_3; UNTIL 0 =0
END REPEAT;
label_4: LOOP LEAVE label_4; END LOOP;
END; //
最后一個循環(huán)例子中我使用了語句標(biāo)號?，F(xiàn)在這里有一個包含4個語句標(biāo)號的過程的例子。我們可以在BEGIN、 WHILE、REPEAT或者LOOP語句前使用語句標(biāo)號，語句標(biāo)號只能在合法的語句前面使用。因此"LEAVE label_3"意味著離開語句標(biāo)號名定義為label_3的語句或復(fù)合語句。

End Labels 標(biāo)號結(jié)束符
CREATE PROCEDURE p18 ()
label_1: BEGIN
label_2: WHILE 0 = 1 DO LEAVE label_2; END
WHILE label_2;
label_3: REPEAT LEAVE label_3; UNTIL 0 =0
END REPEAT label_3 ;
label_4: LOOP LEAVE label_4; END LOOP
label_4 ;
END label_1 ; //
你也可以在語句結(jié)束時使用語句標(biāo)號，和在開頭時使用一樣。這些標(biāo)號結(jié)束符并不是十分有用。
它們是可選的。如果你需要，他們必須和開始定義的標(biāo)號名字一樣當(dāng)然為了有良好的編程習(xí)慣，
方便他人閱讀，最好還是使用標(biāo)號結(jié)束符。
LEAVE and Labels 跳出和標(biāo)號
CREATE PROCEDURE p19 (parameter1 CHAR)
label_1: BEGIN
label_2: BEGIN
label_3: BEGIN
IF parameter1 IS NOT NULL THEN
IF parameter1 = 'a' THEN
LEAVE label_1;
ELSE BEGIN
IF parameter1 = 'b' THEN
LEAVE label_2;
ELSE
LEAVE label_3;
END IF;
END;
END IF;
END IF;
END;
END;
END;//
LEAVE
語句使程序跳出復(fù)雜的復(fù)合語句。
ITERATE
迭代如果目標(biāo)是ITERATE（迭代）語句的話，就必須用到LEAVE語句
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //

ITERATE
（迭代）語句和LEAVE語句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點(diǎn)像C語言中的“Continue”，同樣它可以出現(xiàn)在復(fù)合語句中，引用復(fù)合語句標(biāo)號，ITERATE（迭代）意思是重新開始復(fù)合語句。
那我們啟動并觀察下面這個循環(huán)，這是個需要迭代過程的循環(huán)：

ITERATE: Walking through the loop
深入循環(huán)
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP <--
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
讓這個已經(jīng)定義了標(biāo)號的循環(huán)運(yùn)行起來。

ITERATE: Walking through the loop
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN <--
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
v的值變成3，然后我們把它增加到4。

ITERATE: walking through the loop
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label; <--
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
然后開始ITERATE（迭代）過程。

ITERATE: walking through the loop
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP <--
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
這里的ITERATE（迭代）讓循環(huán)又回到了循環(huán)的頭部。

ITERATE: walking through the loop
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label; <--
END IF;
END LOOP;
END; //
當(dāng)v的值變?yōu)?時，程序?qū)?zhí)行LEAVE語句

ITERATE: walking through the loop
CREATE PROCEDURE p20 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
IF v = 3 THEN
SET v = v + 1;
ITERATE loop_label;
END IF;
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; // <--
LEAVE的結(jié)果就是跳出循環(huán)，使運(yùn)行指令到達(dá)復(fù)合語句的最后一步。

GOTO
CREATE PROCEDURE p...
BEGIN
...
LABEL label_name;
...
GOTO label_name;
...
END;
MySQL的存儲過程中可以使用GOTO語句。雖然這不是標(biāo)準(zhǔn)SQL語句，而且在這里建立標(biāo)號的方法也和慣例中的不一樣。由于為了和其他DBMS兼容，這個語句會慢被淘汰，所以我們在MySQL
參考手冊中沒有提及。

Grand combination
大組合
CREATE PROCEDURE p21
(IN parameter_1 INT, OUT parameter_2 INT)
LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER
BEGIN
DECLARE v INT;
label goto_label; start_label: LOOP
IF v = v THEN LEAVE start_label;
ELSE ITERATE start_label;
END IF;
END LOOP start_label;
REPEAT
WHILE 1 = 0 DO BEGIN END;
END WHILE;
UNTIL v = v END REPEAT;
GOTO goto_label;
END;//
上面例子中的語句包含了我們之前講的所有語法，包括參數(shù)列表，特性參數(shù)，BEGIN/END塊復(fù)合語句，變量聲明，
IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。這是一個荒謬的存儲過程，我不會運(yùn)行它，因為里面有無盡的循環(huán)。但是里面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。
第一部分存儲過程:異常處理,游標(biāo),游標(biāo)的特性,安全措施
Error Handling 異常處理
好了，我們現(xiàn)在要講的是異常處理
1. Sample Problem: Log Of Failures 問題樣例：故障記錄
當(dāng)INSERT失敗時，我希望能將其記錄在日志文件中我們用來展示出錯處理的問題樣例是很
普通的。我希望得到錯誤的記錄。當(dāng)INSERT失敗時，我想在另一個文件中記下這些錯誤的
信息，例如出錯時間，出錯原因等。我對插入特別感興趣的原因是它將違反外鍵關(guān)聯(lián)的約束
2. Sample Problem: Log Of Failures (2)
mysql> CREATE TABLE t2
s1 INT, PRIMARY KEY (s1))
engine=innodb;//
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
FOREIGN KEY (s1) REFERENCES t2 (s1))
engine=innodb;//
mysql> INSERT INTO t3 VALUES (5);//
...
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails（這里顯示的是系統(tǒng)的出錯信息）
我開始要創(chuàng)建一個主鍵表，以及一個外鍵表。我們使用的是InnoDB，因此外鍵關(guān)聯(lián)檢查是打
開的。然后當(dāng)我向外鍵表中插入非主鍵表中的值時，動作將會失敗。當(dāng)然這種條件下可以很
快找到錯誤號1216。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
3. Sample Problem: Log Of Failures
CREATE TABLE error_log (error_message
CHAR(80))//
下一步就是建立一個在做插入動作出錯時存儲錯誤的表。
4. Sample Problem: Log Of Errors
CREATE PROCEDURE p22 (parameter1 INT)
BEGIN
DECLARE EXIT HANDLER FOR 1216
INSERT INTO error_log VALUES
(CONCAT('Time: ',current_date,
'. Foreign Key Reference Failure For
Value = ',parameter1));
INSERT INTO t3 VALUES (parameter1);
END;//
上面就是我們的程序。這里的第一個語句DECLARE EXIT HANDLER是用來處理異常的。意
思是如果錯誤1215發(fā)生了，這個程序?qū)阱e誤記錄表中插入一行。EXIT意思是當(dāng)動作成功提交后退出這個復(fù)合語句。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5. Sample Problem: Log Of Errors
CALL p22 (5) //
調(diào)用這個存儲過程會失敗，這很正常，因為5值并沒有在主鍵表中出現(xiàn)。但是沒有錯誤信息
返回因為出錯處理已經(jīng)包含在過程中了。t3表中沒有增加任何東西，但是error_log表中記錄
下了一些信息，這就告訴我們INSERT into table t3動作失敗。

DECLARE HANDLER syntax 聲明異常處理的語法
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement

上面就是錯誤處理的用法，也就是一段當(dāng)程序出錯后自動觸發(fā)的代碼。MySQL允許兩種處理器，
一種是EXIT處理，我們剛才所用的就是這種。另一種就是我們將要演示的，CONTINUE處理，
它跟EXIT處理類似，不同在于它執(zhí)行后，原主程序仍然繼續(xù)運(yùn)行，那么這個復(fù)合語句就沒有出
口了。
1. DECLARE CONTINUE HANDLER example CONTINUE處理例子
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這是MySQL參考手冊上的CONTINUE處理的例子，這個例子十分好，所以我把它拷貝到這里。
通過這個例子我們可以看出CONTINUE處理是如何工作的。

2. DECLARE CONTINUE HANDLER聲明CONTINUE異常處理
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這次我將為SQLSTATE值定義一個處理程序。還記得前面我們使用的MySQL錯誤代碼1216嗎？
事實上這里的23000SQLSTATE是更常用的，當(dāng)外鍵約束出錯或主鍵約束出錯就被調(diào)用了。

3. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1; <--
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這個存儲過程的第一個執(zhí)行的語句是"SET @x = 1"。

4. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); <--
SET @x = 3;
END;//
運(yùn)行后值1被插入到主鍵表中。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

5. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2; <--
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
然后@x的值變?yōu)?。

6. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); <--
SET @x = 3;
END;//
然后程序嘗試再次往主鍵表中插入數(shù)值，但失敗了，因為主鍵有唯一性限制。

7. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
由于插入失敗，錯誤處理程序被觸發(fā)，開始進(jìn)行錯誤處理。下一個執(zhí)行的語句是錯誤處理的語
句，@x2被設(shè)為2。

8. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3; <--
END;//
到這里并沒有結(jié)束，因為這是CONTINUE異常處理。所以執(zhí)行返回到失敗的插入語句之后，
繼續(xù)執(zhí)行將@x設(shè)定為3動作。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

9. DECLARE CONTINUE HANDLER example
mysql> CALL p23()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x, @x2//
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)

運(yùn)行過程后我們觀察@x的值，很確定的可以知道是3，觀察@x2的值，為1。從這里可以
判斷程序運(yùn)行無誤，完全按照我們的思路進(jìn)行。大家可以花點(diǎn)時間去調(diào)整錯誤處理器，讓
檢查放在語句段的首部，而不是放在可能出現(xiàn)錯誤的地方，雖然那樣看起來程序很紊亂，
跳來跳去的感覺。但是這樣的代碼很安全也很清楚。


1. DECLARE CONDITION
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
這是另外一個錯誤處理的例子，在前面的基礎(chǔ)上修改的。事實上你可給SQLSTATE或者錯誤代碼其他的名字，你就可以在處理中使用自己定義的名字了。下面看它是怎么實現(xiàn)的：我把表t2
定義為InnoDB表，所以對這個表的插入操作都會ROLLBACK（回滾），ROLLBACK（回滾事務(wù)）也是恰好會發(fā)生的。因為對主鍵插入兩個同樣的值會導(dǎo)致SQLSTATE 23000錯誤發(fā)生，這里SQLSTATE 23000是約束錯誤。
2. DECLARE CONDITION聲明條件
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
這個約束錯誤會導(dǎo)致ROLLBACK（回滾事務(wù)）和SQLSTATE 23000錯誤發(fā)生。

3. DECLARE CONDITION
mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)

mysql> SELECT * FROM t2//
Empty set (0.00 sec)

我們調(diào)用這個存儲過程看結(jié)果是什么，從上面結(jié)果我們看到表t2沒有插入任何記錄。全部事務(wù)都回滾了。這正是我們想要的。

4. DECLARE CONDITION

mysql> CREATE PROCEDURE p9 ()
-> BEGIN
-> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
-> END;//
Query OK, 0 rows affected (0.00 sec)

這里是三個預(yù)聲明的條件：NOT FOUND (找不到行), SQLEXCEPTION (錯誤),SQLWARNING (
警告或注釋)。因為它們是預(yù)聲明的，因此不需要聲明條件就可以使用。不過如果你去做這樣的聲明："DECLARE SQLEXCEPTION CONDITION ..."，你將會得到錯誤信息提示。


Cursors 游標(biāo)

游標(biāo)實現(xiàn)功能摘要：
DECLARE cursor-name CURSOR FOR SELECT ...;
OPEN cursor-name;
FETCH cursor-name INTO variable [, variable];
CLOSE cursor-name;
現(xiàn)在我們開始著眼游標(biāo)了。雖然我們的存儲過程中的游標(biāo)語法還并沒有完整的實現(xiàn)，但是
已經(jīng)可以完成基本的事務(wù)如聲明游標(biāo)，打開游標(biāo)，從游標(biāo)里讀取，關(guān)閉游標(biāo)。

1. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//
我們看一下包含游標(biāo)的存儲過程的新例子。

2. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT; <--
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//
這個過程開始聲明了三個變量。附帶說一下，順序是十分重要的。首先要進(jìn)行變量聲明，
然后聲明條件，隨后聲明游標(biāo)，再后面才是聲明錯誤處理器。如果你沒有按順序聲明，
系統(tǒng)會提示錯誤信息。

3. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; <--
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

程序第二步聲明了游標(biāo)cur_1，如果你使用過嵌入式SQL的話，就知道這和嵌入式SQL差不多。

4. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND <--
SET b = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

最后進(jìn)行的是錯誤處理器的聲明。這個CONTINUE處理沒有引用SQL錯誤代碼和SQLSTATE值。
它使用的是NOT FOUND系統(tǒng)返回值，這和SQLSTATE 02000是一樣的。
轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1; <--
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

過程第一個可執(zhí)行的語句是OPEN cur_1，它與SELECT s1 FROM t語句是關(guān)聯(lián)的，過程將執(zhí)行
SELECT s1 FROM t，返回一個結(jié)果集。

6. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a; <--
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

這里第一個FETCH語句會獲得一行從SELECT產(chǎn)生的結(jié)果集中檢索出來的值，然而表t中有多
行，因此這個語句會被執(zhí)行多次，當(dāng)然這是因為語句在循環(huán)塊內(nèi)。

7. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

最后當(dāng)MySQL的FETCH沒有獲得行時，CONTINUE處理被觸發(fā)，將變量b賦值為1。

8. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1; <--
SET return_val = a;
END;//

到了這一步UNTIL b=1條件就為真，循環(huán)結(jié)束。在這里我們可以自己編寫代碼關(guān)閉游標(biāo)，
也可以由系統(tǒng)執(zhí)行，系統(tǒng)會在復(fù)合語句結(jié)束時自動關(guān)閉游標(biāo)，但是最好不要太依賴系統(tǒng)的
自動關(guān)閉行為（譯注：這可能跟Java的Gc一樣，不可信）。

9. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a; <--
END;//
這個例程中我們?yōu)檩敵鰠?shù)指派了一個局部變量，這樣在過程結(jié)束后的結(jié)果仍能使用。
轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
10. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

mysql> CALL p25(@return_val)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @return_val//
+-------------+
| @return_val |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)

上面是過程調(diào)用后的結(jié)果?？梢钥吹絩eturn_val參數(shù)獲得了數(shù)值5，因為這是表t的最后一行。
由此可以知道游標(biāo)工作正常，出錯處理也工作正常。

Cursor Characteristics 游標(biāo)的特性
摘要：轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
READ ONLY只讀屬性
NOT SCROLLABLE順序讀取
ASENSITIVE敏感

在5.0版的MySQL中，你只可以從游標(biāo)中取值，不能對其進(jìn)行更新。因為游標(biāo)是（READ
ONLY）只讀的。你可以這樣做：
FETCH cursor1 INTO variable1;
UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1;

游標(biāo)也是不可以滾動的，只允許逐一讀取下一行，不能在結(jié)果集中前進(jìn)或后退。下面代碼就
是錯誤的：
FETCH PRIOR cursor1 INTO variable1;
FETCH ABSOLUTE 55 cursor1 INTO variable1;

同時也不允許在已打開游標(biāo)進(jìn)行操作的表上執(zhí)行updates事務(wù)，因為游標(biāo)是（ASENSITIVE）敏感的。因為如果你不阻止update事務(wù)，那就不知道結(jié)果會變成什么。如果你使用的是InnoDB
而不是MyISAM存儲引擎的話，結(jié)果也會不一樣。
Security 安全措施
摘要
Privileges (1) CREATE ROUTINE
Privileges (2) EXECUTE
Privileges (3) GRANT SHOW ROUTINE?
Privileges (4) INVOKERS AND DEFINERS

這里我們要討論一些關(guān)于特權(quán)和安全相關(guān)的問題。但因為在MySQL安全措施的功能并沒有完全，所以我們不會對其進(jìn)行過多討論。

1. Privileges CREATE ROUTINE

GRANT CREATE ROUTINE
ON database-name . *
TO user(s)
[WITH GRANT OPTION];
現(xiàn)在用root就可以了

在這里要介紹的特權(quán)是CREATE ROUTINE，它不僅同其他特權(quán)一樣可以創(chuàng)建存儲過程和函數(shù)，
還可以創(chuàng)建視圖和表。Root用戶擁有這種特權(quán)，同時還有ALTER ROUTINE特權(quán)。

2. Privileges EXECUTE

GRANT EXECUTE ON p TO peter
[WITH GRANT OPTION];

上面的特權(quán)是決定你是否可以使用或執(zhí)行存儲過程的特權(quán)，過程創(chuàng)建者默認(rèn)擁有這個特權(quán)。

3. Privileges SHOW ROUTINE?

GRANT SHOW ROUTINE ON db6.* TO joey
[WITH GRANT OPTION];

因為我們已經(jīng)有控制視圖的特權(quán)了：GRANT SHOW VIEW。所以在這個基礎(chǔ)上，為了保證兼容，
日后可能會添加GRANT SHOW ROUTINE特權(quán)。這樣做是不太符合標(biāo)準(zhǔn)的，在寫本書的時候，MySQL還沒實現(xiàn)這個功能。

4. Privileges Invokers and Definers 特權(quán)調(diào)用者和定義者

CREATE PROCEDURE p26 ()
SQL SECURITY INVOKER
SELECT COUNT(*) FROM t //
CREATE PROCEDURE p27 ()
SQL SECURITY DEFINER
SELECT COUNT(*) FROM t //
GRANT INSERT ON db5.* TO peter; //

現(xiàn)在我們測試一下SQL SECURITY子句吧。Security是我們前面提到的程序特性的一部分。你root
用戶，將插入權(quán)賦給了peter。然后使用peter登陸進(jìn)行新的工作，我們看peter可以怎么使用存儲過程，注意：peter沒有對表t的select權(quán)力，只有root用戶有。

5. Privileges Invokers and Definers

/* Logged on with current_user = peter */使用帳戶peter登陸

mysql> CALL p26();
ERROR 1142 (42000): select command denied to user
'peter'@'localhost' for table 't'
mysql> CALL p27();
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

當(dāng)peter嘗試調(diào)用含有調(diào)用保密措施的過程p26時會失敗。那是因為peter沒有對表的select的權(quán)力。
但是當(dāng)petre調(diào)用含有定義保密措施的過程時就能成功。原因是root有select權(quán)力，Peter有root的
權(quán)力，因此過程可以執(zhí)行。
MySQL 5.0 觸發(fā)器
Introduction
本書是為需要了解5.0版本新特性的MySQL老用戶而寫的。簡單的來說介紹了 "存儲過程、觸發(fā)器、視圖、信息架構(gòu)視圖"，這是介紹MySQL 5.0新特性叢書的第一集。希望這本書能像內(nèi)行專家那樣與您進(jìn)行對話，用簡單的問題、例子讓你學(xué)到需要的知識。為了達(dá)到這樣的目的，我會從每一個細(xì)節(jié)開始 慢的為大家建立概念，最后會給大家展示較大的實用例，在學(xué)習(xí)之前也許大家會認(rèn)為這個用例很難，但是只要跟著課程去學(xué)，相信很快就能掌握。

Conventions and Styles約定和編程風(fēng)格
每次我想要演示實際代碼時，我會對mysql客戶端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來與普通文本不一樣（讓大家區(qū)別程序代碼和正文）。在這里舉個例子：

mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)

如果實例比較大，則需要在某些行和段落間加注釋，同時我會用將"<--"符號放在頁面的右邊以表示強(qiáng)調(diào)。例如：

mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//
Query OK, 0 rows affected (0.00 sec)

有時候我會將例子中的"mysql>"和"->"這些系統(tǒng)顯示去掉，你可以直接將代碼復(fù)制到mysql客戶端程序中（如果你現(xiàn)在所讀的不是電子版的，可以在mysql.com網(wǎng)站下載相關(guān)腳本）

所以的例子都已經(jīng)在Suse 9.2 Linux、Mysql 5.0.3公共版上測試通過。在您閱讀本書的時候，Mysql已經(jīng)有更高的版本，同時能支持更多OS了，包括Windows，Sparc，HP-UX。因此這里的
例子將能正常的運(yùn)行在您的電腦上。但如果運(yùn)行仍然出現(xiàn)故障，可以咨詢你認(rèn)識的資深Mysql用戶，這樣就能得到比較好的支持和幫助。轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

Why Triggers 為什么要用觸發(fā)器

我們在MySQL 5.0中包含對觸發(fā)器的支持是由于以下原因:

MySQL早期版本的用戶長期有需要觸發(fā)器的要求。
我們曾經(jīng)許諾支持所有ANSI標(biāo)準(zhǔn)的特性。
您可以使用它來檢查或預(yù)防壞的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫。
您可以改變或者取消INSERT, UPDATE以及DELETE語句。
您可以在一個會話中監(jiān)視數(shù)據(jù)改變的動作。

在這里我假定大家都讀過"MySQL新特性"叢書的第一集--"MySQL存儲過程"，那么大家都應(yīng)該知道MySQL至此存儲過程和函數(shù)，那是很重要的知識，因為在觸發(fā)器中你可以使用在函數(shù)中使用的語句。特別舉個例子：

復(fù)合語句(BEGIN / END)是合法的.轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
流控制（Flow-of-control）語句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.
變量聲明(DECLARE)以及指派(SET)是合法的.
允許條件聲明.
異常處理聲明也是允許的.

但是在這里要記住函數(shù)有受限條件:不能在函數(shù)中訪問表.因此在函數(shù)中使用以下語句是非法的。

ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE

在觸發(fā)器中也有完全一樣的限制.

觸發(fā)器相對而言比較新，因此會有（bugs）缺陷.所以我在這里給大家警告，就像我在存儲過程書中所說那樣.不要在含有重要數(shù)據(jù)的數(shù)據(jù)庫中使用這個觸發(fā)器，如果需要的話在一些以測試為目的的數(shù)據(jù)庫上使用，同時在你對表創(chuàng)建觸發(fā)器時確認(rèn)這些數(shù)據(jù)庫是默認(rèn)的。

Syntax 語法
1. Syntax: Name 語法：命名規(guī)則轉(zhuǎn)載請注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net

CREATE TRIGGER <觸發(fā)器名稱> <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)器SQL語句>

觸發(fā)器必須有名字，最多64個字符，可能后面會附有分隔符.它和MySQL中其他對象的命名方式基本相象.

這里我有個習(xí)慣：就是用表的名字＋'_'＋觸發(fā)器類型的縮寫.因此如果是表t26，觸發(fā)器是在事件UPDATE（參考下面的點(diǎn)（2）和（3））之前（BEFORE）的，那么它的名字就是t26_bu。

2. Syntax: Time 語法：觸發(fā)時間

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER } <--
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)的SQL語句>

觸發(fā)器有執(zhí)行的時間設(shè)置：可以設(shè)置為事件發(fā)生前或后。

3. Syntax: Event語法：事件

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } <--
ON <表名稱>
FOR EACH ROW
<觸發(fā)的SQL語句>

同樣也能設(shè)定觸發(fā)的事件：它們可以在執(zhí)行insert、update或delete的過程中觸發(fā)。

4. Syntax: Table 語法：表

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱> <--
FOR EACH ROW
<觸發(fā)的SQL語句>

觸發(fā)器是屬于某一個表的:當(dāng)在這個表上執(zhí)行插入、更新或刪除操作的時候就導(dǎo)致觸發(fā)器的激活.
我們不能給同一張表的同一個事件安排兩個觸發(fā)器。

5. Syntax: Granularity 語法：（ ：（ 步長）觸發(fā)間隔

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW <--
<觸發(fā)的SQL語句>

觸發(fā)器的執(zhí)行間隔：FOR EACH ROW子句通知觸發(fā)器每隔一行執(zhí)行一次動作，而不是對整個表執(zhí)行一次。


6. Syntax: Statement 語法：語句

CREATE TRIGGER <觸發(fā)器名稱>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名稱>
FOR EACH ROW
<觸發(fā)的SQL語句> <--

觸發(fā)器包含所要觸發(fā)的SQL語句：這里的語句可以是任何合法的語句，包括復(fù)合語句，但是這里的語句受的限制和函數(shù)的一樣。

Privileges權(quán)限

你必須擁有相當(dāng)大的權(quán)限才能創(chuàng)建觸發(fā)器（CREATE TRIGGER）。如果你已經(jīng)是Root用戶，那么就足夠了。這跟SQL的標(biāo)準(zhǔn)有所不同，我也希望能盡快改成標(biāo)準(zhǔn)的。

因此在下一個版本的MySQL中，你完全有可能看到有一種叫做CREATE TRIGGER的新權(quán)限。然后通過這樣的方法賦予：

GRANT CREATE TRIGGER ON <表名稱> TO <用戶或用戶列表>;

也可以通過這樣收回權(quán)限：

REVOKE CREATE TRIGGER ON <表名稱> FROM <用戶或用戶列表>;

Referring to OLD and NEW columns 關(guān)于舊的和新創(chuàng)建的列的標(biāo)識

在觸發(fā)器的SQL語句中，你可以關(guān)聯(lián)表中的任意列。但你不能僅使用列的名稱去標(biāo)識，那會使系統(tǒng)混淆，因為那里可能會有列的新名（這可能正是你要修改的，你的動作可能正是要修改列名），還有列的舊名存在。因此你必須用這樣的語法來標(biāo)識：

"NEW . column_name"或者"OLD . column_name".這樣在技術(shù)上處理（NEW | OLD . column_name）新和舊的列名屬于創(chuàng)建了過渡變量（"transition variables"）。

對于INSERT語句,只有NEW是合法的；對于DELETE語句，只有OLD才合法；而UPDATE語句可以在和NEW以及OLD同時使用。下面是一個UPDATE中同時使用NEW和OLD的例子。

CREATE TRIGGER t21_au
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD . s1;
SET @new = NEW.s1;
END;//

現(xiàn)在如果t21表中的s1列的值是55，那么執(zhí)行了"UPDATE t21 SET s1 = s1 + 1"之后@old的值會變成55，而@new的值將會變成56。


Example of CREATE and INSERT CREATE和INSERT的例子

CREATE table with trigger創(chuàng)建有觸發(fā)器的表

這里所有的例程中我都假定大家的分隔符已經(jīng)設(shè)置成//（DELIMITER //）。

CREATE TABLE t22 (s1 INTEGER)//

CREATE TRIGGER t22_bi
BEFORE INSERT ON t22
FOR EACH ROW
BEGIN
SET @x = 'Trigger was activated!';
SET NEW.s1 = 55;
END;//

在最開始我創(chuàng)建了一個名字為t22的表，然后在表t22上創(chuàng)建了一個觸發(fā)器t22_bi，當(dāng)我們要向表中的行插入時，觸發(fā)器就會被激活，執(zhí)行將s1列的值改為55的動作。

INSERT on table w ith a trigger使用觸發(fā)器執(zhí)行插入動作

mysql> INSERT INTO t22 VALUES (1)//

讓我們看如果向表t2中插入一行數(shù)據(jù)觸發(fā)器對應(yīng)的表會怎么樣？

這里的插入的動作是很常見的，我們不需要觸發(fā)器的權(quán)限來執(zhí)行它。甚至不需要知道是否有觸發(fā)器關(guān)聯(lián)。

mysql> SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)

大家可以看到INSERT動作之后的結(jié)果，和我們預(yù)期的一樣，x標(biāo)記被改動了，同時這里插入的數(shù)據(jù)不是我們開始輸入的插入數(shù)據(jù)，而是觸發(fā)器自己的數(shù)據(jù)。
Example of a "check" constraint
"check"完整性約束例子

What's a "check" constraint 什么是"check"約束

在標(biāo)準(zhǔn)的SQL語言中，我們可以在（CREATE TABLE）創(chuàng)建表的過程中使用"CHECK (condition)"，
例如：
CREATE TABLE t25
(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;

這里CHECK的意思是"當(dāng)s2列的最左邊的字符不是'A'時，insert和update語句都會非法"，MySQL的視圖不支持CHECK，我個人是很希望它能支持的。但如果你很需要在表中使用這樣的功能，我建議大家使用觸發(fā)器來實現(xiàn)。

CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

CREATE TRIGGER t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

我只需要使用BEFORE INSERT和BEFORE UPDATE語句就行了，刪除了觸發(fā)器不會對表有影響，同時AFTER的觸發(fā)器也不能修改NEW的過程變量（transitio