深入解析MySQL逻辑函数与实战应用

一、逻辑函数基础与分类

逻辑函数是构建复杂查询的基石，常见类型包括：

1. 条件判断函数：IF(), CASE
2. 逻辑运算符：AND/OR/NOT
3. 三值逻辑处理：NULL处理函数
4. 类型转换函数：CAST()

-- IF函数示例
SELECT 
    name,
    IF(score >= 60, '合格', '补考') AS result
FROM students;

二、条件判断函数深度解析

2.1 CASE表达式

SELECT 
    employee_id,
    CASE 
        WHEN sales > 10000 THEN '金牌'
        WHEN sales > 5000 THEN '银牌'
        ELSE '普通'
    END AS level
FROM sales_data;

2.2 IF函数嵌套

SELECT 
    product_id,
    IF(stock > 0, '有货', IF(reserved > 0, '预售', '无货')) AS stock_status
FROM products;

三、NULL值处理技巧

3.1 COALESCE应用

SELECT 
    order_id,
    COALESCE(coupon_amount, 0) AS discount
FROM orders;

3.2 NULLIF应用场景

SELECT 
    employee_id,
    NULLIF(department, 'N/A') AS department
FROM employees;

四、逻辑运算符优化技巧

1. 短路计算优化

-- 优先处理高选择条件
WHERE (status = 'active' AND premium = 1) 
   OR (create_time > '2023-01-01')

2. 索引利用原则

-- 创建复合索引优化
CREATE INDEX idx_status_type ON orders (order_status, order_type);

五、窗口函数中的逻辑处理

SELECT 
    employee_id,
    sales,
    RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS rank
FROM sales_data
WHERE sales > 10000;

六、性能优化实践

1. 避免全表扫描

-- 优化前（全表扫描）
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 优化后（使用范围查询）
SELECT * FROM users 
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

2. 索引优化示例

-- 创建函数索引
CREATE INDEX idx_name ON users(UPPER(last_name));

七、复杂查询示例

SELECT
    department,
    COUNT(CASE WHEN score >= 60 THEN 1 END) AS pass_count,
    AVG(CASE WHEN gender = 'M' THEN score END) AS male_avg
FROM students
GROUP BY department
HAVING COUNT(*) > 10;

八、常见问题解决方案

1. NULL处理

-- 使用COALESCE设置默认值
SELECT 
    product_name,
    COALESCE(stock, 0) AS stock_qty 
FROM products;

2. 三值逻辑处理

SELECT 
    user_id,
    CASE 
        WHEN deleted = 1 THEN '已删除'
        WHEN active = 0 THEN '禁用'
        ELSE '正常'
    END AS status
FROM users;

九、性能对比测试

-- 使用EXPLAIN分析执行计划
EXPLAIN 
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND status IN ('completed', 'shipped');

十、最佳实践总结

1. 优先使用简单条件
2. 避免在WHERE子句使用函数
3. 合理使用组合索引
4. 定期分析查询执行计划