萬盛學電腦網

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

PHP 查詢資料庫回傳 JSON:從 mysql_* 到 PDO 的現代化重構指南

⚠️ 重要警示

原文使用 mysql_* 函數,**已在 PHP 7.0 移除**,嚴禁在新專案使用。本文提供現代化 PDO 版本,並修正 SQL 注入、編碼、分頁、錯誤處理等關鍵問題。

現代化 PDO 版本(生產就緒)

<?php
// config.php - 資料庫設定
return [
    'dsn' => 'mysql:host=localhost;dbname=jilinwula;charset=utf8mb4',
    'user' => 'root',
    'pass' => 'root',
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false, // 真實預備語句,防注入
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
    ],
];

// api/users.php
require 'config.php';

try {
    $pdo = new PDO(...$config['dsn'], $config['user'], $config['pass'], $config['options']);
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database connection failed']);
    exit;
}

// 1. 參數驗證與預設值
$page     = max(1, (int)($_GET['page'] ?? 1));
$pageSize = min(100, max(1, (int)($_GET['pageSize'] ?? 10))); // 限制最大 100
$offset   = ($page - 1) * $pageSize;

// 2. 總筆數(分頁資訊)
$totalStmt = $pdo->query('SELECT COUNT(*) FROM userinfo');
$total = (int)$totalStmt->fetchColumn();

// 3. 參數化查詢(防 SQL 注入)
$stmt = $pdo->prepare('SELECT username, password FROM userinfo LIMIT :offset, :limit');
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();

// 4. 輸出標準 JSON 格式
header('Content-Type: application/json; charset=utf-8');
echo json_encode([
    'success' => true,
    'data' => $users,
    'pagination' => [
        'page' => $page,
        'pageSize' => $pageSize,
        'total' => $total,
        'totalPages' => (int)ceil($total / $pageSize),
    ],
], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
?>

進階:封裝成可復用的 Repository 類

<?php
class UserRepository
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function findPaginated(int $page, int $pageSize, array $filters = []): array
    {
        $where = [];
        $params = [];

        // 動態建構 WHERE
        if (!empty($filters['username'])) {
            $where[] = 'username LIKE :username';
            $params[':username'] = '%' . $filters['username'] . '%';
        }
        if (!empty($filters['status'])) {
            $where[] = 'status = :status';
            $params[':status'] = $filters['status'];
        }

        $whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        // 總數
        $total = (int)$this->pdo->query("SELECT COUNT(*) FROM userinfo $whereSql")->fetchColumn();

        // 分頁資料
        $offset = ($page - 1) * $pageSize;
        $sql = "SELECT id, username, email, created_at FROM userinfo $whereSql ORDER BY id DESC LIMIT :offset, :limit";
        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $k => $v) $stmt->bindValue($k, $v);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
        $stmt->execute();

        return [
            'data' => $stmt->fetchAll(),
            'pagination' => [
                'page' => $page,
                'pageSize' => $pageSize,
                'total' => $total,
                'totalPages' => (int)ceil($total / $pageSize),
            ],
        ];
    }
}

// 控制器使用
$repo = new UserRepository($pdo);
$result = $repo->findPaginated($page, $pageSize, ['username' => $_GET['q'] ?? '']);
echo json_encode(['success' => true, ...$result], JSON_UNESCAPED_UNICODE);
?>

常見問題對照表

舊寫法 (mysql_*)問題現代寫法 (PDO)
mysql_connect無預備語句、無例外處理new PDO(..., [ATTR_ERRMODE=>EXCEPTION])
mysql_query($sql)字串拼接 → SQL 注入$pdo->prepare + bindValue
mysql_fetch_assoc逐行取得,效能較差$stmt->fetchAll() 一次取完
SET NAMES 'UTF8'UTF8 非完整 Unicode (無 Emoji)charset=utf8mb4 + COLLATE utf8mb4_unicode_ci
json_encode($arr)中文會被 Unicode 轉義JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES
die('error')輸出 HTML/純文字,非 JSONhttp_response_code + json_encode(['error'=>...])

API 回應標準格式建議

<?php
// 成功
{
  "success": true,
  "data": [...],
  "pagination": { "page": 1, "pageSize": 10, "total": 100, "totalPages": 10 }
}

// 失敗
{
  "success": false,
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "頁碼必須大於 0",
    "details": { "page": "must be >= 1" }
  }
}

// 統一輸出函數
function jsonResponse(array $data, int $status = 200): never
{
    http_response_code($status);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}
?>

效能優化提示

  • 索引:WHERE、ORDER BY、JOIN 欄位必加索引
  • 只查需要欄位:SELECT * 會傳輸多餘資料、增加記憶體
  • 大量資料游標:PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false + fetch() 迴圈
  • 連線池:PHP-FPM + persistent connections (PDO::ATTR_PERSISTENT) 需謹慎評估
  • 快取:熱門查詢加 Redis 快取,TTL 30-300 秒

拋棄 mysql_*,擁抱 PDO + 預備語句 + JSON 標準輸出,是 PHP 後端開發的基本功。

copyright © 萬盛學電腦網 all rights reserved