MySQL 資料庫管理與 PHP 整合開發實戰指南

-- 建立部落格系統的資料庫
-- 資料庫名稱使用小寫與底線，遵循命名慣例
CREATE DATABASE blog_system
  -- 設定字元集為 utf8mb4，支援完整的 Unicode 字元集
  -- 這確保了可以儲存任何語言的文字，包括表情符號
  CHARACTER SET utf8mb4
  -- 設定排序規則為 utf8mb4_unicode_ci
  -- ci 表示不區分大小寫，適合大多數應用場景
  COLLATE utf8mb4_unicode_ci;

-- 切換到新建立的資料庫
-- 之後的所有操作都會在這個資料庫中執行
USE blog_system;

-- 建立文章資料表
-- 儲存部落格文章的主要內容
CREATE TABLE posts (
  -- id: 文章的唯一識別碼
  -- INT: 整數類型，足以支援數百萬筆記錄
  -- AUTO_INCREMENT: 自動遞增，新增記錄時自動分配下一個編號
  -- PRIMARY KEY: 設定為主鍵，確保唯一性且自動建立索引
  id INT AUTO_INCREMENT PRIMARY KEY,
  
  -- title: 文章標題
  -- VARCHAR(255): 可變長度字串，最多 255 個字元
  -- NOT NULL: 不允許空值，標題是必填欄位
  title VARCHAR(255) NOT NULL,
  
  -- content: 文章內容
  -- TEXT: 大型文字欄位，可儲存最多 65,535 個字元
  -- 適合儲存完整的文章內容
  content TEXT NOT NULL,
  
  -- author_id: 作者識別碼
  -- 這個欄位會關聯到 users 資料表
  author_id INT NOT NULL,
  
  -- created_at: 文章建立時間
  -- TIMESTAMP: 時間戳記類型，精確到秒
  -- DEFAULT CURRENT_TIMESTAMP: 預設值為當前時間
  -- 新增記錄時自動填入建立時間
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- updated_at: 文章最後更新時間
  -- ON UPDATE CURRENT_TIMESTAMP: 每次更新記錄時自動更新時間
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  -- 建立索引以提升查詢效能
  -- author_id 經常用於查詢特定作者的文章
  INDEX idx_author (author_id),
  
  -- created_at 用於按時間排序文章
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- 建立評論資料表
-- 儲存文章的使用者評論
CREATE TABLE comments (
  -- 評論的唯一識別碼
  id INT AUTO_INCREMENT PRIMARY KEY,
  
  -- post_id: 關聯的文章識別碼
  -- 這個欄位建立了評論與文章之間的關係
  post_id INT NOT NULL,
  
  -- content: 評論內容
  -- VARCHAR(500): 限制評論長度為 500 字元
  content VARCHAR(500) NOT NULL,
  
  -- commenter_name: 評論者姓名
  commenter_name VARCHAR(100) NOT NULL,
  
  -- created_at: 評論建立時間
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- 外鍵約束
  -- 確保 post_id 必須參照到 posts 資料表中存在的 id
  -- ON DELETE CASCADE: 當文章被刪除時，相關評論也會自動刪除
  -- 這維護了資料的參照完整性
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  
  -- 在 post_id 上建立索引
  -- 加速查詢特定文章的所有評論
  INDEX idx_post (post_id)
) ENGINE=InnoDB;

-- 新增一筆文章記錄
-- 展示基本的 INSERT 語法
INSERT INTO posts (title, content, author_id)
VALUES (
  -- title: 文章標題，使用單引號包圍字串
  'MySQL 資料庫入門教學',
  
  -- content: 文章內容，可以是多行文字
  '本文介紹 MySQL 資料庫的基本概念與操作方法...',
  
  -- author_id: 作者識別碼，參照 users 資料表
  1
);
-- 注意：id、created_at、updated_at 欄位會自動填入
-- 不需要在 INSERT 語句中指定

-- 新增多筆記錄
-- 使用單一 INSERT 語句可以提升效能
INSERT INTO comments (post_id, content, commenter_name)
VALUES
  -- 第一筆評論
  (1, '非常實用的教學文章！', '張三'),
  -- 第二筆評論
  (1, '感謝分享', '李四'),
  -- 第三筆評論
  (1, '期待後續的進階內容', '王五');

-- 查詢所有文章及其評論數量
-- 展示 JOIN 與聚合函數的使用
SELECT 
  -- 從 posts 資料表選取欄位
  -- 使用資料表別名 p 簡化語法
  p.id,
  p.title,
  p.created_at,
  
  -- COUNT 函數統計評論數量
  -- COALESCE 函數處理 NULL 值，沒有評論時顯示 0
  COALESCE(COUNT(c.id), 0) AS comment_count
FROM posts p
-- LEFT JOIN 確保即使文章沒有評論也會出現在結果中
-- 如果使用 INNER JOIN，則只會顯示有評論的文章
LEFT JOIN comments c ON p.id = c.post_id
-- GROUP BY 按文章分組
-- 這是使用聚合函數時的必要條件
GROUP BY p.id, p.title, p.created_at
-- ORDER BY 按建立時間降序排序
-- 最新的文章會出現在最前面
ORDER BY p.created_at DESC;

-- 更新文章標題
-- 使用主鍵確保只更新特定的一筆記錄
UPDATE posts
-- SET 子句指定要更新的欄位與新值
SET 
  title = 'MySQL 資料庫完整教學指南',
  -- updated_at 會自動更新為當前時間
  -- 不需要在 SET 子句中明確指定
  content = '本文提供 MySQL 資料庫的完整學習路徑...'
-- WHERE 子句指定更新條件
-- 使用主鍵確保唯一性
WHERE id = 1;

-- 刪除特定文章
-- 由於設定了 CASCADE 刪除，相關的評論也會自動刪除
DELETE FROM posts
WHERE id = 1;
-- 警告：這會同時刪除該文章的所有評論
-- 因為 comments 資料表的外鍵設定了 ON DELETE CASCADE

<?php
/**
 * MySQL 資料庫連線管理類別
 * 
 * 這個類別封裝了 MySQLi 連線的建立與管理
 * 採用單例模式確保整個應用程式只有一個資料庫連線實例
 * 
 * @author 玄貓（BlackCat）
 * @version 1.0.0
 */
class DatabaseConnection {
    // 資料庫連線實例
    // 使用 private static 確保單例模式
    private static $instance = null;
    
    // MySQLi 連線物件
    private $connection;
    
    // 資料庫連線參數
    // 實務上應該從設定檔或環境變數讀取
    // 避免將敏感資訊硬編碼在程式中
    private $host = 'localhost';
    private $username = 'blog_user';
    private $password = 'secure_password';
    private $database = 'blog_system';
    private $charset = 'utf8mb4';
    
    /**
     * 私有建構子
     * 
     * 防止外部直接使用 new 建立實例
     * 這是單例模式的關鍵設計
     */
    private function __construct() {
        // 建立 MySQLi 連線
        // @ 符號抑制錯誤訊息，使用自訂錯誤處理
        $this->connection = @new mysqli(
            $this->host,
            $this->username,
            $this->password,
            $this->database
        );
        
        // 檢查連線是否成功
        // connect_error 屬性在連線失敗時會包含錯誤訊息
        if ($this->connection->connect_error) {
            // 記錄錯誤到日誌檔案
            error_log('資料庫連線失敗: ' . $this->connection->connect_error);
            
            // 拋出例外，由上層處理
            throw new Exception('無法連線到資料庫伺服器');
        }
        
        // 設定字元集
        // 確保資料的正確編碼，避免亂碼問題
        if (!$this->connection->set_charset($this->charset)) {
            error_log('設定字元集失敗: ' . $this->connection->error);
            throw new Exception('資料庫字元集設定錯誤');
        }
    }
    
    /**
     * 取得資料庫連線實例
     * 
     * 使用單例模式，確保整個應用程式共用同一個連線
     * 這可以節省資源並提升效能
     * 
     * @return DatabaseConnection 資料庫連線實例
     */
    public static function getInstance() {
        // 如果實例尚未建立，則建立新實例
        if (self::$instance === null) {
            self::$instance = new self();
        }
        
        return self::$instance;
    }
    
    /**
     * 取得 MySQLi 連線物件
     * 
     * @return mysqli MySQLi 連線物件
     */
    public function getConnection() {
        return $this->connection;
    }
    
    /**
     * 執行查詢
     * 
     * 提供統一的查詢介面，包含錯誤處理
     * 
     * @param string $sql SQL 查詢語句
     * @return mysqli_result|bool 查詢結果或布林值
     */
    public function query($sql) {
        // 執行查詢
        $result = $this->connection->query($sql);
        
        // 如果查詢失敗，記錄錯誤
        if ($result === false) {
            error_log('SQL 查詢失敗: ' . $this->connection->error);
            error_log('SQL 語句: ' . $sql);
        }
        
        return $result;
    }
    
    /**
     * 關閉連線
     * 
     * 在應用程式結束時呼叫
     * PHP 會在腳本結束時自動關閉連線
     * 但明確關閉是良好的實務習慣
     */
    public function close() {
        if ($this->connection) {
            $this->connection->close();
        }
    }
    
    /**
     * 防止複製實例
     */
    private function __clone() {}
    
    /**
     * 防止反序列化建立實例
     */
    public function __wakeup() {
        throw new Exception('無法反序列化單例類別');
    }
}
?>

<?php
/**
 * PDO 資料庫連線範例
 * 
 * 展示如何使用 PDO 建立安全的資料庫連線
 * PDO 提供了更好的錯誤處理與預處理語句支援
 */

// 資料庫連線參數
// 實務上應該從環境變數或設定檔讀取
$host = 'localhost';
$database = 'blog_system';
$username = 'blog_user';
$password = 'secure_password';
$charset = 'utf8mb4';

// 建構 DSN (Data Source Name) 字串
// DSN 描述了資料庫的類型、位置與名稱
$dsn = "mysql:host={$host};dbname={$database};charset={$charset}";

// PDO 連線選項
// 這些選項會影響 PDO 的行為
$options = [
    // 設定錯誤模式為例外
    // 任何資料庫錯誤都會拋出 PDOException
    // 這讓錯誤處理更加清晰且易於追蹤
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    
    // 設定預設的抓取模式為關聯陣列
    // 查詢結果會以欄位名稱為鍵的陣列形式回傳
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    
    // 禁用模擬預處理語句
    // 使用真正的預處理語句可以提供更好的安全性
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    // 建立 PDO 連線
    // 將連線參數與選項傳入建構子
    $pdo = new PDO($dsn, $username, $password, $options);
    
    // 連線成功
    echo "資料庫連線建立成功\n";
    
} catch (PDOException $e) {
    // 捕捉連線錯誤
    // 記錄錯誤訊息到日誌檔案
    error_log('PDO 連線失敗: ' . $e->getMessage());
    
    // 向使用者顯示友善的錯誤訊息
    // 不應該直接顯示詳細的錯誤資訊
    // 這可能洩露敏感的系統資訊
    die('資料庫連線失敗，請稍後再試');
}
?>

<?php
/**
 * 使用預處理語句安全地查詢資料
 * 
 * 展示如何使用 MySQLi 預處理語句防範 SQL 注入
 */

// 取得資料庫連線
$db = DatabaseConnection::getInstance();
$conn = $db->getConnection();

// 假設從使用者輸入取得文章 ID
// 在實際應用中，應該先驗證輸入的格式與範圍
$post_id = $_GET['id'] ?? 1;

// 準備 SQL 語句
// 使用 ? 作為參數佔位符
// 這些佔位符稍後會被實際的值替換
$sql = "SELECT id, title, content, created_at 
        FROM posts 
        WHERE id = ?";

// 建立預處理語句物件
// prepare 方法會先編譯 SQL 語句
// 此時參數還沒有被綁定
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    // 預處理語句建立失敗
    error_log('預處理語句準備失敗: ' . $conn->error);
    die('查詢準備失敗');
}

// 綁定參數
// 'i' 表示參數類型為整數 (integer)
// 其他類型包括：
//   's' - 字串 (string)
//   'd' - 雙精度浮點數 (double)
//   'b' - 二進位資料 (blob)
$stmt->bind_param('i', $post_id);

// 執行查詢
// 此時實際的參數值會被安全地插入到 SQL 語句中
if (!$stmt->execute()) {
    error_log('查詢執行失敗: ' . $stmt->error);
    die('查詢執行失敗');
}

// 取得查詢結果
$result = $stmt->get_result();

// 檢查是否找到記錄
if ($result->num_rows > 0) {
    // 抓取資料為關聯陣列
    $post = $result->fetch_assoc();
    
    // 顯示文章資訊
    // 使用 htmlspecialchars 防範 XSS 攻擊
    echo "<h1>" . htmlspecialchars($post['title']) . "</h1>";
    echo "<p>" . nl2br(htmlspecialchars($post['content'])) . "</p>";
    echo "<small>發布時間: " . $post['created_at'] . "</small>";
} else {
    echo "找不到指定的文章";
}

// 關閉預處理語句
// 釋放伺服器資源
$stmt->close();
?>

<?php
/**
 * 使用 PDO 預處理語句安全地插入資料
 * 
 * 展示如何使用命名參數與預處理語句
 */

// 假設從表單取得新文章資料
// 實務上應該先進行輸入驗證與清理
$title = $_POST['title'] ?? '';
$content = $_POST['content'] ?? '';
$author_id = $_SESSION['user_id'] ?? 1;

// 驗證必填欄位
if (empty($title) || empty($content)) {
    die('標題與內容不能為空');
}

try {
    // 準備 SQL 語句
    // 使用命名參數 :title, :content, :author_id
    // 命名參數讓程式碼更具可讀性
    $sql = "INSERT INTO posts (title, content, author_id) 
            VALUES (:title, :content, :author_id)";
    
    // 建立預處理語句
    $stmt = $pdo->prepare($sql);
    
    // 綁定參數並執行
    // execute 方法接受一個關聯陣列
    // 陣列的鍵對應參數名稱（不包含冒號）
    $stmt->execute([
        'title' => $title,
        'content' => $content,
        'author_id' => $author_id
    ]);
    
    // 取得新插入記錄的 ID
    // lastInsertId 回傳最後插入的自動遞增 ID
    $new_post_id = $pdo->lastInsertId();
    
    echo "文章新增成功，ID: {$new_post_id}";
    
} catch (PDOException $e) {
    // 捕捉執行錯誤
    error_log('新增文章失敗: ' . $e->getMessage());
    echo '新增文章失敗，請稍後再試';
}
?>