深入解析PHP中各类数据库查询语句的语法、使用场景与最佳实践,涵盖MySQL、PDO、MySQLi等主流扩展
PHP作为服务端脚本语言,在Web开发中广泛应用于数据交互与业务逻辑处理。其核心能力之一是与数据库通信,而实现这一目标的关键桥梁便是数据库查询语句。在实际开发中,PHP通过扩展模块(如MySQL、MySQLi、PDO)连接数据库并执行SQL语句,实现对数据的增、删、改、查等操作。
从语法结构来看,php数据库查询语句本质上是标准SQL(Structured Query Language)在PHP环境中的具体应用。常见的语句类型包括:
值得注意的是,PHP本身不直接执行SQL,而是将语句通过扩展传递给数据库服务器(如MySQL),由数据库引擎解析、优化并返回结果。因此,理解php数据库查询语句不仅涉及PHP语法,还需掌握SQL逻辑与数据库行为特性。
在MySQL生态系统中,常见的查询语句结构如下:
SELECT column1, column2
FROM table_name
WHERE condition
GROUP BY column
HAVING filter_condition
ORDER BY column [ASC|DESC]
LIMIT offset, count;
例如,从users表中获取年龄大于18岁的用户姓名与邮箱,并按注册时间倒序排列前10条记录,对应的php数据库查询语句应为:
SELECT name, email
FROM users
WHERE age > 18
ORDER BY created_at DESC
LIMIT 10;
在PHP中,该语句需通过扩展封装执行,不同扩展(MySQL、MySQLi、PDO)在语法与安全性上存在显著差异,后文将逐一详解。
MySQL扩展是PHP早期版本中用于连接MySQL数据库的原生扩展,提供过程式与面向对象两种接口。然而,自PHP 5.5.0起已被标记为废弃(deprecated),并在PHP 7.0中完全移除。尽管如此,理解其工作原理对维护旧系统仍具参考价值。
使用MySQL扩展时,核心函数包括:
mysql_connect():建立与MySQL服务器的连接mysql_select_db():选择数据库mysql_query():执行SQL语句mysql_fetch_array():获取结果集mysql_close():关闭连接示例代码如下:
<?php
// 建立连接
$link = mysql_connect('localhost', 'root', 'password');
if (!$link) {
die('连接失败: ' . mysql_error());
}
// 选择数据库
mysql_select_db('test_db', $link);
// 执行查询
$sql = "SELECT id, username, email FROM users WHERE status = 'active'";
$result = mysql_query($sql);
if (!$result) {
die('查询失败: ' . mysql_error());
}
// 处理结果
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "ID: {$row['id']} | 用户: {$row['username']} | 邮箱: {$row['email']} <br>";
}
// 释放资源并关闭连接
mysql_free_result($result);
mysql_close($link);
?>
因此,强烈建议在新项目中使用MySQLi或PDO扩展替代MySQL扩展。
MySQLi(MySQL Improved)是MySQL扩展的继任者,专为PHP 5+设计,提供过程式与面向对象双接口,并全面支持预处理语句、事务、存储过程等现代数据库特性。
使用MySQLi的预处理语句是防止SQL注入的黄金标准。其核心流程为:
示例:安全查询用户信息
<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'test_db');
if ($mysqli->connect_error) {
die("连接失败: " . $mysqli->connect_error);
}
// 准备语句
$stmt = $mysqli->prepare("SELECT id, username, email FROM users WHERE email = ? AND status = ?");
// 绑定参数(s=string, i=integer, d=double, b=blob)
$stmt->bind_param("ss", $email, $status);
// 设置参数值
$email = "user@example.com";
$status = "active";
// 执行查询
$stmt->execute();
// 获取结果
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['id']} | 用户: {$row['username']} | 邮箱: {$row['email']} <br>";
}
// 关闭语句与连接
$stmt->close();
$mysqli->close();
?>
MySQLi支持事务操作,适用于需要原子性操作的场景(如转账、订单创建):
<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'test_db');
$mysqli->autocommit(FALSE); // 关闭自动提交
try {
// 转账操作:A账户扣款
$stmt1 = $mysqli->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
$stmt1->bind_param("di", $amount, $from_id);
$stmt1->execute();
// B账户加款
$stmt2 = $mysqli->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
$stmt2->bind_param("di", $amount, $to_id);
$stmt2->execute();
$mysqli->commit(); // 提交事务
echo "转账成功!";
} catch (Exception $e) {
$mysqli->rollback(); // 回滚事务
echo "转账失败: " . $e->getMessage();
}
$mysqli->autocommit(TRUE); // 恢复自动提交
?>
| 方法 | 返回类型 | 适用场景 |
|---|---|---|
fetch_assoc() | 关联数组 | 字段名访问 |
fetch_row() | 索引数组 | 位置访问 |
fetch_object() | 对象 | 面向对象风格 |
fetch_all() | 多行数组 | 一次性获取全部 |
PDO(PHP Data Objects)是PHP官方推荐的数据库抽象层,支持12种数据库驱动(MySQL、PostgreSQL、SQLite等),提供统一API,便于多数据库迁移。
PDO连接采用DSN(Data Source Name)格式指定数据库类型与参数:
$pdo = new PDO('mysql:host=localhost;dbname=test_db;charset=utf8mb4', 'root', 'password', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 抛出异常
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 默认返回关联数组
PDO::ATTR_EMULATE_PREPARES => false // 禁用模拟预处理
]);
PDO支持命名占位符与问号占位符:
// 命名占位符
$sql = "SELECT id, username FROM users WHERE email = :email AND status = :status";
$stmt = $pdo->prepare($sql);
$stmt->execute(['email' => 'user@example.com', 'status' => 'active']);
// 问号占位符
$sql = "SELECT * FROM products WHERE price BETWEEN ? AND ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([100, 500]);
PDO的事务处理更简洁,配合异常机制实现自动回滚:
try {
$pdo->beginTransaction();
$pdo->exec("INSERT INTO orders (user_id, total) VALUES (1, 299.99)");
$orderId = $pdo->lastInsertId();
$stmt = $pdo->prepare("INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)");
$stmt->execute([$orderId, 101, 2]);
$pdo->commit();
} catch (Exception $e) {
$pdo->rollBack();
error_log("事务失败: " . $e->getMessage());
}
fetch()每次返回一行,适合流式处理大结果集:
$stmt = $pdo->query("SELECT id, username FROM users");
while ($row = $stmt->fetch()) {
echo "ID: {$row['id']} | 用户: {$row['username']} <br>";
}
fetchAll()一次性获取所有行,适合小数据集:
$stmt = $pdo->query("SELECT * FROM categories");
$categories = $stmt->fetchAll();
foreach ($categories as $cat) {
echo $cat['name'] . "<br>";
}
fetchColumn(0)获取第一列,常用于统计:
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
$count = $stmt->fetchColumn();
echo "用户总数: {$count}";
通过fetch()映射到自定义类:
class User {
public $id;
public $name;
public $email;
}
$stmt = $pdo->query("SELECT id, username AS name, email FROM users");
while ($user = $stmt->fetch(PDO::FETCH_CLASS, 'User')) {
echo $user->name . "<br>";
}
针对MySQL,建议设置以下属性提升兼容性:
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'":设置字符集PDO::ATTR_CASE => PDO::CASE_NATURAL:保留字段名大小写PDO::ATTR_STRINGIFY_FETCHES => true:将数字转为字符串索引是加速查询的核心手段,但需避免滥用。以下场景需特别注意:
WHERE email = ?应建INDEX(email))INDEX(email(20))可能降低唯一性查看执行计划示例:
EXPLAIN SELECT id, username FROM users WHERE email = 'test@test.com';
关键输出字段:
| 字段 | 含义 | 优化建议 |
|---|---|---|
| type | 连接类型 | 避免ALL(全表扫描) |
| key | 使用的索引 | 应显示有效索引名 |
| rows | 扫描行数 | 越少越好 |
| Extra | 额外信息 | 警惕Using filesort/Using temporary |
以下语句可显著提升性能:
WHERE YEAR(created_at) = 2023应改为WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'对高频查询结果使用缓存层(如Redis、Memcached):
// 检查缓存
$cacheKey = "user_profile_{$userId}";
$data = redis()->get($cacheKey);
if (!$data) {
// 查询数据库
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$userId]);
$data = $stmt->fetch();
// 写入缓存(1小时过期)
redis()->setex($cacheKey, 3600, json_encode($data));
}
$user = json_decode($data, true);
| 错误码 | 错误信息 | 原因 | 解决方案 |
|---|---|---|---|
| 1062 | Duplicate entry 'xxx' for key 'PRIMARY' | 主键冲突 | 检查INSERT/UPDATE是否重复插入 |
| 1054 | Unknown column 'xxx' in 'field list' | 字段名拼写错误 | 核对表结构,使用反引号包裹特殊字段 |
| 1045 | Access denied for user 'xxx'@'xxx' | 权限不足 | 检查用户名/密码/主机限制 |
| 2006 | MySQL server has gone away | 连接超时 | 增大max_allowed_packet与wait_timeout |
| 1205 | Lock wait timeout exceeded | 锁等待超时 | 优化事务,减少锁持有时间 |
error_reporting(E_ALL);
ini_set('display_errors', 1);SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;try {
$stmt = $pdo->prepare($sql);
$stmt->execute();
} catch (PDOException $e) {
error_log("SQL: {$sql} | Error: " . $e->getMessage());
}| 防护方式 | 原理 | PHP实现 | 注意事项 |
|---|---|---|---|
| 预处理语句 | 参数与SQL分离 | mysqli/pdo prepare() | 禁用模拟预处理 |
| 参数化查询 | 绑定变量 | bind_param()/execute([vals]) | 勿拼接用户输入 |
| 输入验证 | 白名单过滤 | filter_var() / ctype_alnum() | 结合正则校验 |
| 最小权限原则 | 数据库账户限制 | GRANT SELECT, INSERT... | 避免使用root |
password_hash()与password_verify()mysqli_real_escape_string()// 错误做法(危险!)
$sql = "SELECT * FROM products WHERE name LIKE '%" . $_GET['q'] . "%'";
// 正确做法
$searchTerm = trim($_GET['q']);
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$stmt->execute(['%' . $searchTerm . '%']);
$products = $stmt->fetchAll();