PHP 實用 MySQL 資料庫連線類別完整實作

前言

封裝資料庫操作為類別可提高程式碼復用性、維護性與安全性。本文提供一個功能完整的 MySQL 連線類別，支援連線管理、查詢執行、結果擷取、錯誤處理等核心功能。

完整類別程式碼

<?php
class MySQLDatabase {
  private $host;
  private $user;
  private $pass;
  private $name;
  private $charset;
  private $conn;
  private $queryId;
  private $result;
  private $numRows;
  private $insertId;

  public function __construct($host, $user, $pass, $name, $charset = 'utf8', $persistent = false) {
    $this->host = $host;
    $this->user = $user;
    $this->pass = $pass;
    $this->name = $name;
    $this->charset = $charset;
    $this->connect($persistent);
  }

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

  private function connect($persistent) {
    $func = $persistent ? 'mysql_pconnect' : 'mysql_connect';
    $this->conn = @$func($this->host, $this->user, $this->pass);
    
 &   if (!$this->conn) {
      $this->showError('資料庫連線失敗：請檢查主機/用戶/密碼');
    }
    
    if (!@mysql_select_db($this->name, $this->conn)) {
      $this->showError("資料庫選擇失敗：{$this->name} 不存在或無權限");
    }
    
    mysql_query("SET NAMES {$this->charset}", $this->conn);
    return $this->conn;
  }

  public function query($sql) {
    $this->freeResult();
    $this->queryId = @mysql_query($sql, $this->conn);
    if (!$this->queryId) {
      $this->showError("SQL 執行錯誤: {$sql}");
    }
    return $this->queryId;
  }

  public function findAll($table) {
    return $this->query("SELECT * FROM `{$table}`");
  }

  public function fetchArray() {
    if ($this->queryId) {
      $this->result = mysql_fetch_array($this->queryId);
      return $this->result;
    }
    return false;
  }

  public function fetchAssoc() {
    if ($this->queryId) {
      $this->result = mysql_fetch_assoc($this->queryId);
      return $this->result;
    }
    return false;
  }

  public function fetchRow() {
    if ($this->queryId) {
      $this->result = mysql_fetch_row($this->queryId);
      return $this->result;
    }
    return false;
  }

  public function fetchObject() {
    if ($this->queryId) {
      $this->result = mysql_fetch_object($this->queryId);
      return $this->result;
    }
    return false;
  }

  public function numRows() {
    if ($this->queryId) {
      $this->numRows = mysql_num_rows($this->queryId);
      return $this->numRows;
    }
    return 0;
  }

  public function insertId() {
    return $this->insertId = mysql_insert_id($this->conn);
  }

  public function showTables() {
    $this->query("SHOW TABLES");
    if ($this->queryId) {
      echo "資料庫 {$this->name} 共有 {$this->numRows()} 張表
";
      $i = 1;
      while ($row = $this->fetchArray()) {
        echo "$i -- {$row[0]}
";
        $i++;
      }
    }
  }

  public function showDatabases() {
    $this->query("SHOW DATABASES");
    if ($this->queryId) {
      echo "共有 {$this->numRows()} 個資料庫
";
      $i = 1;
      while ($row = $this->fetchArray()) {
        echo "$i -- {$row['Database']}
";
        $i++;
      }
    }
  }

  public function dropDatabase($dbName = '') {
    $dbName = $dbName ?: $this->name;
    $this->query("DROP DATABASE `{$dbName}`");
    if ($this->queryId) return "資料庫 {$dbName} 刪除成功";
    $this->showError("資料庫 {$dbName} 刪除失敗");
  }

  public function dropTable($tableName) {
    $this->query("DROP TABLE `{$tableName}`");
    if ($this->queryId) return "資料表 {$tableName} 刪除成功";
    $this->showError("資料表 {$tableName} 刪除失敗");
  }

  public function createDatabase($dbName) {
    $this->query("CREATE DATABASE `{$dbName}`");
    if ($this->queryId) return "資料庫 {$dbName} 建立成功";
    $this->showError("資料庫 {$dbName} 建立失敗");
  }

  public function getServerInfo() {
    echo mysql_get_server_info($this->conn);
  }

  private function showError($msg) {
    $err = mysql_error($this->conn);
    die("錯誤: $msg
MySQL 回報: $err");
  }

  public function freeResult() {
    if ($this->queryId) {
      @mysql_free_result($this->queryId);
      $this->queryId = 0;
      unset($this->result);
    }
  }

  public function close() {
    if ($this->conn) {
      @mysql_close($this->conn);
      $this->conn = null;
    }
  }
}
?>

使用範例

<?php
require_once 'MySQLDatabase.php';

// 建立連線 (非持久)
$db = new MySQLDatabase('localhost', 'root', 'password', 'my_database', 'utf8', false);

// 查詢所有會員
$db->findAll('members');
while ($member = $db->fetchAssoc()) {
  echo "{$member['id']}: {$member['name']} - {$member['email']}
";
}

// 參數化查詢 (手動跳脫)
$keyword = mysql_real_escape_string($_GET['q'], $db->conn);
$db->query("SELECT * FROM products WHERE name LIKE '%{$keyword}%'");
echo "找到 {$db->numRows()} 筆商品
";

// 新增資料並取得 ID
$db->query("INSERT INTO logs (message, created_at) VALUES ('測試訊息', NOW())");
echo "新增記錄 ID: " . $db->insertId();

// 顯示資料表列表
$db->showTables();
?>

類別功能總覽

⚠️ 重要提醒

現代化 PDO 版本架構草圖

<?php
class Database {
  private PDO $pdo;
  
  public function __construct(array $config) {
    $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
    $this->pdo = new PDO($dsn, $config['user'], $config['pass'], [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false,
    ]);
  }
  
  public function query(string $sql, array $params = []): PDOStatement {
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt;
  }
  
  public function fetchAll(string $sql, array $params = []): array {
    return $this->query($sql, $params)->fetchAll();
  }
  
  public function fetchOne(string $sql, array $params = []) {
    return $this->query($sql, $params)->fetch();
  }
  
  public function insert(string $table, array $data): int {
 &   $fields = implode(',', array_keys($data));
    $placeholders = ':' . implode(',:', array_keys($data));
    $this->query("INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})", $data);
    return (int)$this->pdo->lastInsertId();
  }
  
  public function beginTransaction() { $this->pdo->beginTransaction(); }
  public function commit() { $this->pdo->commit(); }
  public function rollBack() { $this->pdo->rollBack(); }
}
?>