PHP与MySQL数据库实战指南

PHP与MySQL数据库实战指南

PHP和MySQL是经典搭档。从最早的mysql扩展到mysqli再到PDO，PHP连接MySQL的方式一直在进化。今天说说在实际项目中怎么用好这对组合。

首先说连接方式。PDO是目前的首选，它支持多种数据库，使用预处理语句天然防止SQL注入。

```php
$host = 'localhost';
$dbname = 'test';
$username = 'root';
$password = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
];

try {
$pdo = new PDO($dsn, $username, $password, $options);
echo "数据库连接成功\n";
} catch (PDOException $e) {
die("连接失败: " . $e->getMessage());
}
?>
```

连接成功之后就是CRUD操作。增删改查各有对应的SQL语句。

```php
// 插入数据
$stmt = $pdo->prepare("
INSERT INTO users (name, email, age, status, created_at)
VALUES (:name, :email, :age, :status, NOW())
");

$stmt->execute([
':name' => '张三',
':email' => 'zhangsan@example.com',
':age' => 28,
':status' => 'active',
]);

$userId = $pdo->lastInsertId();
echo "插入成功，ID: $userId\n";

// 批量插入
$users = [
['李四', 'lisi@test.com', 25],
['王五', 'wangwu@test.com', 30],
['赵六', 'zhaoliu@test.com', 22],
];

$stmt = $pdo->prepare("
INSERT INTO users (name, email, age, status, created_at)
VALUES (?, ?, ?, 'active', NOW())
");

foreach ($users as $user) {
$stmt->execute($user);
}
echo "批量插入完成\n";
?>
```

查询数据是最常用的操作。PDO提供了多种获取模式。

```php
// 查询所有
$stmt = $pdo->query("SELECT * FROM users WHERE status = 'active'");
$users = $stmt->fetchAll();
echo "活跃用户数: " . count($users) . "\n";

foreach ($users as $user) {
echo "{$user['name']} ({$user['email']})\n";
}

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

if ($user) {
echo "用户: {$user['name']}, 年龄: {$user['age']}\n";
} else {
echo "用户不存在\n";
}

// 查询单列
$stmt = $pdo->query("SELECT name FROM users");
$names = $stmt->fetchAll(PDO::FETCH_COLUMN);
print_r($names);

// 查询单个值
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$count = $stmt->fetchColumn();
echo "用户总数: $count\n";

// 分组统计
$stmt = $pdo->query("
SELECT status, COUNT(*) as count
FROM users
GROUP BY status
");
$stats = $stmt->fetchAll();
print_r($stats);
?>
```

更新和删除操作需要谨慎，一定要带上WHERE条件。

```php
// 更新
$stmt = $pdo->prepare("UPDATE users SET age = :age, updated_at = NOW() WHERE id = :id");
$stmt->execute([':age' => 29, ':id' => 1]);
echo "更新了 {$stmt->rowCount()} 行\n";

// 删除
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);
echo "删除了 {$stmt->rowCount()} 行\n";
?>
```

事务处理保证多个操作要么全部成功，要么全部失败。

```php
// 转账示例
function transferMoney(PDO $pdo, int $fromId, int $toId, float $amount): void
{
try {
$pdo->beginTransaction();

// 扣钱
$stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
$stmt->execute([$amount, $fromId]);

// 检查余额
$stmt = $pdo->prepare("SELECT balance FROM accounts WHERE id = ?");
$stmt->execute([$fromId]);
$balance = $stmt->fetchColumn();

if ($balance < 0) {
throw new Exception('余额不足');
}

// 加钱
$stmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
$stmt->execute([$amount, $toId]);

// 记录日志
$stmt = $pdo->prepare("
INSERT INTO transfer_log (from_id, to_id, amount, created_at)
VALUES (?, ?, ?, NOW())
");
$stmt->execute([$fromId, $toId, $amount]);

$pdo->commit();
echo "转账成功\n";
} catch (Exception $e) {
$pdo->rollBack();
echo "转账失败: " . $e->getMessage() . "\n";
}
}

$pdo->beginTransaction();
// 各种操作...
$pdo->commit();
?>
```

分页查询在列表页中很常用。传统的OFFSET分页在数据量大时性能差，可以用游标分页替代。

```php
// 传统分页（OFFSET越大越慢）
function paginateOffset(PDO $pdo, int $page, int $perPage = 20): array
{
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare("
SELECT * FROM articles
ORDER BY created_at DESC
LIMIT ? OFFSET ?
");
$stmt->execute([$perPage, $offset]);
$items = $stmt->fetchAll();

$count = $pdo->query("SELECT COUNT(*) FROM articles")->fetchColumn();

return [
'items' => $items,
'total' => $count,
'page' => $page,
'per_page' => $perPage,
'total_pages' => ceil($count / $perPage),
];
}

// 游标分页（性能稳定）
function paginateCursor(PDO $pdo, ?int $cursor, int $perPage = 20): array
{
if ($cursor === null) {
$stmt = $pdo->prepare("
SELECT * FROM articles
ORDER BY id DESC
LIMIT ?
");
$stmt->execute([$perPage]);
} else {
$stmt = $pdo->prepare("
SELECT * FROM articles
WHERE id < ?
ORDER BY id DESC
LIMIT ?
");
$stmt->execute([$cursor, $perPage]);
}

$items = $stmt->fetchAll();
$nextCursor = !empty($items) ? end($items)['id'] : null;

return [
'items' => $items,
'next_cursor' => $nextCursor,
'has_more' => count($items) === $perPage,
];
}

// 使用游标分页
$result = paginateCursor($pdo, null, 20);
echo "获取了 " . count($result['items']) . " 篇文章\n";

if ($result['has_more']) {
echo "下一页游标: {$result['next_cursor']}\n";
}
?>
```

索引优化是数据库性能的关键。没有索引的查询会全表扫描，数据量大时很慢。

```php
// 创建索引
$pdo->exec("ALTER TABLE users ADD INDEX idx_email (email)");
$pdo->exec("ALTER TABLE users ADD INDEX idx_status_created (status, created_at)");
$pdo->exec("ALTER TABLE articles ADD FULLTEXT idx_content (title, content)");

// 使用索引的查询
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute(['test@example.com']);
// 这个查询会使用idx_email索引

// 全文搜索
$stmt = $pdo->prepare("
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
");
$stmt->execute(['+PHP +MySQL']);
$articles = $stmt->fetchAll();
?>
```

连接管理在生产环境要注意。每次请求都创建新连接开销大，可以考虑长连接或连接池。PHP-FPM模式下用持久连接要小心，因为连接状态可能被上一个请求污染。

总的来说，PHP操作MySQL用PDO就对了。预处理防止注入，事务保证一致性，索引提升性能。这些做好了，数据库层面基本没啥大问题。