php语言怎样使用 PDO 连接多种数据库 php语言 PDO 数据库连接的详细教程指南

PDO

<?php
$dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $username, $password);
    // 设置错误模式为抛出异常，这是我个人强烈推荐的
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); // 默认关联数组取数据

    echo "MySQL 数据库连接成功！<br>";

    // 简单查询示例
    $stmt = $pdo->query("SELECT * FROM users LIMIT 1");
    $user = $stmt->fetch();
    if ($user) {
        echo "查询到用户: " . json_encode($user) . "<br>";
    } else {
        echo "未查询到用户。<br>";
    }

} catch (PDOException $e) {
    // 生产环境别直接输出错误信息，记录到日志里更安全
    echo "数据库连接失败或操作错误: " . $e->getMessage();
}
?>

<?php
$dsn_pg = 'pgsql:host=localhost;port=5432;dbname=your_pg_database;user=your_pg_username;password=your_pg_password';

try {
    $pdo_pg = new PDO($dsn_pg);
    $pdo_pg->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "PostgreSQL 数据库连接成功！<br>";
    // 这里可以继续你的PostgreSQL操作
} catch (PDOException $e) {
    echo "PostgreSQL 连接失败: " . $e->getMessage();
}
?>

mysql_*

// PDO 预处理
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");
$stmt->execute([':email' => $userEmail, ':status' => 1]);
$user = $stmt->fetch();

// 传统方式，容易被注入
$sql = "SELECT * FROM users WHERE email = '" . $_GET['email'] . "'"; // 如果没有正确转义，就危险了

PDOException

try-catch

new PDO(...)

config.php

database.php

// config/database.php
return [
    'mysql_main' => [
        'dsn' => 'mysql:host=localhost;dbname=main_db;charset=utf8mb4',
        'username' => 'user_main',
        'password' => 'pass_main',
    ],
    'pgsql_logs' => [
        'dsn' => 'pgsql:host=log_server;port=5432;dbname=logs_db;user=user_logs;password=pass_logs',
    ],
    // 更多数据库配置...
];

// app/Database/ConnectionFactory.php
class ConnectionFactory {
    private static $connections = [];
    private static $config = [];

    public static function setConfig(array $config) {
        self::$config = $config;
    }

    public static function get(string $name = 'mysql_main'): PDO {
        if (!isset(self::$config[$name])) {
            throw new Exception("数据库配置 '{$name}' 未找到。");
        }

        if (!isset(self::$connections[$name])) {
            $dbConfig = self::$config[$name];
            try {
                $pdo = new PDO(
                    $dbConfig['dsn'],
                    $dbConfig['username'] ?? null, // 允许没有用户名密码的情况，比如SQLite文件
                    $dbConfig['password'] ?? null
                );
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
                self::$connections[$name] = $pdo;
            } catch (PDOException $e) {
                // 生产环境这里应该记录日志
                throw new Exception("无法连接数据库 '{$name}': " . $e->getMessage());
            }
        }
        return self::$connections[$name];
    }
}

// 在你的应用入口（比如index.php或者bootstrap文件）
$dbConfig = require 'config/database.php';
ConnectionFactory::setConfig($dbConfig);

// 任何地方需要连接时
try {
    $mainDb = ConnectionFactory::get('mysql_main');
    // ... 对主数据库的操作
    $logDb = ConnectionFactory::get('pgsql_logs');
    // ... 对日志数据库的操作
} catch (Exception $e) {
    // 处理连接错误
    echo "应用启动失败: " . $e->getMessage();
}

PDOException

php_pdo_pgsql

php.ini

php.ini