php执行sql语句

权威指南 · 从入门到精通的数据库操作实践手册

⚡php执行sql语句基础入门

在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();
?>

此示例展示了:

  1. 创建MySQLi对象并连接数据库
  2. 构造SELECT语句
  3. 执行query()方法获取结果集
  4. 遍历结果并输出
  5. 错误处理机制

值得注意的是,query()仅适用于无参数或简单字符串拼接的SELECT语句;对于含用户输入的SQL,必须使用预处理语句防止SQL注入。

〈基础执行流程〉

标准的SQL执行流程如下:

  1. 建立数据库连接(connection)
  2. 准备SQL语句(statement)
  3. 绑定参数(若使用预处理)
  4. 执行语句(execute)
  5. 处理结果(fetch / affected_rows)
  6. 关闭连接(close)

任一环节出错均可能导致执行失败或数据异常,因此需在每一步加入异常捕获与日志记录。

⚙️PDO:统一数据库访问接口

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〕面向过程与面向对象双模式

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注入防护与安全编码规范

SQL注入是Web安全的头号威胁。攻击者通过构造恶意SQL语句,可绕过认证、窃取数据甚至删除整个数据库。2023年OWASP Top 10仍将SQL注入列为A03:2021注入类风险首位。

常见注入场景:

  1. 拼接用户输入:$sql = "SELECT * FROM users WHERE name = '" . $_GET['name'] . "'"
  2. 未过滤特殊字符:单引号、分号、注释符(--)
  3. 错误处理泄露信息:error_log暴露SQL结构

【错误示例】

<?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'

将返回所有用户数据!

【正确防护方案】

①预处理语句(Prepared Statements)

将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";
?>

▲性能优化:从100ms到10ms的SQL执行提速

数据库查询慢往往导致页面响应延迟,影响用户体验与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; -- 极慢!

优化方案:

  1. 基于游标分页(记录上一页最后一条ID)
  2. 使用覆盖索引(只查必要字段)

【优化后】

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));
}
?>

↖错误排查:常见SQL执行异常与解决方案

⚡1064 - You have an error in your SQL syntax
原因:SQL语法错误,常见于引号未闭合、关键字拼写错误、缺少逗号
解决:使用PDO::ATTR_EMULATE_PREPARES => false强制使用原生预处理;检查SQL格式
⚡2006 - MySQL server has gone away
原因:连接超时(wait_timeout)、发送数据包过大(max_allowed_packet)
解决:调整配置或使用长连接;分批处理大数据
⚡1205 - Lock wait timeout exceeded
原因:事务死锁或锁等待超时
解决:优化事务粒度;使用NOWAIT;分析SHOW ENGINE INNODB STATUS
⚡1054 - Unknown column in 'field list'
原因:字段名拼写错误或表结构变更
解决:检查字段名;使用ORM自动同步结构
⚡1406 - Data too long for column
原因:插入数据超出字段长度限制
解决:截断处理;调整字段类型(如VARCHAR(255)→TEXT)

♀网友常见问题(FAQ)

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中。