PHP MySQL 连接
Establishing a PDO connection, exception handling, and correct parameters.
Key PointsKeep credentials in config, never hardcoded; always enable `ERRMODE_EXCEPTION`.
Connecting
PHP
<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8mb4";
try {
$pdo = new PDO($dsn, "root", "secret", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
} catch (PDOException $e) {
http_response_code(500);
error_log("数据库连接失败: " . $e->getMessage());
exit("服务暂时不可用");
}
echo "连接成功\n";
$n = (int)$pdo->query("SELECT 1")->fetchColumn();
echo $n === 1 ? "查询正常" : "查询异常", "\n";Output
连接成功
查询正常**Never echo
$e->getMessage()** — it can include host, user and even password. Log it, show a generic message.Using a config file
PHP
<?php
// config.php 内容：<?php return ["dsn" => "...", "user" => "...", "pass" => "..."];
$conf = require __DIR__ . "/config.php";
$pdo = new PDO($conf["dsn"], $conf["user"], $conf["pass"], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
echo "已连接: " . $conf["dsn"], "\n";Output
已连接: mysql:host=127.0.0.1;dbname=demo;charset=utf8mb4Better still, read from environment variables via getenv("DB_PASSWORD"). Keep config out of Git and inject secrets in the container.
Common connection failures
| Error | Cause | Fix |
|---|---|---|
| could not find driver | 没装 pdo_mysql | 装扩展并重启 PHP-FPM |
| Connection refused | MySQL 没启动或端口不通 | 查服务状态、防火墙 |
| Access denied | 用户名密码错或主机无权限 | 检查授权与 host 字段 |
| Unknown database | 库名写错或没建 | 先 CREATE DATABASE |
| Too many connections | 连接没释放，或并发过高 | 短连接用完即弃，或上连接池 |
| Cannot connect to 127.0.0.1 | localhost 走了 socket 而不是 TCP | 用 127.0.0.1 强制 TCP |
With
host=localhost the client tries a Unix socket and fails if the path is wrong. **Use 127.0.0.1 to force TCP** and rule out a whole class of issues.Persistent connections
PHP
<?php
// 持久连接：请求结束后不真正关闭，复用给下一个请求
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
echo "持久连接已开启\n";Output
持久连接已开启Persistent connections save handshakes but leak transaction state and temp tables across requests. An uncommitted transaction keeps holding locks. Commit or roll back explicitly every request.
Closing
PHP releases it at script end. To release early, set the variable to null; PDO's destructor closes the connection.
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
echo "连接中\n";
$pdo = null; // 立即释放
echo "已释放\n";Output
连接中
已释放建立连接
PHP
<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=demo;charset=utf8mb4";
try {
$pdo = new PDO($dsn, "root", "secret", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
} catch (PDOException $e) {
http_response_code(500);
error_log("数据库连接失败: " . $e->getMessage());
exit("服务暂时不可用");
}
echo "连接成功\n";
$n = (int)$pdo->query("SELECT 1")->fetchColumn();
echo $n === 1 ? "查询正常" : "查询异常", "\n";Output
连接成功
查询正常catch 里**不要把 $e->getMessage() 直接输出给用户**——它会带上主机、用户名、甚至密码。写日志，页面只给一句人话。用配置文件
PHP
<?php
// config.php 内容：<?php return ["dsn" => "...", "user" => "...", "pass" => "..."];
$conf = require __DIR__ . "/config.php";
$pdo = new PDO($conf["dsn"], $conf["user"], $conf["pass"], [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
echo "已连接: " . $conf["dsn"], "\n";Output
已连接: mysql:host=127.0.0.1;dbname=demo;charset=utf8mb4更规范的做法是从环境变量读：getenv("DB_PASSWORD")。配置文件不进 Git，容器里用 secret 注入。
连接失败常见原因
| 错误 | 原因 | 解决 |
|---|---|---|
| could not find driver | 没装 pdo_mysql | 装扩展并重启 PHP-FPM |
| Connection refused | MySQL 没启动或端口不通 | 查服务状态、防火墙 |
| Access denied | 用户名密码错或主机无权限 | 检查授权与 host 字段 |
| Unknown database | 库名写错或没建 | 先 CREATE DATABASE |
| Too many connections | 连接没释放，或并发过高 | 短连接用完即弃，或上连接池 |
| Cannot connect to 127.0.0.1 | localhost 走了 socket 而不是 TCP | 用 127.0.0.1 强制 TCP |
host=localhost 时 MySQL 客户端库会尝试 Unix socket，路径不对就报连接失败。**改用 127.0.0.1 强制走 TCP**，能排除一大类环境问题。持久连接
PHP
<?php
// 持久连接：请求结束后不真正关闭，复用给下一个请求
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
echo "持久连接已开启\n";Output
持久连接已开启持久连接能省下握手开销，但事务状态和临时表会跨请求残留。上一个请求没提交的事务会挂在那里，锁住行。用持久连接就必须保证每个请求都显式提交或回滚。
关闭连接
PHP 脚本结束时自动释放。要提前释放就把变量置 null——PDO 的析构函数会关掉连接。
PHP
<?php
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
echo "连接中\n";
$pdo = null; // 立即释放
echo "已释放\n";Output
连接中
已释放