C/C++ 調用 MySQL 存儲過程輸入輸出參數完整實戰：MYSQL_BIND 綁定、預處理、結果集獲取封裝類

存儲過程定義

DELIMITER $$
CREATE PROCEDURE `p_InsertFace`(
    IN  p_Id       BIGINT,
    IN  p_UserID   BIGINT,
    IN  p_FeaLen   SMALLINT,
    IN  p_Fea      BLOB,
    IN  p_ImgUrl   CHAR(64),
    IN  p_Type1    INT,
    IN  p_Type2    INT,
    IN  p_Type3    INT,
    IN  p_Type4    CHAR(32),
    IN  p_Remarks  CHAR(64),
    OUT p_OID      BIGINT
)
BEGIN
    DECLARE v_Id BIGINT DEFAULT 0;
    IF p_Id > 0 THEN SET v_Id = p_Id; END IF;

    INSERT INTO t_face (ID, UserID, FeaLen, Fea, ImgUrl, Type1, Type2, Type3, Type4, Stamp, IsDel, UpdateTime, Remarks)
    VALUES (v_Id, p_UserID, p_FeaLen, p_Fea, p_ImgUrl, p_Type1, p_Type2, p_Type3, p_Type4, NOW(), 0, NOW(), p_Remarks);

    IF v_Id = 0 THEN SET p_OID = LAST_INSERT_ID(); END IF;
END$$
DELIMITER ;

C++ 封裝調用類核心代碼

HRESULT CFaceDB::RegFaceP(FACE_INFO info, ULONGLONG& nID) {
    MYSQL_STMT* stmt = mysql_stmt_init(m_pMysql);
    if (!stmt) return S_FALSE;

    // 1. 準備綁定數組 (11 個參數：10 IN + 1 OUT)
    MYSQL_BIND bind[11];
    memset(bind, 0, sizeof(bind));

    // IN 參數綁定 (索引 0-9)
    bind_param(bind[0], MYSQL_TYPE_LONGLONG, &nID);           // p_Id
    bind_param(bind[1], MYSQL_TYPE_LONGLONG, &info.nUserID);  // p_UserID
    bind_param(bind[2], MYSQL_TYPE_SHORT,   &info.nFeaLen);   // p_FeaLen
    bind_blob (bind[3], info.szFea, info.nFeaLen);            // p_Fea (BLOB)
    bind_str  (bind[4], info.szUrl);                          // p_ImgUrl
    bind_param(bind[5], MYSQL_TYPE_LONG,    &info.nType1);    // p_Type1
    bind_param(bind[6], MYSQL_TYPE_LONG,    &info.nType2);    // p_Type2
    bind_param(bind[7], MYSQL_TYPE_LONG,    &info.nType3);    // p_Type3
    bind_str  (bind[8], info.szType4);                        // p_Type4
    bind_str  (bind[9], info.szRemarks);                      // p_Remarks

    // OUT 參數綁定 (索引 10)
    bind_param(bind[10], MYSQL_TYPE_LONGLONG, &nID);          // p_OID

    // 2. 預處理 SQL
    const char* sql = "CALL P_INSERTFACE(?,?,?,?,?,?,?,?,?,?,@out_oid);";
    if (mysql_stmt_prepare(stmt, sql, strlen(sql))) goto ERR;

    // 3. 綁定參數並執行
    mysql_stmt_bind_param(stmt, bind);
    if (mysql_stmt_execute(stmt)) goto ERR;

    // 4. 獲取 OUT 參數值（MySQL 需額外 SELECT @out_oid）
    if (mysql_query(m_pMysql, "SELECT @out_oid")) goto ERR;
    MYSQL_RES* res = mysql_store_result(m_pMysql);
    if (res) {
        MYSQL_ROW row = mysql_fetch_row(res);
        if (row && row[0]) nID = strtoull(row[0], nullptr, 10);
        mysql_free_result(res);
    }

    mysql_stmt_close(stmt);
    return S_OK;

ERR:
    mysql_stmt_close(stmt);
    return S_FALSE;
}

// 輔助綁定函數
void bind_param(MYSQL_BIND& b, enum_field_types type, void* ptr, unsigned long len=0) {
    b.buffer_type = type; b.buffer = ptr; b.buffer_length = len ? len : sizeof(*(long long*)ptr); b.is_null = 0; b.length = 0;
}
void bind_blob(MYSQL_BIND& b, void* ptr, unsigned long len) {
    b.buffer_type = MYSQL_TYPE_BLOB; b.buffer = ptr; b.buffer_length = len; b.length = &len;
}
void bind_str(MYSQL_BIND& b, const char* ptr) {
    unsigned long len = strlen(ptr);
    b.buffer_type = MYSQL_TYPE_STRING; b.buffer = (char*)ptr; b.buffer_length = 64; b.length = &len;
}

關鍵技術點

1. OUT 參數獲取：MySQL C API 不直接支持 mysql_stmt_bind_result 綁定 OUT 參數，需額外 SELECT @user_var 讀取
2. BLOB 綁定：buffer_length 設置最大容量，length 指向實際長度變量
3. 字符串長度：MYSQL_TYPE_STRING 需顯式傳遞長度指針
4. 異常安全：goto ERR 統一清理 mysql_stmt_close，避免句柄洩漏

結構體定義參考

#define MAX_FEA_LEN 10240
#define MAX_URL_LEN 200

typedef struct _FACE_INFO {
    ULONGLONG nID, nUserID;
    DWORD     nFeaLen;
    TCHAR     szFea[MAX_FEA_LEN];
    TCHAR     szUrl[MAX_URL_LEN];
    DWORD     nType1, nType2, nType3;
    TCHAR     szType4[32], szStamp[20], szRemarks[64];
    _FACE_INFO() { memset(this, 0, sizeof(*this)); }
} FACE_INFO;

C/C++ 調用 MySQL 存儲過程的核心難點在於參數綁定類型對應與OUT 參數讀取機制。封裝統一的 bind_param/bind_blob/bind_str 輔助函數可大幅減少樣板代碼與類型錯誤。