INSERT 插入數據
標準語法
INSERT INTO tbl (col1, col2, col3) VALUES (val1, val2, val3);
INSERT INTO tbl (col1, col2) VALUES (v11, v12), (v21, v22), (v31, v32); -- 批次插入
INSERT INTO tbl SELECT col1, col2 FROM other_tbl WHERE condition; -- 查提插入
命令行示例
mysql> USE tutorials;
mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date)
-> VALUES ('Learn PHP', 'John Poul', NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date)
-> VALUES ('Learn MySQL', 'Abdul S', NOW()),
-> ('JAVA Tutorial', 'Sanjay', '2007-05-06');
Query OK, 2 rows affected (0.01 sec)
注意: AUTO_INCREMENT 列無需指定;NOW() 返回當前 datetime;字符串值**必須用引號包裹**。
PHP mysqli 預處理插入(防注入、高性能)
<?php
$mysqli = new mysqli("localhost", "root", "rootpassword", "tutorials", 3306);
if ($mysqli->connect_errno) die("連接失敗: " . $mysqli->connect_error);
$mysqli->set_charset("utf8mb4");
$title = $_POST['tutorial_title'] ?? '';
$author = $_POST['tutorial_author'] ?? '';
$date = $_POST['submission_date'] ?? date('Y-m-d');
$stmt = $mysqli->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $title, $author, $date);
if ($stmt->execute()) {
echo "插入成功,新 ID: " . $mysqli->insert_id;
} else {
die("插入失敗: " . $stmt->error);
}
$stmt->close();
$mysqli->close();
?>
PDO 事務批次插入
<?php
$pdo = new PDO("mysql:host=localhost;dbname=tutorials;charset=utf8mb4", "root", "rootpassword", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$data = [
['Learn PHP', 'John Poul', '2024-01-15'],
['Learn MySQL', 'Abdul S', '2024-01-16'],
['JAVA Tutorial', 'Sanjay', '2024-01-17'],
];
$pdo->beginTransaction();
$stmt = $pdo->prepare("INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)");
foreach ($data as $row) {
$stmt->execute($row);
}
$pdo->commit();
echo "批次插入 " . count($data) . " 條記錄成功";
?>
SELECT 查詢數據
核心語法結構
SELECT [DISTINCT] select_expr [, ...]
FROM table_references
[WHERE where_condition]
[GROUP BY group_expr [, ...]]
[HAVING having_condition]
[ORDER BY order_expr [ASC|DESC] [, ...]]
[LIMIT [offset,] row_count];
命令行查詢示例
mysql> SELECT * FROM tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2024-01-15 |
| 2 | Learn MySQL | Abdul S | 2024-01-16 |
| 3 | JAVA Tutorial | Sanjay | 2024-01-17 |
+-------------+----------------+-----------------+-----------------+
PHP mysqli 多種獲取模式
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
$stmt = $mysqli->prepare($sql);
$stmt->execute();
$res = $stmt->get_result();
// 1. 關聯數組 (推薦,可讀性強)
while ($row = $res->fetch_assoc()) {
echo "ID: {$row['tutorial_id']}, Title: {$row['tutorial_title']}
";
}
// 2. 數字索引數組
$res->data_seek(0);
while ($row = $res->fetch_row()) {
echo "ID: {$row[0]}, Title: {$row[1]}
";
}
// 3. 同時獲取兩種 (fetch_array 默認 MYSQLI_BOTH)
$res->data_seek(0);
while ($row = $res->fetch_array()) {
// $row['tutorial_id'] 和 $row[0] 都可用
}
// 釋放結果集內存
$res->free_result();
PDO 統一獲取
$stmt = $pdo->query("SELECT tutorial_id, tutorial_title FROM tutorials_tbl");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "{$row['tutorial_id']}: {$row['tutorial_title']}
";
}
// 或一次性獲取所有
$all = $stmt->fetchAll(PDO::FETCH_ASSOC);
DELETE 刪除數據
語法
DELETE FROM tbl [WHERE condition] [ORDER BY ...] [LIMIT row_count];
⚠️ 警告: 無 WHERE 條件將刪除**全表數據**且不可回滾(除非在事務中)。生產環境**強制要求 WHERE 條件**。
命令行示例
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id = 3;
Query OK, 1 row affected (0.23 sec)
PHP mysqli 預處理刪除
$stmt = $mysqli->prepare("DELETE FROM tutorials_tbl WHERE tutorial_id = ?");
$stmt->bind_param("i", $id);
$id = 3;
$stmt->execute();
echo "刪除行數: " . $mysqli->affected_rows;
PDO 刪除
$stmt = $pdo->prepare("DELETE FROM tutorials_tbl WHERE tutorial_id = ?");
$stmt->execute([3]);
echo "刪除行數: " . $stmt->rowCount();
UPDATE 更新數據(補充)
-- SQL
UPDATE tutorials_tbl
SET tutorial_title = 'Advanced PHP', status = 1
WHERE tutorial_id = 1;
-- PHP mysqli
$stmt = $mysqli->prepare("UPDATE tutorials_tbl SET tutorial_title = ?, status = ? WHERE tutorial_id = ?");
$stmt->bind_param("sii", $title, $status, $id);
事務控制(保證數據一致性)
<?php
$mysqli->begin_transaction();
try {
$mysqli->query("UPDATE account SET balance = balance - 100 WHERE id = 1");
$mysqli->query("UPDATE account SET balance = balance + 100 WHERE id = 2");
$mysqli->commit();
echo "轉賬成功";
} catch (Exception $e) {
$mysqli->rollback();
die("轉賬失敗,已回滾: " . $e->getMessage());
}
?>
內存釋放與資源管理
- 結果集用完即調用
$res->free_result() (mysqli) 或 $stmt->closeCursor() (PDO)。
- 語句句柄用完調用
$stmt->close()。
- 腳本結束前調用
$mysqli->close() / $pdo = null 釋放連接(PHP-FPM 持久連接除外)。
總結:現代 PHP 開發檢查清單
| 項目 | 推薦做法 |
| 擴展 | mysqli (面向對象) 或 PDO,**禁用 mysql_*** |
| 字符集 | 連接後立即 set_charset('utf8mb4') / DSN charset=utf8mb4 |
| SQL 注入防護 | **全量使用預處理語句**,絕不拼接變量 |
| 錯誤處理 | mysqli: mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);PDO: ERRMODE_EXCEPTION |
| 事務 | 多表寫入/業務邏輯原子操作**必須加事務** |
| 連接池 | PHP-FPM 使用持久連接 mysqli:p: 或 PDO::ATTR_PERSISTENT(配合 max_connections 評估) |