MySQL 中的預存程序：完整指南

最後更新： 5月26 2025
作者： 泰克諾數碼
  • MySQL 中的預存程序將 SQL 語句分組為可重複使用的單元。
  • 它們提高了在伺服器上運行時的效能，減少了網路流量。
  • 它們透過特定權限控制資料訪問，從而提供更高的安全性。
  • 它們透過使用索引和避免使用遊標來促進效能最佳化。
MySQL 中的預存程序

MySQL 中的預存程序是什麼？

MySQL 中的預存程序是一系列腳本或 SQL 程式碼區塊，它們儲存在資料庫伺服器上，並在被呼叫時執行。預存程序是一種強大的方法，可以將相關的 SQL 語句組合成一個可重複使用的邏輯單元。預存程序簡化了程式設計邏輯，提高了效能，並增強了資料庫安全性。

使用預存程序的優點

預存程序在應用程式開發和資料庫管理方面具有許多優勢。其主要優點如下：

  1. 模組化和程式碼重用預存程序可讓您將相關的 SQL 語句分組為邏輯單元，從而更容易在應用程式的不同部分中重複使用它們。這提高了程式碼的可維護性並減少了程式碼重複。
  2. 更好的表現：在執行時 資料庫伺服器 對於資料存儲，預存程序避免了從客戶端應用程式發送多個查詢的需要。這減少了網路流量並提高了整體應用程式的效能。
  3. 安全預存程序可用於控制對資料的存取並執行特定的安全規則。可以將預存程序的執行權限指派給使用者角色，從而提供額外的安全性等級。
  4. 錯誤減少透過將程式邏輯封裝在預存程序當中，可以減少應用程式出現錯誤的可能性。這是因為預存程序只需測試和調試一次，然後就可以被多個應用程式使用，而無需修改原始程式碼。

建立儲存過程

在 MySQL 中建立預存程序是一個相對簡單的過程。若要建立預存程序，請使用下列語句 CREATE PROCEDURE。以下是建立顯示表中所有記錄的預存程序的基本範例：

  MySQL 資料類型：特性與範例

建立過程 sp_show_records()
開始
從表中選擇*；
結束

在這個例子中， sp_mostrar_registros 是預存程序的名稱。區塊 BEGIN y END 定義過程主體，在本例中由一個簡單的查詢組成 SELECT 顯示名為“table”的表中的所有記錄。

預存程序的參數

預存程序可以接受參數，從而在執行時接收外部值。參數在過程聲明中定義，並在過程體中使用。以下是一個接受兩個參數並執行條件查詢的預存程序範例：

建立過程 sp_search_product(IN 名稱 VARCHAR(50)，IN 價格 DECIMAL(8,2))
開始
SELECT * FROM 產品 WHERE name LIKE CONCAT('%', name, '%') AND price <= price;
結束

在此範例中，預存程序 sp_buscar_producto 接受兩個參數： nombre y precio。諮詢 SELECT 在製程主體中，使用這些參數根據部分名稱和最高價格過濾「產品」表中的記錄。

局部變數和流控制

MySQL 中的預存程序可以使用局部變數和流控制結構，例如條件和循環。這允許在儲存過程內進行更複雜和有條件的操作。以下是使用變數和流程控制的預存程序的範例：

建立過程 sp_actualizar_stock(IN Producto_id INT, IN cantidad INT)
開始
聲明 stock_actual INT；

從產品中選擇庫存到目前庫存，其中 id = product_id；

如果實際庫存 >= 數量，則
更新產品設定庫存 = 庫存 - 數量 WHERE id = product_id;
ELSE
選擇“庫存不足”作為訊息；
萬一;
結束

在此範例中，預存程序 sp_actualizar_stock 採用兩個參數： producto_id y cantidad。使用名為 stock_actual 儲存產品的當前庫存價值。然後使用控制結構 IF 檢查庫存是否充足，並在「產品」表中執行更新，否則顯示錯誤訊息。

儲存過程內的函數

除了執行 SQL 查詢之外，MySQL 中的預存程序還可以包含函數。函數允許您執行計算並傳回值，而不僅僅是顯示查詢結果。以下是一個使用函數計算訂單總價的預存程序範例：

  資料庫規範化：完整指南和逐步範例

建立函數 fn_calculate_total_price(order_id INT) 傳回 DECIMAL(8,2)
開始
聲明總計 DECIMAL(8,2)；

從 order_detail 中選擇 SUM(price * 數量) 到總計，其中 order_id = order_id；

返回總計；
結束

在此範例中，預存程序包含一個名為 fn_calcular_precio_total 接受參數 pedido_id 並回傳訂單總價。該函數使用局部變數 total 儲存計算結果，然後使用語句返回 RETURN.

預定義預存程序

MySQL 提供了一系列預先定義的預存流程，涵蓋了各種常見任務。這些預存程序可以直接在資料庫中使用，無需編寫額外的程式碼。一些最常用的預定義預存程序包括：

  • COUNT()：傳回符合指定條件的行數。
  • SUM()：計算指定列中的值的總和。
  • AVG()：計算指定列中的值的平均值。
  • MAX()：傳回指定列中的最大值。
  • MIN()：傳回指定列中的最小值。

這些預先定義的預存程序經過高度最佳化，與從頭開始編寫等效 SQL 查詢相比，效能有所提升。

安全性和執行權限

在 MySQL 中，可以將預存程序的執行權限指派給使用者角色。這樣可以控制對預存程序的訪問，並保護資料的完整性和安全性。執行權限透過 MySQL 的使用者和權限管理系統進行管理。

為儲存程序分配適當的執行權限至關重要，這可以防止未經授權的存取並確保資料機密性。資料庫管理員應嚴格分配執行權限，並定期審查使用者權限，以維護安全的環境。

最佳化儲存過程

為了確保最佳效能，優化 MySQL 中的預存程序非常重要。一些常見的優化技術包括：

  1. 使用索引：在預存程序內查詢中使用的列上的索引可以顯著提高效能。索引透過減少預存程序的執行時間來加快資料搜尋和檢索速度。
  2. 限制遊標的使用：過度使用遊標可能會對預存程序的效能產生負面影響。相反，建議使用集合操作，例如 JOIN y GROUP BY 來操作數據，而不是逐行循環。
  3. 避免過度使用子查詢：子查詢在某些情況下很有用，但過度使用可能會導致效能不佳。相反，建議使用 JOIN y GROUP BY 有效地組合和處理數據。
  4. 執行測試和調整：執行徹底的效能測試並根據需要調整預存程序非常重要。這涉及到識別瓶頸、測量執行時間以及更改程序的設計或邏輯以提高效能。
  MySQL 與MariaDB：堂兄弟之間的決鬥

結論

總之，MySQL 中的預存程序是簡化程式邏輯、提高效能以及增強應用程式開發和資料庫管理的安全性的強大工具。它們允許將相關的 SQL 語句分組為邏輯且可重複使用的單元，提供模組化、程式碼重複使用、更好的效能和安全性等優勢。

在 MySQL 中建立預存程序很簡單，使用語句 CREATE PROCEDURE，並且可以接受參數以適應不同的場景。此外，預存程序可以包含局部變數、流控制和函數，使其更加靈活和強大。

使用索引、限制遊標和子查詢的使用以及執行徹底的效能測試等技術來優化預存程序非常重要。這可確保最終用戶獲得最佳效能和高效體驗。

總而言之，MySQL 中的預存程序對於開發人員和資料庫管理員來說是一個有價值的工具，其正確的實作和最佳化可以對應用程式和資料庫系統的效能和安全性產生影響。

MySQL 中的預存程序
相關文章：
MySQL 中的預存程序:如何使用它們？