PHP数据库学习之如何使用PDO执行SQL语句

发布时间：2021-10-28 13:30:29 作者：iii
来源：亿速云阅读：165

# PHP数据库学习之如何使用PDO执行SQL语句

## 前言

在PHP开发中，数据库操作是不可或缺的重要环节。随着PHP版本的更新迭代，传统的mysql_*函数已被废弃，而PDO（PHP Data Objects）作为新一代的数据库访问抽象层，凭借其安全、高效和跨数据库的特性，成为现代PHP开发的首选方案。本文将全面讲解如何使用PDO执行SQL语句，涵盖从基础连接到高级应用的完整知识体系。

## 一、PDO简介与优势

### 1.1 什么是PDO

PDO（PHP Data Objects）是PHP提供的一个数据库访问抽象层，它为访问不同数据库提供了一致的接口。无论您使用的是MySQL、PostgreSQL还是SQLite，都可以使用相同的函数来执行查询和获取数据。

### 1.2 PDO的主要优势

- **数据库无关性**：支持多种数据库驱动
- **预处理语句**：有效防止SQL注入
- **面向对象接口**：更符合现代编程习惯
- **错误处理**：提供多种错误处理模式
- **性能优化**：支持持久连接和批量操作
- **事务支持**：完整的事务控制能力

## 二、建立PDO连接

### 2.1 基本连接方式

```php
<?php
$host = 'localhost';
$dbname = 'test_db';
$username = 'root';
$password = 'password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    // 设置错误模式为异常模式
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功";
} catch(PDOException $e) {
    die("连接失败: " . $e->getMessage());
}
?>

2.2 连接选项配置

PDO构造函数支持第四个参数用于设置连接选项：

$options = [
    PDO::ATTR_PERSISTENT => true,    // 持久连接
    PDO::ATTR_EMULATE_PREPARES => false, // 禁用预处理模拟
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // 默认获取关联数组
];

$pdo = new PDO($dsn, $username, $password, $options);

2.3 不同数据库的DSN格式

MySQL: mysql:host=hostname;dbname=database;charset=utf8mb4
PostgreSQL: pgsql:host=hostname;dbname=database
SQLite: sqlite:/path/to/database.sqlite

三、执行SQL语句的基本方法

3.1 exec()方法 - 执行无结果集SQL

适合执行INSERT、UPDATE、DELETE等不返回结果集的SQL语句：

$affectedRows = $pdo->exec("UPDATE users SET status = 1 WHERE last_login < NOW() - INTERVAL 6 MONTH");
echo "影响了 $affectedRows 行";

3.2 query()方法 - 执行有结果集SQL

适合执行SELECT等返回结果集的SQL语句：

$stmt = $pdo->query("SELECT id, username FROM users WHERE status = 1");
while ($row = $stmt->fetch()) {
    echo $row['username'] . "<br>";
}

3.3 预处理语句prepare() + execute()

PDO的核心优势在于预处理语句，可以有效防止SQL注入：

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(['john_doe', 'john@example.com']);

四、预处理语句详解

4.1 参数绑定的三种方式

位置参数（?占位符）：

$stmt = $pdo->prepare("SELECT * FROM users WHERE id > ? AND status = ?");
$stmt->execute([10, 1]);

命名参数（:name形式）：

$stmt = $pdo->prepare("SELECT * FROM users WHERE id > :id AND status = :status");
$stmt->execute([':id' => 10, ':status' => 1]);

bindParam()方法绑定：

$stmt = $pdo->prepare("SELECT * FROM users WHERE id > :id");
$id = 10;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

4.2 参数类型常量

PDO提供了一系列类型常量用于指定参数类型： - PDO::PARAM_INT - PDO::PARAM_STR（默认） - PDO::PARAM_BOOL - PDO::PARAM_NULL

$stmt->bindValue(':limit', 10, PDO::PARAM_INT);

4.3 批量执行预处理语句

$data = [
    ['john', 'john@example.com'],
    ['jane', 'jane@example.com'],
    ['mike', 'mike@example.com']
];

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)");

foreach ($data as $row) {
    $stmt->execute($row);
}

五、获取查询结果

5.1 获取单行数据

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([1]);
$user = $stmt->fetch(); // 获取一行

5.2 获取所有数据

$stmt = $pdo->query("SELECT * FROM users");
$allUsers = $stmt->fetchAll();

5.3 获取列数据

$stmt = $pdo->query("SELECT username FROM users");
$usernames = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

5.4 获取键值对

$stmt = $pdo->query("SELECT id, username FROM users");
$userMap = $stmt->fetchAll(PDO::FETCH_KEY_PR);

5.5 获取对象形式结果

$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$users = $stmt->fetchAll();

六、事务处理

6.1 基本事务流程

try {
    $pdo->beginTransaction();
    
    $stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt1->execute([100, 1]);
    
    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt2->execute([100, 2]);
    
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "事务失败: " . $e->getMessage();
}

6.2 事务隔离级别

$pdo->setAttribute(PDO::ATTR_ISOLATION_LEVEL, PDO::TRANSACTION_READ_COMMITTED);

七、错误处理

7.1 错误模式设置

// 异常模式（推荐）
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 警告模式
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

// 静默模式（默认）
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

7.2 获取错误信息

$stmt = $pdo->prepare("SELECT * FROM non_existent_table");
if (!$stmt) {
    $error = $pdo->errorInfo();
    echo "错误代码: " . $error[1] . "<br>";
    echo "错误信息: " . $error[2];
}

八、高级应用技巧

8.1 分页查询

function getPaginatedUsers($pdo, $page = 1, $perPage = 10) {
    $offset = ($page - 1) * $perPage;
    $stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

8.2 IN语句处理

$ids = [1, 3, 5, 7];
$in = str_repeat('?,', count($ids) - 1) . '?';
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($in)");
$stmt->execute($ids);

8.3 存储过程调用

$stmt = $pdo->prepare("CALL sp_get_user_stats(?, @total, @active)");
$stmt->execute([1]);
$result = $pdo->query("SELECT @total AS total, @active AS active")->fetch();

九、安全最佳实践

始终使用预处理语句：防止SQL注入
禁用模拟预处理：$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
正确设置字符集：DSN中添加charset=utf8mb4
最小权限原则：数据库用户只授予必要权限
错误日志记录：生产环境不要显示错误给用户

十、性能优化建议

使用持久连接：减少连接开销
批量操作：合并多次操作为一次
合理使用事务：减少磁盘I/O
选择合适获取模式：避免不必要的数据转换
关闭连接：长时间不用的连接及时关闭

结语

PDO作为PHP官方推荐的数据库访问方式，提供了安全、高效且一致的数据库操作接口。通过本文的系统学习，您应该已经掌握了使用PDO执行SQL语句的全面知识。在实际开发中，建议结合项目需求选择最适合的操作方式，并始终将安全性放在首位。

随着PHP的持续发展，PDO也在不断优化和改进。建议定期查阅官方文档，了解最新的特性和最佳实践，以保持您的数据库访问代码既安全又高效。 “`

这篇文章涵盖了PDO从基础到高级的全面内容，包括： 1. 连接建立与配置 2. 各种SQL执行方法 3. 预处理语句详解 4. 结果获取方式 5. 事务与错误处理 6. 高级应用技巧 7. 安全与性能建议

全文约4000字，采用Markdown格式，结构清晰，适合作为学习资料或技术文档使用。