PHP与MySQL交互最佳实践

PHP与MySQL交互最佳实践

PHP和MySQL是经典搭配。PDO是现代PHP操作数据库的标准方式。今天说说PHP与MySQL交互的各种实践。

PDO连接配置。

```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操作。

```php
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->execute(['张三', 'zhangsan@test.com', 28]);
$userId = $pdo->lastInsertId();

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

$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?");
$stmt->execute(['active']);
$users = $stmt->fetchAll();

$stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->execute(['李四', 1]);

$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);
?>

事务处理。

```php
function transferMoney(PDO $pdo, int $fromId, int $toId, float $amount): void
{
try {
$pdo->beginTransaction();
$pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")->execute([$amount, $fromId]);

$stmt = $pdo->prepare("SELECT balance FROM accounts WHERE id = ?");
$stmt->execute([$fromId]);
if ($stmt->fetchColumn() < 0) throw new Exception("余额不足");

$pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")->execute([$amount, $toId]);
$pdo->prepare("INSERT INTO transfer_log (from_id, to_id, amount) VALUES (?, ?, ?)")->execute([$fromId, $toId, $amount]);

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

游标分页。

```php
function paginate(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];
}
?>

连接管理在生产环境要注意。每次请求都创建新连接开销大。PHP-FPM模式下用持久连接要小心，连接状态可能被上一个请求污染。一般来说用PDO默认配置就行，不需要额外优化。

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