MySQL 資料庫與 PHP 應用程式整合開發完全指南

<?php
/**
 * PDO 資料庫連線與查詢範例
 * 
 * 本範例展示 PDO 的基本使用方式，包含錯誤處理、預處理陳述式、
 * 參數綁定等核心功能。適用於生產環境的標準做法。
 */

// 資料庫連線參數設定
// DSN (Data Source Name) 定義了資料庫類型、主機位址、資料庫名稱、字元編碼
$dsn = 'mysql:host=localhost;dbname=enterprise_db;charset=utf8mb4';
$username = 'app_user';  // 應用程式專用帳號，遵循最小權限原則
$password = 'secure_password_here';  // 實務上應從環境變數或配置檔讀取

// PDO 連線選項設定
$options = [
    // 錯誤模式設為例外處理，便於捕捉與記錄錯誤
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // 預設使用預處理陳述式，強化安全性
    PDO::ATTR_EMULATE_PREPARES => false,
    // 設定查詢結果的回傳格式為關聯陣列
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // 啟用持久連線，重用現有連線以提升效能
    PDO::ATTR_PERSISTENT => true
];

try {
    // 建立 PDO 資料庫連線物件
    $pdo = new PDO($dsn, $username, $password, $options);
    
    // 查詢範例：取得特定部門的員工清單
    // 使用命名佔位符 (:department_id) 進行參數綁定
    $sql = "SELECT employee_id, employee_name, email, hire_date 
            FROM employees 
            WHERE department_id = :department_id 
            AND status = :status 
            ORDER BY hire_date DESC";
    
    // 準備 SQL 陳述式，此時 MySQL 會編譯並快取執行計畫
    $stmt = $pdo->prepare($sql);
    
    // 綁定參數值，PDO 會自動處理資料型別與跳脫
    $stmt->bindValue(':department_id', 10, PDO::PARAM_INT);
    $stmt->bindValue(':status', 'active', PDO::PARAM_STR);
    
    // 執行查詢
    $stmt->execute();
    
    // 取得查詢結果
    $employees = $stmt->fetchAll();
    
    // 處理查詢結果
    foreach ($employees as $employee) {
        // 輸出員工資訊，實務上會進一步處理或渲染至前端
        echo sprintf(
            "員工編號：%s，姓名：%s，電子郵件：%s，到職日期：%s\n",
            htmlspecialchars($employee['employee_id']),
            htmlspecialchars($employee['employee_name']),
            htmlspecialchars($employee['email']),
            htmlspecialchars($employee['hire_date'])
        );
    }
    
    // 交易處理範例：更新員工部門並記錄異動歷史
    // 開啟交易，確保操作的原子性
    $pdo->beginTransaction();
    
    try {
        // 更新員工部門
        $updateSql = "UPDATE employees 
                      SET department_id = :new_dept, 
                          updated_at = NOW() 
                      WHERE employee_id = :emp_id";
        $updateStmt = $pdo->prepare($updateSql);
        $updateStmt->execute([
            ':new_dept' => 20,
            ':emp_id' => 'E12345'
        ]);
        
        // 記錄異動歷史
        $logSql = "INSERT INTO department_change_log 
                   (employee_id, old_dept, new_dept, change_date, changed_by) 
                   VALUES (:emp_id, :old_dept, :new_dept, NOW(), :user)";
        $logStmt = $pdo->prepare($logSql);
        $logStmt->execute([
            ':emp_id' => 'E12345',
            ':old_dept' => 10,
            ':new_dept' => 20,
            ':user' => 'HR_ADMIN'
        ]);
        
        // 所有操作成功，提交交易
        $pdo->commit();
        echo "部門異動成功完成\n";
        
    } catch (PDOException $e) {
        // 發生錯誤時回滾交易，確保資料一致性
        $pdo->rollBack();
        // 記錄錯誤資訊至日誌系統
        error_log("交易失敗：" . $e->getMessage());
        echo "部門異動失敗，已回滾所有變更\n";
    }
    
} catch (PDOException $e) {
    // 處理連線錯誤或其他 PDO 例外
    // 實務上應記錄至專業日誌系統，並通知管理員
    error_log("資料庫錯誤：" . $e->getMessage());
    die("系統暫時無法處理請求，請稍後再試");
}

/**
 * MySQLi 物件導向方式範例
 * 
 * 展示 MySQLi 的物件導向操作方式，包含錯誤處理、
 * 預處理陳述式、結果集處理等功能
 */

// 建立 MySQLi 連線物件
$mysqli = new mysqli('localhost', 'app_user', 'secure_password_here', 'enterprise_db');

// 設定字元編碼為 UTF-8，確保繁體中文正確處理
$mysqli->set_charset('utf8mb4');

// 檢查連線是否成功
if ($mysqli->connect_errno) {
    // 記錄連線錯誤並終止執行
    error_log("MySQLi 連線失敗：" . $mysqli->connect_error);
    die("資料庫連線失敗");
}

// 準備查詢陳述式
// 使用問號佔位符進行參數綁定
$sql = "SELECT product_id, product_name, unit_price, stock_quantity 
        FROM products 
        WHERE category_id = ? AND unit_price <= ? 
        ORDER BY product_name";

// 建立預處理陳述式物件
$stmt = $mysqli->prepare($sql);

if ($stmt === false) {
    error_log("陳述式準備失敗：" . $mysqli->error);
    die("查詢準備失敗");
}

// 綁定參數
// 第一個參數 "id" 定義參數型別：i=integer, d=double, s=string, b=blob
$categoryId = 5;
$maxPrice = 1000.00;
$stmt->bind_param("id", $categoryId, $maxPrice);

// 執行查詢
$stmt->execute();

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

// 處理查詢結果
echo "查詢結果：\n";
while ($row = $result->fetch_assoc()) {
    printf(
        "產品：%s（編號：%s），單價：NT$ %.2f，庫存：%d\n",
        htmlspecialchars($row['product_name']),
        htmlspecialchars($row['product_id']),
        $row['unit_price'],
        $row['stock_quantity']
    );
}

// 釋放結果集與陳述式資源
$result->free();
$stmt->close();

// 關閉資料庫連線
$mysqli->close();

/**
 * MySQLi 程序式方式範例
 * 
 * 展示程序式 MySQLi 的使用方式，適合習慣程序式
 * 程式設計風格的開發者，或需要維護舊有系統時使用
 */

// 建立資料庫連線
$connection = mysqli_connect('localhost', 'app_user', 'secure_password_here', 'enterprise_db');

// 檢查連線狀態
if (!$connection) {
    error_log("MySQLi 程序式連線失敗：" . mysqli_connect_error());
    die("資料庫連線失敗");
}

// 設定字元編碼
mysqli_set_charset($connection, 'utf8mb4');

// 準備 SQL 查詢
$query = "SELECT order_id, customer_name, order_total, order_date 
          FROM orders 
          WHERE order_status = ? AND order_date >= ? 
          ORDER BY order_date DESC 
          LIMIT ?";

// 建立預處理陳述式
$statement = mysqli_prepare($connection, $query);

if ($statement === false) {
    error_log("陳述式準備失敗：" . mysqli_error($connection));
    die("查詢準備失敗");
}

// 綁定參數
$orderStatus = 'completed';
$startDate = '2025-01-01';
$limitCount = 50;
mysqli_stmt_bind_param($statement, "ssi", $orderStatus, $startDate, $limitCount);

// 執行查詢
mysqli_stmt_execute($statement);

// 取得結果
$queryResult = mysqli_stmt_get_result($statement);

// 處理結果集
echo "訂單查詢結果：\n";
while ($orderRow = mysqli_fetch_assoc($queryResult)) {
    printf(
        "訂單編號：%s，客戶：%s，金額：NT$ %.2f，日期：%s\n",
        htmlspecialchars($orderRow['order_id']),
        htmlspecialchars($orderRow['customer_name']),
        $orderRow['order_total'],
        htmlspecialchars($orderRow['order_date'])
    );
}

// 釋放資源
mysqli_free_result($queryResult);
mysqli_stmt_close($statement);
mysqli_close($connection);
?>

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL 資料庫操作 Python 範例
展示使用 mysql-connector-python 進行資料庫連線、查詢、
交易處理等核心操作，適用於企業級應用開發
"""

import mysql.connector
from mysql.connector import Error, pooling
from datetime import datetime
import logging

# 設定日誌記錄
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    filename='/var/log/app/database.log'
)

class DatabaseManager:
    """
    資料庫管理類別
    提供連線池管理、查詢執行、交易處理等功能
    """
    
    def __init__(self, pool_name="mypool", pool_size=5):
        """
        初始化資料庫連線池
        
        Args:
            pool_name: 連線池名稱
            pool_size: 連線池大小，根據應用程式並發需求調整
        """
        try:
            # 建立連線池配置
            self.pool = mysql.connector.pooling.MySQLConnectionPool(
                pool_name=pool_name,
                pool_size=pool_size,
                pool_reset_session=True,  # 重用連線時重設 session
                host='localhost',
                database='enterprise_db',
                user='app_user',
                password='secure_password',
                charset='utf8mb4',  # 支援完整 Unicode 字元集
                collation='utf8mb4_unicode_ci',  # 使用 Unicode 排序規則
                autocommit=False,  # 關閉自動提交，明確控制交易
                use_unicode=True
            )
            logging.info(f"連線池 {pool_name} 初始化成功，大小：{pool_size}")
        except Error as e:
            logging.error(f"連線池建立失敗：{e}")
            raise
    
    def get_connection(self):
        """
        從連線池取得資料庫連線
        
        Returns:
            資料庫連線物件
        """
        try:
            connection = self.pool.get_connection()
            logging.debug("成功從連線池取得連線")
            return connection
        except Error as e:
            logging.error(f"取得連線失敗：{e}")
            raise
    
    def execute_query(self, query, params=None):
        """
        執行 SELECT 查詢
        
        Args:
            query: SQL 查詢陳述式
            params: 查詢參數，使用 tuple 或 list 傳遞
            
        Returns:
            查詢結果列表
        """
        connection = None
        cursor = None
        
        try:
            # 從連線池取得連線
            connection = self.get_connection()
            # 建立遊標物件，使用字典格式回傳結果
            cursor = connection.cursor(dictionary=True)
            
            # 執行查詢，自動進行參數綁定
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            # 取得所有查詢結果
            results = cursor.fetchall()
            logging.info(f"查詢成功，回傳 {len(results)} 筆資料")
            return results
            
        except Error as e:
            logging.error(f"查詢執行錯誤：{e}")
            raise
        finally:
            # 確保資源被正確釋放
            if cursor:
                cursor.close()
            if connection:
                connection.close()
                logging.debug("連線已歸還至連線池")
    
    def execute_transaction(self, operations):
        """
        執行交易，確保多個資料庫操作的原子性
        
        Args:
            operations: 操作清單，每個操作為 (query, params) 組合
            
        Returns:
            交易執行結果
        """
        connection = None
        cursor = None
        
        try:
            connection = self.get_connection()
            cursor = connection.cursor()
            
            # 明確開啟交易
            connection.start_transaction()
            logging.info("交易開始")
            
            # 逐一執行交易中的操作
            for query, params in operations:
                cursor.execute(query, params)
                logging.debug(f"執行操作：{query[:50]}...")
            
            # 提交交易
            connection.commit()
            logging.info("交易提交成功")
            return True
            
        except Error as e:
            # 發生錯誤時回滾交易
            if connection:
                connection.rollback()
                logging.warning("交易已回滾")
            logging.error(f"交易執行失敗：{e}")
            raise
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()

def main():
    """
    主程式：展示資料庫操作的實務應用
    """
    # 初始化資料庫管理器
    db = DatabaseManager(pool_size=10)
    
    # 範例一：查詢員工資料
    print("=== 查詢部門員工 ===")
    employee_query = """
        SELECT e.employee_id, e.employee_name, e.email, d.department_name
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE d.department_id = %s AND e.status = %s
        ORDER BY e.hire_date DESC
        LIMIT %s
    """
    
    try:
        employees = db.execute_query(
            employee_query,
            (10, 'active', 20)  # 部門 ID、狀態、查詢筆數
        )
        
        for emp in employees:
            print(f"員工：{emp['employee_name']} ({emp['email']})")
            print(f"部門：{emp['department_name']}\n")
            
    except Error as e:
        print(f"查詢失敗：{e}")
    
    # 範例二：執行交易 - 轉帳操作
    print("=== 執行轉帳交易 ===")
    transfer_operations = [
        # 從帳戶 A 扣款
        (
            "UPDATE accounts SET balance = balance - %s WHERE account_id = %s",
            (1000.00, 'ACC001')
        ),
        # 向帳戶 B 入款
        (
            "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
            (1000.00, 'ACC002')
        ),
        # 記錄交易歷史
        (
            """INSERT INTO transaction_log 
               (from_account, to_account, amount, transaction_date) 
               VALUES (%s, %s, %s, %s)""",
            ('ACC001', 'ACC002', 1000.00, datetime.now())
        )
    ]
    
    try:
        db.execute_transaction(transfer_operations)
        print("轉帳成功完成")
    except Error as e:
        print(f"轉帳失敗：{e}")
    
    # 範例三：批次資料處理
    print("=== 批次匯入資料 ===")
    batch_data = [
        ('張小明', 'ming@example.com', 10),
        ('李小華', 'hua@example.com', 10),
        ('王小美', 'mei@example.com', 20)
    ]
    
    insert_query = """
        INSERT INTO employees (employee_name, email, department_id)
        VALUES (%s, %s, %s)
    """
    
    batch_operations = [
        (insert_query, data) for data in batch_data
    ]
    
    try:
        db.execute_transaction(batch_operations)
        print(f"成功匯入 {len(batch_data)} 筆員工資料")
    except Error as e:
        print(f"批次匯入失敗：{e}")

if __name__ == "__main__":
    main()

package com.enterprise.database;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * MySQL 資料庫管理類別
 * 提供企業級的資料庫連線管理、查詢執行、交易處理功能
 * 使用 HikariCP 連線池確保高效能與穩定性
 * 
 * @author BlackCat
 * @version 1.0
 */
public class DatabaseManager {
    
    // 日誌記錄器
    private static final Logger logger = LoggerFactory.getLogger(DatabaseManager.class);
    
    // HikariCP 資料源
    private static HikariDataSource dataSource;
    
    /**
     * 初始化資料庫連線池
     * 採用 HikariCP 作為連線池實作，提供最佳效能
     */
    static {
        try {
            // 建立 HikariCP 配置
            HikariConfig config = new HikariConfig();
            
            // 設定 JDBC 連線參數
            config.setJdbcUrl("jdbc:mysql://localhost:3306/enterprise_db");
            config.setUsername("app_user");
            config.setPassword("secure_password");
            
            // 設定連線池參數
            config.setMaximumPoolSize(20);  // 最大連線數
            config.setMinimumIdle(5);  // 最小閒置連線數
            config.setConnectionTimeout(30000);  // 連線超時時間（毫秒）
            config.setIdleTimeout(600000);  // 閒置連線超時（10分鐘）
            config.setMaxLifetime(1800000);  // 連線最大生命週期（30分鐘）
            
            // 設定連線測試查詢
            config.setConnectionTestQuery("SELECT 1");
            
            // 設定資料庫連線屬性
            Properties props = new Properties();
            props.setProperty("cachePrepStmts", "true");  // 啟用預處理陳述式快取
            props.setProperty("prepStmtCacheSize", "250");  // 快取大小
            props.setProperty("prepStmtCacheSqlLimit", "2048");  // SQL 長度限制
            props.setProperty("useServerPrepStmts", "true");  // 使用伺服器端預處理
            props.setProperty("useUnicode", "true");  // 啟用 Unicode
            props.setProperty("characterEncoding", "UTF-8");  // 字元編碼
            props.setProperty("serverTimezone", "Asia/Taipei");  // 時區設定
            config.setDataSourceProperties(props);
            
            // 建立資料源
            dataSource = new HikariDataSource(config);
            logger.info("資料庫連線池初始化成功");
            
        } catch (Exception e) {
            logger.error("資料庫連線池初始化失敗", e);
            throw new RuntimeException("無法初始化資料庫連線池", e);
        }
    }
    
    /**
     * 取得資料庫連線
     * 
     * @return 資料庫連線物件
     * @throws SQLException 連線取得失敗時拋出
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    /**
     * 執行查詢操作並回傳結果列表
     * 
     * @param sql SQL 查詢陳述式
     * @param params 查詢參數陣列
     * @return 查詢結果列表
     */
    public static List<Employee> queryEmployees(String sql, Object... params) {
        List<Employee> employees = new ArrayList<>();
        
        // 使用 try-with-resources 確保資源自動釋放
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // 設定查詢參數
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            
            // 執行查詢
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    // 建立員工物件並填入資料
                    Employee employee = new Employee();
                    employee.setEmployeeId(rs.getString("employee_id"));
                    employee.setEmployeeName(rs.getString("employee_name"));
                    employee.setEmail(rs.getString("email"));
                    employee.setDepartmentId(rs.getInt("department_id"));
                    employee.setHireDate(rs.getDate("hire_date"));
                    
                    employees.add(employee);
                }
            }
            
            logger.info("查詢成功，回傳 {} 筆資料", employees.size());
            
        } catch (SQLException e) {
            logger.error("查詢執行失敗：{}", sql, e);
            throw new RuntimeException("資料查詢失敗", e);
        }
        
        return employees;
    }
    
    /**
     * 執行交易操作
     * 確保多個資料庫操作的原子性、一致性
     * 
     * @param operations 交易操作清單
     * @return 交易是否成功
     */
    public static boolean executeTransaction(List<TransactionOperation> operations) {
        Connection conn = null;
        
        try {
            // 取得連線並關閉自動提交
            conn = getConnection();
            conn.setAutoCommit(false);
            
            logger.info("開始執行交易，包含 {} 個操作", operations.size());
            
            // 逐一執行交易操作
            for (TransactionOperation operation : operations) {
                try (PreparedStatement pstmt = conn.prepareStatement(operation.getSql())) {
                    
                    // 設定參數
                    Object[] params = operation.getParams();
                    for (int i = 0; i < params.length; i++) {
                        pstmt.setObject(i + 1, params[i]);
                    }
                    
                    // 執行更新
                    int affectedRows = pstmt.executeUpdate();
                    logger.debug("操作影響 {} 筆資料", affectedRows);
                }
            }
            
            // 提交交易
            conn.commit();
            logger.info("交易提交成功");
            return true;
            
        } catch (SQLException e) {
            // 發生錯誤時回滾交易
            if (conn != null) {
                try {
                    conn.rollback();
                    logger.warn("交易已回滾");
                } catch (SQLException rollbackEx) {
                    logger.error("交易回滾失敗", rollbackEx);
                }
            }
            logger.error("交易執行失敗", e);
            return false;
            
        } finally {
            // 恢復自動提交模式並關閉連線
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    logger.error("連線關閉失敗", e);
                }
            }
        }
    }
    
    /**
     * 批次插入資料
     * 使用批次處理提升大量資料插入的效能
     * 
     * @param sql SQL 插入陳述式
     * @param batchData 批次資料列表
     * @return 成功插入的資料筆數
     */
    public static int batchInsert(String sql, List<Object[]> batchData) {
        int totalInserted = 0;
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            // 關閉自動提交以提升效能
            conn.setAutoCommit(false);
            
            // 逐筆加入批次
            for (Object[] data : batchData) {
                for (int i = 0; i < data.length; i++) {
                    pstmt.setObject(i + 1, data[i]);
                }
                pstmt.addBatch();
            }
            
            // 執行批次操作
            int[] results = pstmt.executeBatch();
            conn.commit();
            
            // 計算成功插入筆數
            for (int result : results) {
                if (result > 0) {
                    totalInserted++;
                }
            }
            
            logger.info("批次插入完成，成功插入 {} 筆資料", totalInserted);
            
        } catch (SQLException e) {
            logger.error("批次插入失敗", e);
            throw new RuntimeException("批次資料插入失敗", e);
        }
        
        return totalInserted;
    }
    
    /**
     * 關閉資料源（應用程式關閉時呼叫）
     */
    public static void shutdown() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            logger.info("資料庫連線池已關閉");
        }
    }
}

/**
 * 交易操作封裝類別
 */
class TransactionOperation {
    private String sql;
    private Object[] params;
    
    public TransactionOperation(String sql, Object... params) {
        this.sql = sql;
        this.params = params;
    }
    
    public String getSql() {
        return sql;
    }
    
    public Object[] getParams() {
        return params;
    }
}

/**
 * 員工資料模型類別
 */
class Employee {
    private String employeeId;
    private String employeeName;
    private String email;
    private int departmentId;
    private Date hireDate;
    
    // Getter 與 Setter 方法省略
    public String getEmployeeId() { return employeeId; }
    public void setEmployeeId(String employeeId) { this.employeeId = employeeId; }
    public String getEmployeeName() { return employeeName; }
    public void setEmployeeName(String employeeName) { this.employeeName = employeeName; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public int getDepartmentId() { return departmentId; }
    public void setDepartmentId(int departmentId) { this.departmentId = departmentId; }
    public Date getHireDate() { return hireDate; }
    public void setHireDate(Date hireDate) { this.hireDate = hireDate; }
}