萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP mysqli 擴展完整指南:面向對象與過程式風格、預處理、事務、封裝類

PHP mysqli 擴展:MySQL 數據庫操作的現代標準

mysqli (MySQL Improved) 是 PHP 連接 MySQL 的推薦擴展(另一選擇是 PDO),支援面向對象與過程式雙風格、預處理語句、事務、多查詢、SSL 連接等。本文系統講解核心用法並提供生產級封裝類。

一、面向對象風格(推薦)

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // 拋出異常而非警告

$mysqli = new mysqli('localhost', 'user', 'password', 'database', 3306);
$mysqli->set_charset('utf8mb4'); // 關鍵:設置字符集

// 查詢 (DQL)
$sql = "SELECT id, username, email FROM users WHERE status = ? AND created_at > ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('is', 1, '2024-01-01');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "{$row['username']} <{$row['email']}>
"; } $stmt->close(); // 增刪改 (DML) $stmt = $mysqli->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)"); $stmt->bind_param('sss', $username, $email, password_hash($pass, PASSWORD_DEFAULT)); $stmt->execute(); $insertId = $stmt->insert_id; $affected = $stmt->affected_rows; $stmt->close(); // 事務 $mysqli->begin_transaction(); try { $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1"); $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2"); $mysqli->commit(); } catch (mysqli_sql_exception $e) { $mysqli->rollback(); throw $e; } $mysqli->close(); ?>

二、過程式風格(兼容舊代碼)

<?php
$link = mysqli_connect('localhost', 'user', 'password', 'database', 3306);
if (!$link) die('連接失敗: ' . mysqli_connect_error());
mysqli_set_charset($link, 'utf8mb4');

$result = mysqli_query($link, "SELECT * FROM users WHERE status = 1");
while ($row = mysqli_fetch_assoc($result)) { /* ... */ }
mysqli_free_result($result);

mysqli_query($link, "UPDATE users SET last_login = NOW() WHERE id = 1");
echo "影響行數: " . mysqli_affected_rows($link);

mysqli_close($link);
?>

三、核心方法速查表

類別面向對象過程式說明
連接new mysqli()mysqli_connect()建立連接
$mysqli->connect_errormysqli_connect_error()連接錯誤信息
$mysqli->set_charset()mysqli_set_charset()設置字符集 (必須 utf8mb4)
查詢$mysqli->query()mysqli_query()直接執行 SQL(不帶參數)
$mysqli->prepare()mysqli_prepare()預處理語句(防注入、性能)
$stmt->bind_param()mysqli_stmt_bind_param()綁定參數 (類型: i/d/s/b)
$stmt->execute()mysqli_stmt_execute()執行預處理
結果集$stmt->get_result()mysqli_stmt_get_result()獲取結果集對象 (需 mysqlnd)
$result->fetch_assoc()mysqli_fetch_assoc()獲取關聯數組
$result->fetch_all(MYSQLI_ASSOC)mysqli_fetch_all()一次性獲取所有行 (需 mysqlnd)
$result->num_rowsmysqli_num_rows()行數
$result->free()mysqli_free_result()釋放結果集內存
DML 反饋$stmt->affected_rows / $mysqli->affected_rowsmysqli_affected_rows()影響行數
$stmt->insert_id / $mysqli->insert_idmysqli_insert_id()自增 ID
$mysqli->errno / $mysqli->errormysqli_errno() / mysqli_error()錯誤碼/信息
事務$mysqli->begin_transaction()mysqli_begin_transaction()開啟事務
$mysqli->commit()mysqli_commit()提交
$mysqli->rollback()mysqli_rollback()回滾
關閉$mysqli->close()mysqli_close()關閉連接

四、生產級封裝類:SqlHelper(支援預處理、事務、日誌、斷線重連)

<?php
namespace App\Database;

use mysqli;
use mysqli_sql_exception;
use Throwable;

class SqlHelper
{
    private mysqli $mysqli;
    private array $config;
    private bool $inTransaction = false;
    private ?callable $logger = null;

    public function __construct(array $config, ?callable $logger = null)
    {
        $this->config = $config;
        $this->logger = $logger;
        $this->connect();
    }

    private function connect(): void
    {
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        $this->mysqli = new mysqli(
            $this->config['host'],
            $this->config['user'],
            $this->config['pass'],
            $this->config['db'],
            $this->config['port'] ?? 3306
        );
        $this->mysqli->set_charset($this->config['charset'] ?? 'utf8mb4');
        $this->log("Connected to {$this->config['host']}:{$this->config['port']}/{$this->config['db']}");
    }

    /** 確保連接有效(斷線自動重連) */
    private function ensureConnected(): void
    {
        if (!$this->mysqli || !$this->mysqli->ping()) {
            $this->log('Connection lost, reconnecting...');
            $this->connect();
        }
    }

    /** 執行 SELECT 查詢,返回關聯數組數組 */
    public function select(string $sql, array $params = [], array $types = []): array
    {
        $this->ensureConnected();
        $stmt = $this->prepareAndBind($sql, $params, $types);
        $stmt->execute();
        $result = $stmt->get_result();
        $data = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        $this->log("SELECT: $sql", $params, count($data));
        return $data;
    }

    /** 執行單行查詢 */
    public function selectOne(string $sql, array $params = [], array $types = []): ?array
    {
        $rows = $this->select($sql, $params, $types);
        return $rows[0] ?? null;
    }

    /** 執行 INSERT/UPDATE/DELETE,返回影響行數 */
    public function execute(string $sql, array $params = [], array $types = []): int
    {
        $this->ensureConnected();
        $stmt = $this->prepareAndBind($sql, $params, $types);
        $stmt->execute();
        $affected = $stmt->affected_rows;
        $insertId = $stmt->insert_id;
        $stmt->close();
        $this->log("EXECUTE: $sql", $params, "affected=$affected, insert_id=$insertId");
        return $affected;
    }

    /** 批量插入(單條 SQL 多值) */
    public function batchInsert(string $table, array $columns, array $rows): int
    {
        if (empty($rows)) return 0;
        $placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')';
        $sql = "INSERT INTO `$table` (" . implode(',', array_map(fn($c) => "`$c`", $columns)) . ") VALUES " . implode(',', array_fill(0, count($rows), $placeholders));
        $flatParams = array_merge(...$rows);
        return $this->execute($sql, $flatParams);
    }

    /** 預處理並綁定參數 */
    private function prepareAndBind(string $sql, array $params, array $types): mysqli_stmt
    {
        $stmt = $this->mysqli->prepare($sql);
        if ($params) {
            $typeStr = $types ? implode('', $types) : str_repeat('s', count($params)); // 默認全字符串
            $stmt->bind_param($typeStr, ...$params);
        }
        return $stmt;
    }

    /** 事務執行(自動提交/回滾) */
    public function transaction(callable $callback): mixed
    {
        $this->ensureConnected();
        if ($this->inTransaction) {
            // 已在事務中(嵌套),直接執行回調,由外層控制提交/回滾
            return $callback($this);
        }
        $this->inTransaction = true;
        $this->mysqli->begin_transaction();
        $this->log('Transaction BEGIN');
        try {
            $result = $callback($this);
            $this->mysqli->commit();
            $this->log('Transaction COMMIT');
            return $result;
        } catch (Throwable $e) {
            $this->mysqli->rollback();
            $this->log('Transaction ROLLBACK: ' . $e->getMessage());
            throw $e;
        } finally {
            $this->inTransaction = false;
        }
    }

    /** 獲取底層 mysqli 實例(高級用法) */
    public function getMysqli(): mysqli
    {
        $this->ensureConnected();
        return $this->mysqli;
    }

    /** 設置日誌回調 */
    public function setLogger(callable $logger): void
    {
        $this->logger = $logger;
    }

    private function log(string $sql, array $params = [], mixed $info = null): void
    {
        if ($this->logger) {
            ($this->logger)($sql, $params, $info);
        }
    }

    public function __destruct()
    {
        if ($this->mysqli) {
            $this->mysqli->close();
        }
    }
}

// ===== 使用示例 =====
$config = [
    'host' => '127.0.0.1',
    'user' => 'app_user',
    'pass' => 'secret',
    'db'   => 'myapp',
    'port' => 3306,
];

$db = new SqlHelper($config, fn($sql, $params, $info) => 
    error_log(sprintf('[SQL] %s | Params: %s | Info: %s', $sql, json_encode($params), $info))
);

// 查詢
$users = $db->select("SELECT * FROM users WHERE status = ? AND role IN (?,?)", [1, 'admin', 'editor']);
// 寫入
$db->execute("INSERT INTO logs (level, message) VALUES (?, ?)", ['INFO', 'User login']);
// 事務
$db->transaction(function($db) {
    $db->execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100, 1]);
    $db->execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100, 2]);
});
?>

五、最佳實踐清單

  1. 永遠使用預處理語句:防止 SQL 注入,複用執行計劃提升性能。
  2. 強制 utf8mb4:支援 Emoji、完整 Unicode,避免「Incorrect string value」錯誤。
  3. 開啟異常模式:mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT),避免靜默失敗。
  4. 及時釋放結果集:$result->free() 或 $stmt->close(),防止內存洩漏。
  5. 連接池/持久連接:高併發場景配合 PHP-FPM 使用持久連接 p:host 或 Swoole 協程連接池。
  6. 只讀/寫分離:主庫寫、從庫讀,封裝類中路由 SELECT 到從庫。
  7. SQL 日誌審計:生產環境記錄慢查詢、錯誤、關鍵寫入操作。
copyright © 萬盛學電腦網 all rights reserved