PHP 数据库连接完全指南

在PHP网站开发中，数据库连接是核心功能之一。以下是几种主流的数据库连接方式及最佳实践：

一、MySQL 连接方式

1. MySQLi 面向过程方式

php

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检测连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
echo "连接成功";

// 执行查询
$sql = "SELECT id, name FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 结果";
}

// 关闭连接
mysqli_close($conn);
?>

2. MySQLi 面向对象方式

php

<?php
$conn = new mysqli("localhost", "username", "password", "myDB");

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "SELECT id, name FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 结果";
}

$conn->close();
?>

3. PDO 方式（推荐）

php

<?php
try {
    $conn = new PDO("mysql:host= ;dbname=myDB", "username", "password");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "SELECT id, name FROM users";
    $stmt = $conn->query($sql);
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
}
catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

$conn = null; // 关闭连接
?>

二、数据库连接最佳实践

1. 使用配置文件

config.php

php

<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'username');
define('DB_PASS', 'password');
define('DB_NAME', 'myDB');
?>

database.php

php

<?php
require_once 'config.php';

class Database {
    private static $instance = null;
    private $connection;
    
    private function __construct() {
        $this->connection = new PDO(
            "mysql:host=".DB_HOST.";dbname=".DB_NAME, 
            DB_USER, 
            DB_PASS
        );
        $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    
    public static function getInstance() {
        if(!self::$instance) {
            self::$instance = new Database();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->connection;
    }
}
?>

2. 使用连接池（适用于高并发）

php

// 使用单例模式模拟简单连接池
class ConnectionPool {
    private static $pool = [];
    private static $maxSize = 5;
    
    public static function getConnection() {
        if (empty(self::$pool)) {
            for ($i = 0; $i < self::$