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_error | mysqli_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_rows | mysqli_num_rows() | 行數 | |
| $result->free() | mysqli_free_result() | 釋放結果集內存 | |
| DML 反饋 | $stmt->affected_rows / $mysqli->affected_rows | mysqli_affected_rows() | 影響行數 |
| $stmt->insert_id / $mysqli->insert_id | mysqli_insert_id() | 自增 ID | |
| $mysqli->errno / $mysqli->error | mysqli_errno() / mysqli_error() | 錯誤碼/信息 | |
| 事務 | $mysqli->begin_transaction() | mysqli_begin_transaction() | 開啟事務 |
| $mysqli->commit() | mysqli_commit() | 提交 | |
| $mysqli->rollback() | mysqli_rollback() | 回滾 | |
| 關閉 | $mysqli->close() | mysqli_close() | 關閉連接 |
<?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]);
});
?>
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT),避免靜默失敗。$result->free() 或 $stmt->close(),防止內存洩漏。p:host 或 Swoole 協程連接池。