PHP 数据库操作：PDO 与 MySQLi 实战指南

两种连接方式对比

PHP连接MySQL有两套API：PDO（数据对象层，数据库无关）和MySQLi（MySQL改进版，专为MySQL设计）。推荐新手从PDO入手。

一、PDO 连接数据库

1. 建立连接

$host = '127.0.0.1';
$dbname = 'myapp';
$user = 'root';
$pass = 'password';
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo '连接成功';
} catch (PDOException $e) {
    die('数据库连接失败：' . $e->getMessage());
}

2. 设置连接参数（推荐）

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $options);

二、基础增删改查

3. 插入数据（INSERT）

// 直接执行
$pdo->exec("INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com')");
echo $pdo->lastInsertId();

// 预处理（推荐，防注入）
$stmt = $pdo->prepare('INSERT INTO users (name, email, age) VALUES (:name, :email, :age)');
$stmt->execute([
    'name'  => '李四',
    'email' => 'lisi@example.com',
    'age'   => 25,
]);
echo $stmt->rowCount();

4. 查询数据（SELECT）

// 查询单条
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => 1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// 查询多条
$stmt = $pdo->prepare('SELECT * FROM users WHERE age > :age ORDER BY id DESC');
$stmt->execute(['age' => 20]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 获取一列值
$names = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_COLUMN);

5. 更新数据（UPDATE）

$stmt = $pdo->prepare('UPDATE users SET name = :name, age = :age WHERE id = :id');
$stmt->execute([
    'name' => '王五',
    'age'  => 30,
    'id'   => 1,
]);
echo $stmt->rowCount();

6. 删除数据（DELETE）

$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute(['id' => 5]);
echo $stmt->rowCount();

三、预处理语句详解

7. 占位符语法

有两种占位符：命名参数（:name）和问号（?）：

// 命名参数（推荐，清晰）
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = :name AND age > :age');
$stmt->execute(['name' => '张三', 'age' => 20]);

// 问号参数
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = ? AND age > ?');
$stmt->execute(['张三', 20]);

8. 批量插入（高性能）

$users = [
    ['name'=>'张三', 'email'=>'z1@example.com'],
    ['name'=>'李四', 'email'=>'l2@example.com'],
    ['name'=>'王五', 'email'=>'w3@example.com'],
];

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
foreach ($users as $user) {
    $stmt->execute($user);
}

四、事务处理

9. 事务（Transaction）

事务确保一组操作要么全部成功，要么全部回滚：

try {
    $pdo->beginTransaction();

// 扣钱
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE user_id = ?')
        ->execute([100, 1]);
// 加钱
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE user_id = ?')
        ->execute([100, 2]);
// 记录日志
    $pdo->prepare('INSERT INTO logs (message) VALUES (?)')
        ->execute(['转账100元']);
$pdo->commit();
    echo '操作成功';
} catch (Exception $e) {
    $pdo->rollBack();
    die('操作失败：' . $e->getMessage());
}

五、MySQLi 用法

10. MySQLi 连接

$mysqli = new mysqli('127.0.0.1', 'root', 'password', 'myapp');
if ($mysqli->connect_error) {
    die('连接失败：' . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');

11. MySQLi 预处理

$stmt = $mysqli->prepare('SELECT * FROM users WHERE name = ? AND age > ?');
$stmt->bind_param('si', $name, $age); // s=string, i=integer
$name = '张三';
$age = 20;
$stmt->execute();
$result = $stmt->get_result();
$users = $result->fetch_all(MYSQLI_ASSOC);

六、实用技巧

12. 分页查询

$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare('SELECT * FROM articles ORDER BY id DESC LIMIT :limit OFFSET :offset');
$stmt->bindValue('limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue('offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$articles = $stmt->fetchAll();

13. 模糊搜索

$keyword = '%' . $_GET['keyword'] . '%';
$stmt = $pdo->prepare('SELECT * FROM articles WHERE title LIKE ? LIMIT 10');
$stmt->execute([$keyword]);
$results = $stmt->fetchAll();

14. 统计查询

// 总数
$total = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

// 分组统计
$stmt = $pdo->query('SELECT category, COUNT(*) as cnt FROM articles GROUP BY category');
$stats = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

15. 关联查询（JOIN）

$stmt = $pdo->prepare('
    SELECT a.title, a.views, u.name as author
    FROM articles a
    LEFT JOIN users u ON a.user_id = u.id
    WHERE a.id = :id
');
$stmt->execute(['id' => 1]);
$article = $stmt->fetch(PDO::FETCH_ASSOC);

七、ORM思想简介

直接写SQL在大项目中会变得难以维护，ORM（对象关系映射）将数据库表映射为类：

// 不用ORM：手写SQL
$pdo->prepare('UPDATE users SET name=? WHERE id=?')->execute([$name, $id]);

// 用ORM（如Eloquent）：把数据库记录当作对象操作
$user = User::find($id);
$user->name = $name;
$user->save();
// 链式查询
$users = User::where('age', '>', 20)->orderBy('id', 'desc')->limit(10)->get();

总结

• PDO是更现代的选择，支持多种数据库，代码可移植性好


• 永远用预处理语句，杜绝SQL注入


• 事务用于保证数据一致性（如转账、订单处理）


• fetch() vs fetchAll()：查一条用fetch，查多条用fetchAll


• rowCount()可以知道INSERT/UPDATE/DELETE影响了几行


• 随着项目变大，考虑引入ORM框架（如Laravel Eloquent）提升开发效率