C語言操作 MySQL：結構體資料參數化插入實戰

痛點：手動拼裝 SQL 字串易錯、有注入風險

結構體：

typedef struct {
    int id;
    char name[16];
    char sex;
    char birth[12];
    char dept[16];
    int salary;
} Member;

方案：預處理語句 + 參數綁定 (MySQL C API)

#include <mysql.h>
#include <stdio.h>
#include <string.h>

int insert_member(MYSQL *conn, const Member *m)
{
    MYSQL_STMT *stmt = mysql_stmt_init(conn);
    if (!stmt) return -1;

    const char *sql = "INSERT INTO members (name, sex, birth, dept, salary) VALUES (?, ?, ?, ?, ?)";
    if (mysql_stmt_prepare(stmt, sql, strlen(sql))) {
        fprintf(stderr, "Prepare failed: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    MYSQL_BIND bind[5] = {0};
    // name
    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)m->name;
    bind[0].buffer_length = strlen(m->name);
    // sex
    bind[1].buffer_type = MYSQL_TYPE_TINY;
    bind[1].buffer = (char *)&m->sex;
    // birth
    bind[2].buffer_type = MYSQL_TYPE_STRING;
    bind[2].buffer = (char *)m->birth;
    bind[2].buffer_length = strlen(m->birth);
    // dept
    bind[3].buffer_type = MYSQL_TYPE_STRING;
    bind[3].buffer = (char *)m->dept;
    bind[3].buffer_length = strlen(m->dept);
    // salary
    bind[4].buffer_type = MYSQL_TYPE_LONG;
    bind[4].buffer = (void *)&m->salary;

    if (mysql_stmt_bind_param(stmt, bind)) {
        fprintf(stderr, "Bind failed: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "Execute failed: %s\n", mysql_stmt_error(stmt));
        return -1;
    }

    mysql_stmt_close(stmt);
    return 0;
}

優勢

• **安全**：參數與 SQL 碼分離，杜絕注入。
• **效能**：預處理一次，多次執行只傳參數。
• **型別安全**：API 強制指定 MYSQL_TYPE_*，避免格式字串錯誤。

注意

• 字串欄位需確保以 \0 結尾，長度不含結尾符。
• 數值型別 (int, long long) 需對應 MYSQL_TYPE_LONG/LONGLONG。
• 編譯連結：-lmysqlclient (Linux) / libmysql.lib (Windows)。