MySQL觸發器的動作自動化

核心要點

• MySQL觸發器通過自動化操作（如數據庫查詢、文件操作和數據處理）簡化PHP項目。它們在表上執行操作（插入、更新、刪除）之前或之後自動調用。
• 觸發器在MySQL 5.0.2版本中引入，需要相應的權限才能創建。它們在創建它們的數據庫中必須具有唯一名稱，並且僅在執行原始SQL語句時才會觸發。
• 觸發器有助於維護一組表的完整性，在新的插入/刪除時自動增加或減少統計表，記錄對數據庫內數據的更改，以及保持表與其他表同步。
• MySQL觸發器可以對網站的性能產生積極影響，並使開發人員無需編寫大量的PHP代碼來處理操作。它們可用於自動執行響應特定數據更改的任務。

我們編寫的許多代碼都是為了執行某個操作。無論是數據庫查詢、文件操作、數據處理等等，所有這些都是為了使我們的腳本能夠實現其既定目的。但是，您是否注意到有時您需要編寫多少代碼來驗證之前的某個操作？我最近的一個項目涉及一個相當麻煩的問題，這導致我使用了無數的查詢，只是為了確保在每次操作後所有數據在我的表中都保持同步。這遠非優雅，本來應該是一個相當簡單的腳本，卻變成了一個複雜的查詢頁面。從維護的角度來看，這是不可行的，當我想要更新頁面功能的一部分時，這簡直是一場噩夢。正是這裡，MySQL觸發器進入了我的項目。通過讓MySQL通過觸發器做更多工作，我的項目的PHP端得到了極大的簡化。因此，本文的目的是讓您深入了解MySQL觸發器的創建和使用，以便在閱讀結束時，您可以在自己的項目中使用它們。

什麼是MySQL觸發器？

觸發器在MySQL 5.0.2版本中引入，只是MySQL增加的功能之一，可以幫助我們作為開發人員簡化工作。它們在表上執行操作（插入、更新、刪除）之前或之後自動調用。您需要擁有相應的權限才能創建觸發器。在MySQL 5.1.6之前，您需要SUPER權限，但在5.1.6中，這發生了變化，您需要TRIGGER權限。通常，共享主機計劃不允許SUPER，因為它很容易被濫用，因此您可能只能在您擁有更多權限的服務器上使用它們，例如（虛擬）專用服務器或您的本地主機，具體取決於您使用的MySQL版本。以下是一些關於觸發器的其他快速說明：

• 它們在創建它們的數據庫中必須具有唯一的（不區分大小寫）名稱。
• 每個表只允許一個具有相同事件（更新/插入/刪除）和時間（之前/之後）的觸發器。
• 刪除表時，與其關聯的觸發器也會被刪除。
• 您無法使用ALTER語句顯式更改觸發器（與事件不同）。您需要刪除觸發器並重新創建它。
• 只有在執行原始SQL語句時才會觸發觸發器；例如，外鍵關係刪除不會激活觸發器。

現在讓我們通過將其分解為原始形式來仔細查看觸發器的基本語法：

<code class="language-sql">CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END</code>

創建觸發器時，您可以選擇它是在操作發生之前還是之後觸發；您選擇哪個將完全取決於您使用它們的情況。如果您要修改進入數據庫的傳入數據，則需要BEFORE。但是，如果您要執行某個操作是因為之前的操作，則應使用AFTER語句。將觸發觸發器的操作可以是INSERT、UPDATE或DELETE，因為這三個語句是唯一會導致修改表內數據的語句。

將觸發器應用於實際情況

觸發器在許多情況下都非常有用。一個眾所周知的用法是通過在未使用外鍵時觸發過時記錄的刪除來維護一組表的完整性。它們還可以用於在新的插入/刪除時自動增加或減少統計表，記錄對數據庫內數據的更改，保持表與其他表同步等等。在本文中，我們將使用它們來預處理一些計算。這種情況是，我們有一家公司以每小時30英鎊的價格出租其大廳。他們記錄在該大廳舉行的每個活動的名稱、開始時間和結束時間，然後在收入表中計算時間和應付費用。事件的名稱和開始時間最初被插入到事件表中以預訂大廳，然後只有在活動結束後，才會在該行上更新租賃成本。需要計算事件的持續時間（以分鐘為單位），其中開始時間將從結束時間中減去，然後租賃費用將通過將總時間乘以0.5（每小時30英鎊，每分鐘50便士）來計算。我們可以在更新事件信息時使用PHP執行事件持續時間和費用的計算（通過選擇插入的數據，計算持續時間和租賃費用，然後插入或更新收入表），或者我們可以簡單地使用觸發器來自動化此過程並減少一些PHP代碼。讓我們設置兩個基本表並將一些虛擬預訂數據插入事件以開始操作。

<code class="language-sql">CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);</code>

設置好兩個表後，我們可以繼續創建名為CostCalc的觸發器。觸發器設置為在事件表上發生更新後觸發，然後執行前面提到的計算。然後它插入或更新（如果預先存在的事件ID已設置）收入表。

<code class="language-sql">CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END</code>

創建觸發器時（與事件和存儲例程類似），我們需要做的第一件事是指定一個新的分隔符，它表示觸發器的結尾。這是使用DELIMITER關鍵字完成的，後面跟著一個自定義符號（或符號），並且需要將觸發器作為一個整體執行，而不是MySQL只是單獨執行內部的語句。然後，我們指定觸發器的名稱、其時間、事件以及它將被設置為在其上觸發的表。在此示例中，我們將觸發器的時間設置為在UPDATE語句發生後起作用，因為我們只想在成功更新後執行觸發器；否則，我們將重複該事件的先前記錄。接下來，我們使用BEGIN...END複合語句來容納觸發器的功能。觸發器的正文首先聲明兩個變量：rows和time。我們從事件表中選擇行數，其中ID引用剛剛修改的行，並且事件_start和event_end時間中的一個（或兩個）已被修改，以及event_end時間不等於零。這是為了澄清是否需要對收入表進行任何操作，因為只有通過這些更改才能計算租賃費用。一旦我們知道我們可以計算時間和費用，我們將time變量設置為等於從事件的開始到結束列的分鐘數。通過將這個數字乘以0.5，我們也可以得到租賃成本。由於event_id列是唯一的，我們只能有一個ID對應於事件表；因此，我們使用REPLACE來更新表中預先存在的行（如果有）或插入新行（如果沒有）。在MySQL語句中，您可能還注意到在上面的SELECT和REPLACE語句中以及時間變量值的表達式中使用了關鍵字OLD和NEW。您使用這兩個關鍵字將取決於您情況的事件以及觸發器觸發的時間。

• NEW關鍵字用於訪問進入數據庫的傳入數據。這僅在INSERT和UPDATE語句中可用。
• OLD關鍵字用於訪問在對記錄進行任何修改之前記錄中的當前數據。這僅在UPDATE和DELETE語句中可用。

將用於啟動觸發器的相應PHP腳本將包括一個類（稱為EventHandler），以及我們的客戶端調用代碼。該類將通過PDO連接到我們的MySQL數據庫，並將包含一個方法updateEvent()，該方法將在需要更新事件內容時調用。

<code class="language-sql">CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END</code>

我們首先創建我們的EventHandler類，其中屬性$db被定義為保存PDO類的實例，該實例通過構造方法設置。然後，我們繼續製作我們的updateEvent()方法，其中定義了三個參數。第一個參數指定我們想要在事件表中更新的列，並且允許三個值之一：name、start、end。第二個參數保存要插入的值或更新列的當前值；而第三個參數保存要更新的元組的ID。確保列名有效後，我們通過參數化查詢查詢我們的表，最後檢查是否有任何行被更新。創建類後，我們繼續調用它。我們將PDO對象的實例作為參數傳遞給EventHandler類。然後，updateEvent()方法將使用不同的值調用四次，以顯示我們的觸發器將如何對在事件表上進行的更新做出反應。前兩次更新不會導致我們的觸發器插入或更新行，因為我們仍然沒有計算事件持續時間和租賃費用所需的信息。我們所做的只是更新事件名稱並將它的開始時間推遲兩天。然而，接下來的兩次更新將需要我們的觸發器的功能，因為第一次更新定義了結束時間，第二次更新將結束時間重新定義為一個小時後，從而導致持續時間發生變化，因此租賃費用也發生變化。這就是我們需要REPLACE語句的地方，因為我們在創建表時對錶施加了約束，我們每個事件ID只能有一條記錄。

結束語

MySQL觸發器如果使用得當，不僅可以對網站的性能產生積極影響，還可以避免編寫大量的PHP代碼來處理此類操作。我希望您在項目中發現它們像我在我的項目中一樣有用，因此請隨時大膽使用觸發器！ 圖片來自Fotolia

關於使用MySQL觸發器進行操作自動化的常見問題解答

什麼是MySQL觸發器，它是如何工作的？

MySQL觸發器是一個存儲程序，它會自動響應表中發生的事件（例如插入、更新或刪除）而被調用。觸發器用於維護數據庫中信息的完整性，並且在表上發生特定操作時會自動調用。它們可以在事件之前或之後執行。

如何創建MySQL觸發器？

創建MySQL觸發器涉及CREATE TRIGGER語句，其中包括觸發器的名稱、觸發事件以及事件發生時要執行的語句。這是一個基本示例：

<code class="language-sql">CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);</code>

我可以從MySQL觸發器調用PHP腳本嗎？

不能直接調用。 MySQL不支持從觸發器調用PHP腳本。但是，您可以間接地通過使用UDF（用戶定義函數）或使用外部系統來監視數據庫中的更改，然後調用PHP腳本來實現這一點。

MySQL觸發器的語法是什麼？

在MySQL中創建觸發器的語法如下：

<code class="language-sql">CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END</code>

如何使用MySQL自動化任務？

MySQL中的自動化可以通過觸發器、存儲過程和事件來實現。觸發器可用於自動化響應特定數據更改而應執行的任務。存儲過程允許您將一系列命令封裝到單個可調用例程中。事件是根據計劃運行的任務。

MySQL觸發器的局限性是什麼？

MySQL觸發器的一些局限性包括：它們只能與單個表關聯，它們不能返回結果集，它們不能接受參數，並且不能像存儲過程那樣直接調用。

如何調試MySQL觸發器？

調試MySQL觸發器可能具有挑戰性，因為沒有內置調試器。但是，您可以使用變通方法，例如將值插入單獨的表以跟踪執行流程，或者使用MySQL Debugger之類的第三方工具。

MySQL觸發器可以調用存儲過程嗎？

可以。但是，您在執行此操作時應謹慎，因為它可能導致難以管理和調試的複雜事件鏈。

如何刪除MySQL觸發器？

您可以使用DROP TRIGGER語句，後跟觸發器的名稱來刪除MySQL觸發器。例如：

<code class="language-sql">CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);</code>

MySQL觸發器可以用於數據驗證嗎？

可以。您可以創建一個觸發器來檢查數據是否在插入或更新到表中之前，並相應地採取措施。

以上是MySQL觸發器的動作自動化的詳細內容。更多資訊請關注PHP中文網其他相關文章！