权威指南 · 从入门到精通的数据库操作实践手册
在PHP开发中,执行SQL语句是与数据库交互的核心操作。无论是增删改查还是复杂的数据分析,都依赖于对SQL语句的正确执行。PHP提供了多种方式执行SQL语句,其中最常用的是通过PDO(PHP Data Objects)和MySQLi扩展。
在开始之前,我们需明确几个关键概念:
以下是一个基础的MySQL连接与执行示例(使用MySQLi面向对象方式):
<?php
$mysqli = new mysqli("localhost", "root", "password", "testdb");
if ($mysqli->connect_error) {
die("连接失败: " . $mysqli->connect_error);
}
$sql = "SELECT id, name FROM users WHERE status = 1";
$result = $mysqli->query($sql);
if ($result) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " - 姓名: " . $row['name'] . "<br>";
}
} else {
echo "查询失败: " . $mysqli->error;
}
$mysqli->close();
?>
此示例展示了:
值得注意的是,query()仅适用于无参数或简单字符串拼接的SELECT语句;对于含用户输入的SQL,必须使用预处理语句防止SQL注入。
标准的SQL执行流程如下:
任一环节出错均可能导致执行失败或数据异常,因此需在每一步加入异常捕获与日志记录。
PDO是PHP官方推荐的数据库抽象层,支持12种数据库驱动(如mysql、pgsql、sqlite、oracle等),提供统一API调用方式。其优势在于:
以下是使用PDO执行SQL的标准流程:
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb;charset=utf8mb4", "root", "password", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
// 插入数据(带预处理)
$stmt = $pdo->prepare("INSERT INTO users (name, email, created_at) VALUES (:name, :email, NOW())");
$stmt->execute(['name' => '张三', 'email' => 'zhangsan@example.com']);
echo "插入成功,ID:" . $pdo->lastInsertId();
// 查询数据
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => 'zhangsan@example.com']);
$user = $stmt->fetch();
print_r($user);
} catch (PDOException $e) {
echo "数据库错误: " . $e->getMessage();
}
?>
关键点说明:
对于批量操作,可使用事务确保数据一致性:
<?php
try {
$pdo->beginTransaction();
$stmt1 = $pdo->prepare("INSERT INTO orders (user_id, amount) VALUES (?, ?)");
$stmt1->execute([1001, 299.99]);
$orderId = $pdo->lastInsertId();
$stmt2 = $pdo->prepare("UPDATE users SET order_count = order_count + 1 WHERE id = ?");
$stmt2->execute([1001]);
$pdo->commit();
echo "订单创建成功";
} catch (Exception $e) {
$pdo->rollBack();
echo "事务失败: " . $e->getMessage();
}
?>
事务机制确保“要么全部成功,要么全部回滚”,是金融级应用的必备特性。
MySQLi(MySQL Improved)是专为MySQL设计的扩展,支持面向对象与面向过程两种风格,性能略优于PDO(仅限MySQL场景)。其特色功能包括:
【面向对象示例】
<?php
$mysqli = new mysqli("localhost", "root", "password", "testdb");
// 设置字符集
$mysqli->set_charset("utf8mb4");
// 预处理插入
$stmt = $mysqli->prepare("INSERT INTO logs (level, message, created_at) VALUES (?, ?, NOW())");
$stmt->bind_param("ss", $level, $msg);
$level = "WARNING";
$msg = "用户登录失败";
$stmt->execute();
echo "日志ID:" . $stmt->insert_id;
// 查询结果集
$stmt = $mysqli->prepare("SELECT id, title FROM articles WHERE status = ?");
$stmt->bind_param("i", $status);
$status = 1;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo $row['title'] . "<br>";
}
$stmt->close();
$mysqli->close();
?>
【面向过程示例】
<?php
$connection = mysqli_connect("localhost", "root", "password", "testdb");
mysqli_set_charset($connection, "utf8mb4");
$sql = "SELECT * FROM products WHERE price > 100";
$result = mysqli_query($connection, $sql);
if ($result) {
while ($row = mysqli_fetch_assoc($result)) {
echo $row['name'] . " - ¥" . $row['price'] . "<br>";
}
} else {
echo "查询失败:" . mysqli_error($connection);
}
mysqli_close($connection);
?>
注意事项:
SQL注入是Web安全的头号威胁。攻击者通过构造恶意SQL语句,可绕过认证、窃取数据甚至删除整个数据库。2023年OWASP Top 10仍将SQL注入列为A03:2021注入类风险首位。
常见注入场景:
【错误示例】
<?php
// 危险!直接拼接
$name = $_POST['username'];
$sql = "SELECT * FROM users WHERE username = '$name'";
$result = $mysqli->query($sql);
?>
若用户输入:admin' OR '1'='1,则查询变为:
SELECT * FROM users WHERE username = 'admin' OR '1'='1'
将返回所有用户数据!
【正确防护方案】
将SQL结构与数据分离,参数仅作为值传递,不参与语法解析:
<?php
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $name);
$stmt->execute();
$result = $stmt->get_result();
?>
即使输入admin' OR '1'='1,也仅被当作普通字符串处理,无法破坏SQL结构。
对数字型参数强制转型:
<?php
$id = (int)$_GET['id']; // 强制转为整数
$stmt = $pdo->prepare("DELETE FROM sessions WHERE id = ?");
$stmt->execute([$id]);
?>
数据库账号应仅授予必要权限:
生产环境禁止直接输出错误信息:
<?php
// 错误方式
try {
$pdo->query($sql);
} catch (PDOException $e) {
echo "错误:" . $e->getMessage(); // 泄露表结构、字段名
}
// 正确方式
try {
$pdo->query($sql);
} catch (PDOException $e) {
error_log("DB Error: " . $e->getMessage()); // 记录日志
http_response_code(500);
echo "系统繁忙,请稍后再试";
}
?>
对枚举值使用白名单校验:
<?php
$sort = $_GET['sort'] ?? 'id';
$allowedSort = ['id', 'created_at', 'price'];
if (!in_array($sort, $allowedSort)) {
$sort = 'id';
}
$sql = "SELECT * FROM products ORDER BY $sort";
?>
数据库查询慢往往导致页面响应延迟,影响用户体验与SEO排名。以下从SQL层面与PHP层面双维度优化:
无索引查询将导致全表扫描(Full Table Scan),在百万级数据下性能急剧下降:
【索引创建示例】
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
ALTER TABLE products ADD INDEX idx_price_category (price, category_id);
使用EXPLAIN分析执行计划:
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid';
-- 结果中type=ref, key=idx_user_status 表示索引生效
传统分页LIMIT 0,20在大数据量下性能差:
SELECT * FROM logs ORDER BY id DESC LIMIT 100000, 20; -- 极慢!
优化方案:
【优化后】
SELECT id, title, created_at FROM logs
WHERE id > 123456
ORDER BY id ASC
LIMIT 20;
重复执行相同SQL时,预处理语句可避免重复解析:
<?php
$stmt = $pdo->prepare("INSERT INTO logs (level, message) VALUES (?, ?)");
foreach ($logs as $log) {
$stmt->execute([$log['level'], $log['msg']]);
}
?>
比循环内拼接SQL快30%以上。
频繁建立/关闭连接开销大,可启用持久连接(需谨慎):
<?php
// PDO持久连接
$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_PERSISTENT => true]);
// MySQLi持久连接
$mysqli = new mysqli("p:localhost", $user, $pass, $db); // 注意:p:前缀
?>
⚠️注意:持久连接可能导致连接状态残留,需在每次查询前reset connection。
对频繁读取、低频更新的数据使用缓存:
<?php
$cacheKey = 'article_list_' . $page;
$articles = $redis->get($cacheKey);
if (!$articles) {
$articles = $pdo->query("SELECT * FROM articles LIMIT 20")->fetchAll();
$redis->setex($cacheKey, 3600, json_encode($articles));
}
?>
Q1:PHP执行SQL语句后如何获取自增ID?
答:MySQLi使用$stmt->insert_id;PDO使用$pdo->lastInsertId()。
Q2:如何执行多条SQL语句?
答:MySQLi支持multi_query(),但存在注入风险;推荐使用事务包裹多条语句。
Q3:中文乱码如何解决?
答:确保三处编码一致:
①数据库表使用utf8mb4字符集
②连接时执行SET NAMES utf8mb4
③PHP文件保存为UTF-8无BOM格式
Q4:SELECT * 和指定字段有何区别?
答:SELECT * 会增加网络传输量,且当表结构变更时易出错;推荐明确指定必要字段。
Q5:如何避免SQL注入中的“二次注入”?
答:对存入数据库的数据也需验证;避免直接拼接数据库取出的字段到新SQL中。