SQL查询语句
SELECT
id,
name,
price,
stock,
sales,
category_id
FROM products
WHERE
status = 'active'
AND category_id = :category_id
AND price BETWEEN :min_price AND :max_price
AND name LIKE CONCAT('%', :kw, '%')
AND stock > 0
AND deleted_at IS NULL
ORDER BY
sales DESC,
price ASC,
id DESC
LIMIT 20;
语句说明
-
查询目的：
- 在指定分类下检索在线且未被软删除的在售商品，并按销量优先、价格次之进行排序，若价格相同则以 id 倒序确保稳定排序，最多返回 20 条。
-
字段解释：
- id：商品主键，用于唯一标识并作为稳定排序的最后断点。
- name：商品名称，用于关键词模糊匹配与结果展示。
- price：商品价格，用于价格区间过滤与次级排序。
- stock：库存数量，筛除无库存商品（>0）。
- sales：销量指标，作为主要排序依据（降序）。
- category_id：品类标识，限定在指定分类下检索。
-
条件分析：
- status = 'active'：只取已上架/有效商品。
- category_id = :category_id：限定在指定分类。
- price BETWEEN :min_price AND :max_price：限定价格区间（包含边界）。
- name LIKE CONCAT('%', :kw, '%')：名称模糊匹配，匹配包含关键词的商品名。
- stock > 0：仅返回有库存的商品。
- deleted_at IS NULL：软删除保护，排除已删除记录。
- ORDER BY sales DESC, price ASC, id DESC：销量高的优先；同销量时价格更低的优先；同价时使用 id DESC 稳定排序。
- LIMIT 20：限制返回记录数，控制接口负载与响应时间。
使用建议
<?php
declare(strict_types=1);
$dsn = 'mysql:host=127.0.0.1;dbname=your_db;charset=utf8mb4';
$username = 'your_user';
$password = 'your_pass';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 使用真正的预处理，防止注入与类型歧义
];
$pdo = new PDO($dsn, $username, $password, $options);
// 输入获取与校验（示例，可按实际来源调整）
$categoryId = filter_input(INPUT_GET, 'category_id', FILTER_VALIDATE_INT);
$minPriceIn = $_GET['min_price'] ?? null;
$maxPriceIn = $_GET['max_price'] ?? null;
$kwIn = $_GET['kw'] ?? '';
// 基本校验与清洗
if ($categoryId === false || $categoryId <= 0) {
http_response_code(400);
exit('Invalid category_id');
}
// 对于 DECIMAL(10,2) 等价格字段，使用字符串绑定避免浮点误差
$minPrice = is_numeric($minPriceIn) ? (string)$minPriceIn : '0';
$maxPrice = is_numeric($maxPriceIn) ? (string)$maxPriceIn : $minPrice;
// 保证 min <= max
if (bccomp($minPrice, $maxPrice, 8) === 1) {
[$minPrice, $maxPrice] = [$maxPrice, $minPrice];
}
// 关键词清洗与限长，避免超长输入影响性能
$kw = trim((string)$kwIn);
if ($kw === '') {
// 允许空关键词时，仍可使用 LIKE '%%'，如需强制关键词可在此报错
}
$kw = mb_substr($kw, 0, 64);
// 预处理查询
$sql = <<<SQL
SELECT
id,
name,
price,
stock,
sales,
category_id
FROM products
WHERE
status = 'active'
AND category_id = :category_id
AND price BETWEEN :min_price AND :max_price
AND name LIKE CONCAT('%', :kw, '%')
AND stock > 0
AND deleted_at IS NULL
ORDER BY
sales DESC,
price ASC,
id DESC
LIMIT 20
SQL;
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':category_id', $categoryId, PDO::PARAM_INT);
// 对 DECIMAL 使用字符串绑定，避免浮点精度问题
$stmt->bindValue(':min_price', $minPrice, PDO::PARAM_STR);
$stmt->bindValue(':max_price', $maxPrice, PDO::PARAM_STR);
$stmt->bindValue(':kw', $kw, PDO::PARAM_STR);
$stmt->execute();
$products = $stmt->fetchAll();
header('Content-Type: application/json; charset=utf-8');
echo json_encode([
'data' => $products,
'count' => count($products),
], JSON_UNESCAPED_UNICODE);
-
安全注意事项
- 始终使用预处理与命名参数绑定（已示例），不要将变量直接拼接到 SQL 中。
- 验证与规范化所有输入：category_id 必须为正整数；价格应为数值且满足 min <= max；关键词限长并去除前后空格。
- 确保数据库与连接使用 utf8mb4 字符集，避免字符集不一致带来的匹配异常与安全隐患。
- 不要将用户可控内容拼接进入 ORDER BY 或 LIMIT；本例 LIMIT 为常量 20，排序字段为固定列表。
- 生产环境隐藏详细 SQL 错误信息，对外统一为通用错误消息。
-
性能优化提示
- 索引建议（根据实际数据分布与版本微调）：
- 过滤组合索引：CREATE INDEX idx_products_filter ON products (status, category_id, deleted_at, stock, price);
- 等值条件置前（status, category_id, deleted_at），提升过滤效率；
- stock、price 为范围条件，放在后面可配合 Index Condition Pushdown（MySQL 5.6+）减少回表。
- 单列/小组合索引：确保 category_id、status、deleted_at 至少有选择性较好的索引。
- LIKE 前置通配符会导致无法使用普通前缀索引，若关键词查询是高频场景：
- 优先考虑 FULLTEXT(name) 并改为 MATCH(name) AGAINST (... IN BOOLEAN MODE)；
- 或采用搜索引擎（如 Elasticsearch）承载复杂检索。
- 排序优化：当前排序字段与过滤字段混合，且含范围条件，通常会触发 filesort 属正常现象。确保 sort_buffer_size 合理、LIMIT 小（本例 20）即可。
- 使用 EXPLAIN 检查执行计划，关注 type、rows、filtered、Extra（Using index condition / Using filesort）。
- 如需分页，优先使用基于上次游标的“keyset pagination”（如基于 sales/price/id 的断点条件）代替 OFFSET，可避免深分页性能劣化。
注意事项
-
数据库连接配置要求
- 使用 utf8mb4 字符集与与之匹配的 _ci（不区分大小写）排序规则，确保 LIKE 模糊匹配符合预期。
- 建议启用严格 SQL 模式（如 STRICT_TRANS_TABLES）防止静默截断或隐式类型转换。
-
参数绑定的重要性
- 价格建议按字符串绑定以匹配 DECIMAL 精度；整型字段使用 PDO::PARAM_INT。
- 严格校验与规范化输入范围，避免逻辑异常（例如 max_price 小于 min_price）。
-
错误处理建议
- 使用 PDO 异常模式（ERRMODE_EXCEPTION）并在外层捕获 PDOException，记录日志但不向客户端泄露内部细节。
- 对外统一返回稳定的错误结构体（如 JSON），便于前端处理。
- 可对查询设置合理的超时（如 PDO::ATTR_TIMEOUT）并在慢查询日志中监控与优化。
