萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

PHP MySQL INSERT 資料寫入完整教學:預處理語句防注入與表單實作

INSERT 基礎語法

形式一:指定欄位名 (推薦,順序無關、可跳過有預設值欄位)

INSERT INTO table_name (column1, column2, column3) 
VALUES (value1, value2, value3);

形式二:不指定欄位 (需按建表順序給值,易出錯)

INSERT INTO table_name VALUES (value1, value2, value3, ...);

PHP 實作:單筆插入 (mysqli 物件導向 + 預處理語句)

<?php
$mysqli = new mysqli('localhost', 'db_user', 'password', 'my_db');
if ($mysqli->connect_errno) die('連接失敗: ' . $mysqli->connect_error);
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// ===== 方式一:預處理語句 (防 SQL 注入,推薦) =====
$stmt = $mysqli->prepare("INSERT INTO `persons` (FirstName, LastName, Age) VALUES (?, ?, ?)");
$stmt->bind_param('ssi', $firstName, $lastName, $age);

// 繫結變數
$firstName = 'Peter';
$lastName = 'Griffin';
$age = 35;
$stmt->execute();
echo "新增 ID: " . $stmt->insert_id . "\n";

// 同一預處理語句可重複執行
$firstName = 'Glenn';
$lastName = 'Quagmire';
$age = 33;
$stmt->execute();
echo "新增 ID: " . $stmt->insert_id . "\n";

$stmt->close();

// ===== 方式二:直接查詢 (僅限可信資料,需手動轉義) =====
$firstName = $mysqli->real_escape_string("O'Reilly");
$lastName = $mysqli->real_escape_string('Smith');
$age = 28;
$sql = "INSERT INTO `persons` (FirstName, LastName, Age) VALUES ('$firstName', '$lastName', $age)";
$mysqli->query($sql);
echo "新增 ID: " . $mysqli->insert_id . "\n";

$mysqli->close();
?>

PHP 實作:批次插入 (高效能)

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$data = [
    ['Alice', 'Chen', 25],
    ['Bob', 'Wang', 30],
    ['Cindy', 'Lin', 28],
    // ... 更多資料
];

// 建構批次 INSERT
$placeholders = implode(',', array_fill(0, count($data), '(?, ?, ?)'));
$sql = "INSERT INTO `persons` (FirstName, LastName, Age) VALUES $placeholders";

$stmt = $mysqli->prepare($sql);

// 動態繫結參數
$types = str_repeat('ssi', count($data));
$params = [];
foreach ($data as $row) {
    $params[] = $row[0];
    $params[] = $row[1];
    $params[] = $row[2];
}

// 繫結參考 (PHP 需傳址)
$bindParams = array_merge([$types], $params);
$refs = [];
foreach ($bindParams as $key => $val) {
    $refs[$key] = &$bindParams[$key];
}
call_user_func_array([$stmt, 'bind_param'], $refs);

$stmt->execute();
echo "批次新增 " . $stmt->affected_rows . " 筆,最後 ID: " . $stmt->insert_id;

$stmt->close();
$mysqli->close();
?>

HTML 表單新增資料完整流程

1. 表單頁面 (create.html)

<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>新增會員</title></head>
<body>
<h2>新增會員</h2>
<form action="insert.php" method="post">
    <div>
        <label>姓名:</label>
        <input type="text" name="firstname" required maxlength="50">
    </div>
    <div>
        <label>姓氏:</label>
        <input type="text" name="lastname" required maxlength="50">
    </div>
    <div>
        <label>年齡:</label>
        <input type="number" name="age" min="1" max="120" required>
    </div>
    <button type="submit">送出</button>
</form>
</body></html>

2. 處理頁面 (insert.php)

<?php
require_once 'db_config.php'; // 包含 $mysqli 連接物件

// 1. 驗證輸入
$errors = [];
$firstname = trim($_POST['firstname'] ?? '');
$lastname = trim($_POST['lastname'] ?? '');
$age = (int)($_POST['age'] ?? 0);

if ($firstname === '') $errors[] = '姓名不可為空';
if ($lastname === '') $errors[] = '姓氏不可為空';
if ($age < 1 || $age > 120) $errors[] = '年齡需在 1-120 之間';

if ($errors) {
    http_response_code(400);
    echo "輸入錯誤:
" . implode('
', $errors); exit; } // 2. 預處理插入 try { $stmt = $mysqli->prepare("INSERT INTO `persons` (FirstName, LastName, Age) VALUES (?, ?, ?)"); $stmt->bind_param('ssi', $firstname, $lastname, $age); $stmt->execute(); echo "✅ 成功新增 1 筆資料,ID: " . $stmt->insert_id; $stmt->close(); } catch (mysqli_sql_exception $e) { http_response_code(500); error_log("INSERT Error: " . $e->getMessage()); echo "❌ 新增失敗,請稍後再試"; } $mysqli->close(); ?>

關鍵安全重點

錯誤做法風險正確做法
直接拼接 $_POST 進 SQLSQL 注入使用預處理語句 prepare() + bind_param()
未驗證輸入長度/型別資料異常、截斷、報錯伺服端嚴格驗證 + 前端輔助驗證
錯誤訊息直接輸出給用戶洩漏資料庫結構記錄詳細錯誤到日誌,用戶端顯示通用訊息
使用已棄用 mysql_* 函數PHP 7+ 不支援、無預處理改用 mysqli 或 PDO

取得自動遞增 ID

$stmt->insert_id;        // 物件導向
mysqli_insert_id($link); // 程序式
$mysqli->insert_id;      // 直接查詢後

多筆插入效能對比

方法1000 筆耗時 (約)適用場景
單筆 prepare + 循環 execute~800ms邏輯複雜、需即時取得 ID
單一大 SQL 多 VALUES~50ms批次匯入、資料單純
LOAD DATA INFILE~5ms超大量資料 (萬筆以上)
INSERT ... SELECT~20ms表間複製
copyright © 萬盛學電腦網 all rights reserved