PHP数据库操作实战：SQL语句与安全优化指南

发布时间：2026/7/22 4:21:53

1. PHP常用SQL语句实战指南

作为PHP开发者，与数据库交互是日常开发中最频繁的操作之一。我整理了15年开发经验中最实用的SQL语句模板，涵盖增删改查、事务处理、预处理等核心场景，每个案例都经过生产环境验证。

1.1 基础查询语句

// 简单查询（MySQLi面向对象方式） $conn = new mysqli("localhost", "user", "password", "dbname"); $sql = "SELECT id, username FROM users WHERE status=1 LIMIT 10"; $result = $conn->query($sql); while($row = $result->fetch_assoc()) { echo "ID: {$row['id']}, Name: {$row['username']}"; } // 带参数查询（PDO预处理方式） $pdo = new PDO("mysql:host=localhost;dbname=dbname", "user", "password"); $stmt = $pdo->prepare("SELECT * FROM products WHERE price > :price AND stock > 0"); $stmt->execute([':price' => 100]); $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

关键点：PDO预处理能有效防止SQL注入，建议所有动态参数都采用预处理方式

1.2 数据操作语句

插入数据：

// 单条插入 $stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (?, ?)"); $stmt->execute(['john_doe', 'john@example.com']); // 批量插入（PDO事务方式） $pdo->beginTransaction(); try { $stmt = $pdo->prepare("INSERT INTO log (action, user_id) VALUES (?, ?)"); foreach ($logs as $log) { $stmt->execute([$log['action'], $log['user_id']]); } $pdo->commit(); } catch (Exception $e) { $pdo->rollBack(); throw $e; }

更新数据：

// 条件更新 $stmt = $pdo->prepare("UPDATE orders SET status = ? WHERE id = ?"); $stmt->execute(['shipped', $orderId]); // 增量更新 $pdo->exec("UPDATE products SET stock = stock - 1 WHERE id = 123");

删除数据：

// 软删除（推荐做法） $pdo->exec("UPDATE comments SET deleted_at = NOW() WHERE id = 456"); // 物理删除 $stmt = $pdo->prepare("DELETE FROM temp_files WHERE created_at < ?"); $stmt->execute([date('Y-m-d', strtotime('-30 days'))]);

2. 高级查询技巧

2.1 复杂查询构建

// 多表联查 $sql = "SELECT u.username, o.order_no, o.total_amount FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1 AND o.create_time BETWEEN :start AND :end ORDER BY o.total_amount DESC LIMIT 20"; // 分组统计 $sql = "SELECT product_id, COUNT(*) as total_orders, SUM(amount) as total_sales FROM order_items GROUP BY product_id HAVING total_orders > 5";

2.2 分页查询优化

// 传统分页（不推荐） $page = 2; $perPage = 10; $offset = ($page - 1) * $perPage; $sql = "SELECT * FROM articles LIMIT $offset, $perPage"; // 高性能分页（推荐） $lastId = 15; // 上一页最后记录的ID $sql = "SELECT * FROM articles WHERE id > $lastId ORDER BY id ASC LIMIT $perPage";

经验：大数据量分页避免使用OFFSET，改用WHERE条件过滤

3. 事务与锁机制

3.1 事务处理模板

// MySQLi事务示例 $mysqli->begin_transaction(); try { $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1"); $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2"); $mysqli->commit(); } catch (Exception $e) { $mysqli->rollback(); throw $e; } // PDO事务示例 $pdo->beginTransaction(); try { $stmt1 = $pdo->prepare("INSERT INTO orders (...) VALUES (...)"); $stmt2 = $pdo->prepare("INSERT INTO order_items (...) VALUES (...)"); $stmt1->execute([...]); $orderId = $pdo->lastInsertId(); foreach ($items as $item) { $stmt2->execute([...]); } $pdo->commit(); } catch (Exception $e) { $pdo->rollBack(); throw $e; }

3.2 锁机制应用

// 悲观锁（查询时锁定） $pdo->exec("SELECT * FROM inventory WHERE product_id = 123 FOR UPDATE"); // 乐观锁（版本号控制） $pdo->exec("UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 123 AND version = $currentVersion");

4. 性能优化语句

4.1 索引优化提示

// 强制索引使用 $sql = "SELECT * FROM users FORCE INDEX (idx_status) WHERE status = 1"; // 忽略索引 $sql = "SELECT * FROM users IGNORE INDEX (idx_name) WHERE name LIKE '%john%'";

4.2 查询缓存控制

// 禁用查询缓存 $sql = "SELECT SQL_NO_CACHE * FROM large_table"; // 大数据导出优化 $sql = "SELECT * FROM huge_table INTO OUTFILE '/tmp/export.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'";

5. 安全防护实践

5.1 SQL注入防御

// 错误做法（拼接SQL） $sql = "SELECT * FROM users WHERE id = " . $_GET['id']; // 危险！ // 正确做法（PDO预处理） $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?"); $stmt->execute([$_GET['id']]); // 存储过程调用 $stmt = $pdo->prepare("CALL sp_get_user_by_id(?)"); $stmt->execute([$userId]);

5.2 敏感数据保护

// 密码哈希处理 $hashedPassword = password_hash($plainPassword, PASSWORD_DEFAULT); $stmt = $pdo->prepare("UPDATE users SET password = ? WHERE id = ?"); $stmt->execute([$hashedPassword, $userId]); // 数据加密存储 $encryptedData = openssl_encrypt($data, 'AES-256-CBC', $key); $stmt = $pdo->prepare("INSERT INTO sensitive_data (encrypted_content) VALUES (?)"); $stmt->execute([$encryptedData]);

6. 实用代码片段

6.1 数据库元信息查询

// 获取表结构 $stmt = $pdo->query("DESCRIBE users"); $columns = $stmt->fetchAll(PDO::FETCH_ASSOC); // 查询数据库大小 $sql = "SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = 'your_db' ORDER BY (data_length + index_length) DESC";

6.2 批量操作优化

// 批量更新（CASE WHEN方式） $sql = "UPDATE products SET price = CASE id WHEN 1 THEN 19.99 WHEN 2 THEN 29.99 WHEN 3 THEN 39.99 END, updated_at = NOW() WHERE id IN (1,2,3)"; // ON DUPLICATE KEY UPDATE $sql = "INSERT INTO user_stats (user_id, login_count) VALUES (1,1), (2,1), (3,1) ON DUPLICATE KEY UPDATE login_count = login_count + 1, last_login = NOW()";

7. 特殊场景处理

7.1 JSON数据处理（MySQL 5.7+）

// JSON字段查询 $sql = "SELECT id, JSON_EXTRACT(profile, '$.address.city') AS city FROM users WHERE JSON_CONTAINS(profile, '\"developer\"', '$.tags')"; // JSON字段更新 $sql = "UPDATE users SET profile = JSON_SET(profile, '$.phone', '123456789') WHERE id = 1";

7.2 全文检索实现

// 创建全文索引 $pdo->exec("ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, content)"); // 全文检索查询 $stmt = $pdo->prepare(" SELECT id, title, MATCH(title, content) AGAINST(:keyword) AS score FROM articles WHERE MATCH(title, content) AGAINST(:keyword IN BOOLEAN MODE) ORDER BY score DESC "); $stmt->execute([':keyword' => '+php -java']);

8. 调试与日志

8.1 SQL调试技巧

// 获取最后执行的SQL（PDO） $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?"); $stmt->execute([$userId]); $debugSql = $stmt->queryString; // 含占位符的SQL // 记录慢查询 $pdo->exec("SET GLOBAL slow_query_log = 'ON'"); $pdo->exec("SET GLOBAL long_query_time = 1");

8.2 查询日志分析

// 开启查询日志 $pdo->exec("SET GLOBAL general_log = 'ON'"); $pdo->exec("SET GLOBAL log_output = 'TABLE'"); // 分析日志 $sql = "SELECT * FROM mysql.general_log WHERE argument LIKE 'SELECT%' ORDER BY event_time DESC LIMIT 100";

9. 连接与配置

9.1 连接参数优化

// PDO连接配置示例 $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, // 禁用预处理模拟 PDO::ATTR_PERSISTENT => true, // 持久化连接 PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4", PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true ]; $pdo = new PDO( "mysql:host=localhost;dbname=test;charset=utf8mb4", "username", "password", $options );

9.2 连接池实现

// 简易连接池类 class ConnectionPool { private $pool; private $config; public function __construct($config, $size = 10) { $this->config = $config; for ($i = 0; $i < $size; $i++) { $this->pool[] = new PDO(...$config); } } public function getConnection() { if (empty($this->pool)) { throw new RuntimeException("No available connections"); } return array_pop($this->pool); } public function releaseConnection($conn) { $this->pool[] = $conn; } }

10. 跨数据库兼容

10.1 分页语法差异处理

function getPageData($dbType, $page, $perPage) { $offset = ($page - 1) * $perPage; switch ($dbType) { case 'mysql': return "LIMIT $offset, $perPage"; case 'postgresql': return "LIMIT $perPage OFFSET $offset"; case 'oracle': return "OFFSET $offset ROWS FETCH NEXT $perPage ROWS ONLY"; default: throw new InvalidArgumentException("Unsupported database type"); } }

10.2 日期函数兼容

function getDateFunction($dbType) { switch ($dbType) { case 'mysql': return ['NOW()', 'DATE_ADD(NOW(), INTERVAL 1 DAY)']; case 'postgresql': return ['NOW()', 'NOW() + INTERVAL \'1 day\'']; case 'sqlite': return ['datetime(\'now\')', 'datetime(\'now\', \'+1 day\')']; default: return [date('Y-m-d H:i:s'), date('Y-m-d H:i:s', strtotime('+1 day'))]; } }

11. 实战经验总结

预处理语句必用：所有动态参数必须使用预处理，这是防止SQL注入的第一道防线
事务粒度控制：
- 短事务：单个业务操作（如扣减库存）
- 长事务：跨多个表的业务流水（如订单创建）
- 避免在事务中包含远程调用等耗时操作
索引使用原则：
- 为WHERE、JOIN、ORDER BY字段建索引
- 避免过度索引，影响写入性能
- 定期使用EXPLAIN分析慢查询
连接管理要点：
- 用完立即关闭或归还连接池
- 设置合理的连接超时时间
- 生产环境建议使用连接池
错误处理规范：
- 捕获特定异常（如PDOException）
- 记录完整的错误上下文（SQL、参数等）
- 对用户显示友好提示，日志记录详细错误

12. 性能监控与优化

12.1 查询性能分析

// EXPLAIN分析 $stmt = $pdo->prepare("EXPLAIN SELECT * FROM large_table WHERE status = ?"); $stmt->execute([1]); $explainResult = $stmt->fetchAll(); // 基准测试 $start = microtime(true); for ($i = 0; $i < 100; $i++) { // 执行查询 } $elapsed = microtime(true) - $start;

12.2 索引优化建议

// 查找未使用索引 $sql = "SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 AND object_schema NOT IN ('mysql', 'performance_schema')";

13. 备份与恢复

13.1 数据备份语句

// 导出表结构 $pdo->exec("SELECT * INTO OUTFILE '/backup/users.csv' FIELDS TERMINATED BY ',' FROM users"); // 生成备份脚本 $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN); foreach ($tables as $table) { $createTable = $pdo->query("SHOW CREATE TABLE $table")->fetchColumn(1); file_put_contents("/backup/schema.sql", "$createTable;\n\n", FILE_APPEND); }

13.2 数据恢复方案

// 从CSV导入 $pdo->exec("LOAD DATA INFILE '/backup/users.csv' INTO TABLE users FIELDS TERMINATED BY ','"); // 事务恢复点 $pdo->exec("SAVEPOINT before_batch_update"); try { // 批量操作 $pdo->exec("ROLLBACK TO SAVEPOINT before_batch_update"); } catch (Exception $e) { // 处理错误 }

14. 特殊数据类型处理

14.1 二进制数据存储

// 存储图片 $imageData = file_get_contents('photo.jpg'); $stmt = $pdo->prepare("INSERT INTO images (name, data) VALUES (?, ?)"); $stmt->execute(['profile.jpg', $imageData]); // 读取二进制 $stmt = $pdo->prepare("SELECT data FROM images WHERE id = ?"); $stmt->execute([$imageId]); $imageData = $stmt->fetchColumn(); header('Content-Type: image/jpeg'); echo $imageData;

14.2 地理空间数据

// 存储坐标点 $pdo->exec("INSERT INTO locations (name, point) VALUES ('Office', ST_GeomFromText('POINT(116.404 39.915)'))"); // 距离查询 $sql = "SELECT name, ST_Distance_Sphere(point, ST_GeomFromText('POINT(116.404 39.915)')) as distance FROM locations ORDER BY distance ASC LIMIT 10";