MySQL价格区间查询：从基础到进阶的完整指南

在电商、金融、库存管理等业务场景中，价格区间查询是数据库操作的高频需求。如何高效、准确地筛选出符合价格范围的数据，直接影响系统性能和用户体验。本文将从基础语法到高级优化，系统讲解MySQL中价格区间查询的实现方法。

一、基础价格区间查询语法

1. BETWEEN运算符的基本用法

BETWEEN是MySQL中最常用的价格区间查询运算符，其语法结构为：

SELECT * FROM products 
WHERE price BETWEEN lower_bound AND upper_bound;

示例：查询价格在100元至500元之间的商品

SELECT product_name, price 
FROM products 
WHERE price BETWEEN 100 AND 500;

BETWEEN的特点：

• 包含边界值（100和500都会被包含）
• 语法简洁直观
• 适用于闭区间查询

2. 比较运算符的替代方案

当需要更灵活的区间控制时，可以使用比较运算符组合：

SELECT * FROM products 
WHERE price >= 100 AND price <= 500;

这种写法与BETWEEN等效，但提供了更多控制可能性：

-- 查询价格大于100且小于500的商品（开区间）
SELECT * FROM products 
WHERE price > 100 AND price < 500;

二、性能优化策略

1. 索引设计与优化

价格字段的索引设计是区间查询性能的关键：

1. 单列索引：为price字段创建普通索引

   ALTER TABLE products ADD INDEX idx_price (price);

2. 复合索引：当查询条件包含其他字段时

   ALTER TABLE products ADD INDEX idx_category_price (category_id, price);

索引使用原则：

• 遵循最左前缀原则
• 避免在索引列上使用函数或计算
• 区间查询会导致索引使用效率下降（只能用到左边界）

2. 查询条件优化技巧

1. 窄化查询范围：先通过其他条件缩小数据集

   SELECT * FROM products 
   WHERE category_id = 5 
   AND price BETWEEN 100 AND 500;

2. 避免索引失效：以下写法会导致索引失效

   -- 错误示例：在price上使用函数
   SELECT * FROM products WHERE ROUND(price) BETWEEN 100 AND 500;
   -- 错误示例：隐式类型转换
   SELECT * FROM products WHERE price BETWEEN '100' AND '500';

3. 分页优化：对于大数据量的区间查询

   SELECT * FROM products 
   WHERE price BETWEEN 100 AND 500
   LIMIT 20 OFFSET 0;

三、高级查询场景

1. 多条件组合查询

实际业务中，价格区间通常与其他条件组合：

SELECT * FROM products 
WHERE (price BETWEEN 100 AND 500)
AND (stock_quantity > 0)
AND (discontinued = 0)
ORDER BY price ASC;

2. 动态价格区间统计

统计不同价格区间的商品数量：

SELECT 
  CASE 
    WHEN price < 100 THEN '0-100'
    WHEN price BETWEEN 100 AND 300 THEN '100-300'
    WHEN price BETWEEN 301 AND 500 THEN '301-500'
    ELSE '500+'
  END AS price_range,
  COUNT(*) AS product_count
FROM products
GROUP BY price_range;

3. 跨表关联查询

查询订单中价格区间对应的商品信息：

SELECT o.order_id, p.product_name, p.price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.price BETWEEN 200 AND 800
AND o.order_date > '2023-01-01';

四、实际应用案例

1. 电商系统价格筛选

电商平台的商品筛选功能通常需要处理多个价格区间：

-- 基础实现
SELECT product_id, product_name, price 
FROM products 
WHERE price BETWEEN ? AND ?
ORDER BY price ASC
LIMIT 20;
-- 优化版本（带缓存）
SELECT p.product_id, p.product_name, p.price 
FROM products p
JOIN product_categories pc ON p.category_id = pc.category_id
WHERE p.price BETWEEN 100 AND 500
AND pc.parent_id = 5  -- 特定分类
AND p.is_active = 1;

2. 金融系统风险评估

金融产品按风险等级和收益区间筛选：

SELECT product_name, risk_level, expected_return
FROM financial_products
WHERE expected_return BETWEEN 4.5 AND 6.0
AND risk_level IN ('R2', 'R3')
AND maturity_date > DATE_ADD(CURDATE(), INTERVAL 1 YEAR);

五、常见问题与解决方案

1. 浮点数比较问题

价格字段使用DECIMAL而非FLOAT/DOUBLE：

-- 错误示例：浮点数比较不精确
CREATE TABLE products (
  price FLOAT  -- 不推荐
);
-- 正确做法
CREATE TABLE products (
  price DECIMAL(10,2)  -- 推荐
);

2. 大数据量查询优化

对于百万级数据表：

1. 使用覆盖索引

   ALTER TABLE products ADD INDEX idx_price_only (price);
   -- 查询时只选择索引列
   SELECT product_id, price FROM products 
   WHERE price BETWEEN 100 AND 500;

2. 分区表策略（按价格范围分区）

   CREATE TABLE products (
     product_id INT,
     price DECIMAL(10,2),
     -- 其他字段
   ) PARTITION BY RANGE (price) (
     PARTITION p0 VALUES LESS THAN (100),
     PARTITION p1 VALUES LESS THAN (500),
     PARTITION p2 VALUES LESS THAN (1000),
     PARTITION pmax VALUES LESS THAN MAXVALUE
   );

3. 动态SQL生成

在应用层动态构建价格区间查询：

// Java示例
public List<Product> findByPriceRange(double min, double max) {
    String sql = "SELECT * FROM products WHERE price BETWEEN ? AND ?";
    // 使用PreparedStatement防止SQL注入
    // 执行查询...
}

六、最佳实践总结

1. 字段类型选择：价格字段优先使用DECIMAL(10,2)或DECIMAL(12,2)

2. 索引策略：

   • 单表查询：price字段单独索引
   • 多条件查询：创建复合索引(category_id, price)

3. 查询写法：

   • 优先使用BETWEEN处理闭区间
   • 复杂条件使用AND组合
   • 避免在索引列上使用函数

4. 性能监控：

   • 使用EXPLAIN分析查询执行计划
   • 监控慢查询日志
   • 定期分析表并优化

5. 业务适配：

   • 根据实际业务调整区间划分
   • 考虑是否需要包含边界值
   • 对于频繁查询的价格区间，可考虑物化视图

通过系统掌握这些技术和优化策略，开发者能够高效处理各种价格区间查询需求，在保证查询准确性的同时，显著提升系统性能和响应速度。