萬盛學電腦網

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

MySQL 增刪改查 (CRUD) 核心語法與 PHP mysqli/PDO 實戰全指南

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 評估)
copyright © 萬盛學電腦網 all rights reserved