MySQL 預存程序語法錯誤與 PREPARE 語句限制完整解析

問題背景

在使用 phpMyAdmin 建立 MySQL 預存程序時，開發者常會遇到語法錯誤。最初嘗試在 phpMyAdmin 中執行帶有 BEGIN ... END 區塊的程式碼，卻出現大量報錯；移除 BEGIN 和 END 後雖能建立成功，但呼叫預存程序時在 phpMyAdmin 中卻無法執行，必須改用 MySQL 命令列工具才行。更棘手的是，當在預存程序中引入預處理語句（PREPARE）後，即便在命令列下也無法建立，報錯 ERROR 1314 (0A000): PREPARE is not allowed in stored procedures。

錯誤重現與分析

錯誤一：LIMIT 子句語法錯誤

mysql> CREATE PROCEDURE catalog_get_products_in_category(
    -> IN inCategoryId INT, IN inShortProductDescriptionLength INT,
    -> IN inProductsPerPage INT, IN inStartItem INT)
    -> begin
    -> SELECT p.product_id, p.name,
    IF(LENGTH(p.description) <= inShortProductDescriptionLength, p.description,
    -> CONCAT(LEFT(p.description, inShortProductDescriptionLength),'...')) AS description,
    p.price, p.discounted_price, p.thumbnail
    -> FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id
    -> WHERE pc.category_id = inCategoryId
    -> ORDER BY p.display DESC
    -> LIMIT inStartItem;inProductsPerPage;
    -> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'inStartItem;inProductsPerPage; end' at line 10

原因：LIMIT 子句接受兩個參數時，必須用逗號分隔（LIMIT offset, row_count），不能用分號。此外，預存程序本體建議使用 DELIMITER 變更結束符號，避免分號衝突。

錯誤二：PREPARE 不允許在預存程序中使用

mysql> delimiter $$
mysql> CREATE PROCEDURE catalog_get_products_in_category(
    -> IN inCategoryId INT, IN inShortProductDescriptionLength INT,
    -> IN inProductsPerPage INT, IN inStartItem INT)
    -> BEGIN
    -> PREPARE statement FROM
    -> "SELECT p.product_id, p.name, IF(LENGTH(p.description) <= ?, p.description,
    -> CONCAT(LEFT(p.description, ?),'...')) AS description, p.price, p.discounted_price, p.thumbnail
    -> FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id
    -> WHERE pc.category_id = ?
    -> ORDER BY p.display DESC
    -> LIMIT ?, ?";
    -> SET @p1 = inShortProductDescriptionLength;
    -> SET @p2 = inShortProductDescriptionLength;
    -> SET @p3 = inCategoryId;
    -> SET @p4 = inStartItem;
    -> SET @p5 = inProductsPerPage;
    -> EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
    -> END$$
ERROR 1314 (0A000): PREPARE is not allowed in stored procedures

原因：MySQL 5.0 到 5.6 版本均 禁止在預存程序中使用 PREPARE/EXECUTE/DEALLOCATE PREPARE。這是刻意的限制，官方文件已明確說明。若需動態 SQL，必須在應用層（PHP、Java 等）組裝參數化查詢，或升級至 MySQL 8.0 並改用 EXECUTE IMMEDIATE（僅支援特定場景）。

正確寫法示範

1. 修正 LIMIT 語法並加上 DELIMITER

DELIMITER $$
CREATE PROCEDURE catalog_get_products_in_category(
    IN inCategoryId INT,
    IN inShortProductDescriptionLength INT,
    IN inProductsPerPage INT,
    IN inStartItem INT
)
BEGIN
    SELECT p.product_id, p.name,
           IF(LENGTH(p.description) <= inShortProductDescriptionLength,
              p.description,
              CONCAT(LEFT(p.description, inShortProductDescriptionLength), '...')
           ) AS description,
           p.price, p.discounted_price, p.thumbnail
    FROM product p
    INNER JOIN product_category pc ON p.product_id = pc.product_id
    WHERE pc.category_id = inCategoryId
    ORDER BY p.display DESC
    LIMIT inStartItem, inProductsPerPage;
END$$
DELIMITER ;

2. 避開 PREPARE 限制：直接使用參數化查詢

MySQL 預存程序本身已支援參數傳遞，無需動態 SQL：

DELIMITER $$
CREATE PROCEDURE catalog_get_products_in_category(
    IN inCategoryId INT,
    IN inShortProductDescriptionLength INT,
    IN inProductsPerPage INT,
    IN inStartItem INT
)
BEGIN
    SELECT p.product_id, p.name,
           IF(LENGTH(p.description) <= inShortProductDescriptionLength,
              p.description,
              CONCAT(LEFT(p.description, inShortProductDescriptionLength), '...')
           ) AS description,
           p.price, p.discounted_price, p.thumbnail
    FROM product p
    INNER JOIN product_category pc ON p.product_id = pc.product_id
    WHERE pc.category_id = inCategoryId
    ORDER BY p.display DESC
    LIMIT inStartItem, inProductsPerPage;
END$$
DELIMITER ;

呼叫方式：

CALL catalog_get_products_in_category(5, 100, 20, 0);

最佳實踐建議

• 建立預存程序時，永遠先執行 DELIMITER $$，結束後再 DELIMITER ;。
• LIMIT offset, row_count 必須用逗號，不可用分號。
• MySQL 5.7 及更早版本 不支援 預存程序內的 PREPARE，請在應用程式層處理動態 SQL。
• 若確實需要動態 SQL，可考慮升級至 MySQL 8.0 並使用 EXECUTE IMMEDIATE，或改用應用層參數化查詢。
• phpMyAdmin 執行多行 SQL 時，請在「SQL」分頁勾選「允許多查詢」或直接使用命令列工具。

常見問題快速參考

掌握以上要點，即可避開 MySQL 預存程序開發中最常見的語法陷阱與版本限制，提升開發效率。