萬盛學電腦網

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

MySQL 基礎 CRUD 與 PHP PDO 實戰:從入門到封裝資料庫操作類的完整指南

MySQL 四大核心操作 SELECT、INSERT、UPDATE、DELETE 是所有業務的基石。本文系統整理標準語法、條件運算、排序分頁、聚合函數,並提供 PHP PDO (取代已棄用 mysql_*) 連線、預處理、事務、資料讀取的現代化最佳實踐,最後封裝可復用的 Database 類。

SQL 基礎語法速查

SELECT 查詢

-- 基礎
SELECT * FROM `test` WHERE 1;
SELECT `id`, `username` FROM `test` WHERE `id` = 2;

-- 條件運算
SELECT * FROM `test` WHERE `id` IN (1,2,4);
SELECT * FROM `test` WHERE `username` LIKE '%王%';
SELECT * FROM `test` WHERE `id` BETWEEN 1 AND 3;
SELECT * FROM `test` WHERE `id` = 2 AND `remark` = '學生';

-- 排序、分組、分頁 (優先級:GROUP BY → ORDER BY → LIMIT)
SELECT * FROM `test` GROUP BY `remark`;
SELECT * FROM `test` ORDER BY `regdate` ASC, `id` ASC;
SELECT * FROM `test` LIMIT 0, 10;  -- 第 1 頁,每頁 10 筆

-- 聚合函數
SELECT COUNT(*) FROM `test`;
SELECT MAX(`id`), MIN(`regdate`), AVG(`score`), SUM(`amount`) FROM `test`;

INSERT 插入

INSERT INTO `test` (`id`, `username`, `regdate`, `remark`) 
VALUES (NULL, '小方', NOW(), '學生');  -- 自增主鍵傳 NULL

UPDATE 更新

UPDATE `test` SET `username` = '小方' WHERE `id` = 4 LIMIT 1;

DELETE 刪除

DELETE FROM `test` WHERE `id` = 3 LIMIT 1;  -- 生產環境務必加 LIMIT

PHP PDO 現代化實踐 (棄用 mysql_* 函式)

1. 連線與配置

$dsn = 'mysql:host=localhost;dbname=newdb;charset=utf8mb4';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,  // 真實預處理
];
try {
    $pdo = new PDO($dsn, 'root', 'root', $options);
} catch (PDOException $e) {
    die('連線失敗: ' . $e->getMessage());
}

2. 預處理語句 (防 SQL 注入、效能優化)

$sql = "INSERT INTO `test` (`username`, `regdate`, `remark`) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['小方', date('Y-m-d H:i:s'), '學生']);
$lastId = $pdo->lastInsertId();

3. 查詢與讀取

$stmt = $pdo->query("SELECT * FROM `test`");
while ($row = $stmt->fetch()) {
    echo $row['id'] . ' ' . $row['username'] . '
'; } // 或一次性取得所有 $rows = $stmt->fetchAll(); print_r($rows);

4. 事務控制

try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO `test` (`username`) VALUES ('A')");
    $pdo->exec("INSERT INTO `test` (`username`) VALUES ('B')");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

常用 PDO 方法速查

封裝 Database 類 (單例模式、支援鏈式呼叫)

class Database {
    private static $instance = null;
    private $pdo;

    private function __construct(array $config) {
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], $options);
    }

    public static function getInstance(array $config = []): self {
        if (self::$instance === null) {
            self::$instance = new self($config);
        }
        return self::$instance;
    }

    public function query(string $sql, array $params = []): PDOStatement {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public function execute(string $sql, array $params = []): int {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    public function lastInsertId(): string {
        return $this->pdo->lastInsertId();
    }

    public function transaction(callable $callback) {
        $this->pdo->beginTransaction();
        try {
            $result = $callback($this);
            $this->pdo->commit();
            return $result;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    // 禁止複製
    private function __clone() {}
    public function __wakeup() {}
}

// 使用範例
$db = Database::getInstance([
    'host' => 'localhost', 'dbname' => 'newdb', 
    'user' => 'root', 'pass' => 'root', 'charset' => 'utf8mb4'
]);
$db->execute("INSERT INTO `test` (`username`, `remark`) VALUES (?, ?)", ['張三', '學生']);
$rows = $db->query("SELECT * FROM `test` WHERE `remark` = ?", ['學生'])->fetchAll();
棄用 mysql_*、拥抱 PDO:預處理防注入、異常處理替代 or die、單例封裝復用連線、事務閉包保證原子性。這才是 2024+ PHP 專案的標準資料庫存取姿勢。
copyright © 萬盛學電腦網 all rights reserved
方法用途回傳值
query()執行 SELECT、無參數 SQLPDOStatement
exec()執行 INSERT/UPDATE/DELETE、DDL影響行數 (int)
prepare()預處理語句PDOStatement
lastInsertId()取得最後自增 IDstring/int
beginTransaction()開啟事務bool
commit() / rollBack()提交/回滾bool
errorCode() / errorInfo()錯誤代碼/詳情string/array