MySQL 存儲過程實戰詳解：創建、調用、分頁與 PHP 多結果集處理

存儲過程優勢

• 預編譯執行，性能優於動態 SQL
• 減少網絡傳輸（批量操作單次調用）
• 封裝業務邏輯，提升安全性與可維護性

基礎實戰：用戶註冊存儲過程

1. 建庫建表

CREATE DATABASE db_proc;
USE db_proc;

CREATE TABLE proc_test (
    id TINYINT(4) NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. 創建存儲過程

DELIMITER //
CREATE PROCEDURE mytest(IN p_name VARCHAR(20), IN p_pwd VARCHAR(20))
BEGIN
    INSERT INTO proc_test(username, password) VALUES(p_name, p_pwd);
END //
DELIMITER ;

3. 調用與驗證

CALL mytest('測試用戶', 'password123');
SELECT * FROM proc_test WHERE username = '測試用戶';

4. 刪除存儲過程

DROP PROCEDURE IF EXISTS mytest;

通用分頁存儲過程（生產級）

DROP PROCEDURE IF EXISTS pr_pager;
DELIMITER //
CREATE PROCEDURE pr_pager(
    IN p_table_name VARCHAR(1024),
    IN p_fields VARCHAR(1024),
    IN p_page_size INT,
    IN p_page_now INT,
    IN p_order_string VARCHAR(128),
    IN p_where_string VARCHAR(1024),
    OUT p_out_rows INT
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '通用分頁存儲過程'
BEGIN
    DECLARE m_begin_row INT DEFAULT 0;
    DECLARE m_limit_string CHAR(64);
    
    SET m_begin_row = (p_page_now - 1) * p_page_size;
    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);
    
    SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);
    
    PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
    
    SET p_out_rows = @ROWS_TOTAL;
    
    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
END //
DELIMITER ;

調用示例

-- 基礎分頁
CALL pr_pager('user', '*', 15, 1, '', 'WHERE id > 3', @count_rows);

-- 多表連接分頁
CALL pr_pager('job j LEFT JOIN enter_job ej ON j.job_no=ej.job_no', 'j.*,ej.*', 25, 1, 'GROUP BY ej.put_away_user ORDER BY ej.put_away_user DESC', 'WHERE j.job_table="enter"', @p_out_rows);

-- 獲取總數
SELECT @count_rows;

PHP 調用多結果集關鍵配置

存儲過程返回多個結果集（狀態 + 數據），需啟用 CLIENT_MULTI_RESULTS：

define('CLIENT_MULTI_RESULTS', 131072);

$link = mysql_connect('127.0.0.1', 'root', '', true, CLIENT_MULTI_RESULTS)
    or die('連接失敗：' . mysql_error());

// 調用分頁存儲過程
$rs = mysql_query("CALL pr_pager('change_monitor', '*', 10, 1, '', '', @p_out_rows)");
while ($row = mysql_fetch_assoc($rs)) {
    echo $row['Schedule'] . "
";
}

// 獲取輸出參數
$q = mysql_query("SELECT @p_out_rows");
$v = mysql_fetch_assoc($q);
echo "總記錄數：{$v['@p_out_rows']}";

現代建議： 新專案請使用 mysqli 或 PDO，原生支援多結果集（mysqli::multi_query / PDO::nextRowset）。