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 進 SQL | SQL 注入 | 使用預處理語句 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 | 表間複製 |