GaussDB SQL基础语法示例：BOOLEAN表达式深度解析-CSDN博客

本文链接：https://blog.csdn.net/m0_60569209/article/details/148334926

GaussDB SQL基础语法示例：BOOLEAN表达式深度解析

一、BOOLEAN表达式核心价值

1.1 逻辑控制中枢
数据筛选：精准过滤目标数据（如筛选VIP用户）
条件分支：实现动态业务规则（如订单状态机）
流程控制：管理事务执行路径（如库存扣减校验）
1.2 典型应用场景
场景实现方式性能特征
数据过滤 WHERE子句中的逻辑组合毫秒级响应
业务规则校验 CHECK约束中的条件表达式事务级原子性
动态计算 CASE表达式中的条件分支百万级处理能力

二、语法全解析

基础运算符

-- 比较运算符
SELECT * FROM users 
WHERE age > 18 AND status = 'active';

-- 逻辑运算符
SELECT * FROM orders 
WHERE (amount > 1000 OR is_vip) 
  AND pay_status = 'completed';

-- NULL处理
SELECT * FROM products 
WHERE price IS NOT NULL 
  AND category IN ('Electronics', 'Books');

高级表达式

-- BETWEEN范围判断
SELECT * FROM logs 
WHERE log_time BETWEEN '2023-01-01' AND '2023-12-31';

-- IN集合判断
SELECT * FROM employees 
WHERE department_id IN (1,3,5)
  AND hire_date > '2020-01-01';

-- EXISTS子查询
SELECT * FROM customers c 
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.customer_id = c.id 
    AND o.amount > 1000
);

三、实战应用示例

数据过滤增强

-- 组合条件查询（含NULL处理）
SELECT user_id, 
       CASE 
         WHEN age < 18 THEN '未成年'
         WHEN age BETWEEN 18 AND 60 THEN '成人'
         ELSE '长者'
       END AS age_group
FROM users
WHERE (gender = 'F' OR gender = 'M')
  AND email LIKE '%@%.com'
  AND create_time > NOW() - INTERVAL '1 year';

-- 复杂业务规则
SELECT product_id,
       CASE 
         WHEN stock > 100 AND is_featured THEN '热销品'
         WHEN stock < 10 THEN '缺货预警'
         ELSE '常规商品'
       END AS status
FROM inventory;

事务控制应用

-- 库存扣减校验
BEGIN;
UPDATE products 
SET stock = stock - 1 
WHERE product_id = 1001 
  AND stock >= 1; -- 隐含条件校验

IF NOT FOUND THEN
  RAISE EXCEPTION '库存不足';
END IF;
COMMIT;

-- 权限校验
CREATE FUNCTION check_permission()
RETURNS TRIGGER AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM user_roles 
    WHERE user_id = NEW.user_id 
      AND role = 'admin'
  ) THEN
    RAISE EXCEPTION '权限不足';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

四、高级技巧

短路逻辑优化

-- 优先级调整（AND优先于OR）
SELECT * FROM products 
WHERE category = 'Electronics' 
  OR (category = 'Books' AND price < 100);

-- 括号强制优先级
SELECT * FROM products 
WHERE (category = 'Electronics' OR category = 'Books') 
  AND price > 50;

布尔表达式树

-- 多层嵌套逻辑
SELECT * FROM orders 
WHERE ( (status = 'shipped' AND ship_date < NOW()) 
      OR (status = 'processing' AND priority = 'high') )
  AND payment_status = 'confirmed';

与窗口函数结合

-- 排名过滤
SELECT * FROM (
  SELECT 
    user_id,
    RANK() OVER (ORDER BY total_spent DESC) AS rank
  FROM customer_stats
) t
WHERE rank <= 100;

-- 窗口条件判断
SELECT 
  order_id,
  CASE 
    WHEN SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) 
         > 1000 THEN '高价值客户'
    ELSE '普通客户'
  END AS customer_type
FROM orders;

五、性能优化指南

执行计划对比

-- 创建测试表
CREATE TABLE test_bool (
  id SERIAL PRIMARY KEY,
  a INT,
  b VARCHAR,
  c BOOLEAN
);

-- 插入测试数据
INSERT INTO test_bool (a, b, c)
SELECT floor(random()*100), 
       CASE WHEN random() < 0.3 THEN 'A' 
            WHEN random() < 0.6 THEN 'B' 
            ELSE 'C' END,
       random() < 0.5
FROM generate_series(1, 1000000);

-- 分析带布尔条件的查询
EXPLAIN ANALYZE
SELECT * FROM test_bool 
WHERE a > 50 AND b = 'A' AND c = true;

-- 对比索引效果
CREATE INDEX idx_test_bool ON test_bool(a, b, c);
EXPLAIN ANALYZE
SELECT * FROM test_bool 
WHERE a > 50 AND b = 'A' AND c = true;

优化策略
优化方向具体措施效果提升
索引优化建立覆盖索引（INCLUDE子句）查询速度提升5-10倍
条件顺序调整高选择性条件前置减少扫描行数
避免函数嵌套预计算布尔表达式结果 CPU消耗降低40%

六、避坑指南

优先级陷阱

-- 错误示例：AND/OR优先级混淆
SELECT * FROM users 
WHERE gender = 'F' OR gender = 'M' 
  AND age > 18;

-- 正确做法：显式括号控制
SELECT * FROM users 
WHERE (gender = 'F' OR gender = 'M') 
  AND age > 18;

NULL处理陷阱

-- 错误示例：隐式NULL转换
SELECT * FROM products 
WHERE price > 100 OR description = NULL;

-- 正确做法：显式NULL判断
SELECT * FROM products 
WHERE price > 100 OR description IS NULL;

七、最佳实践建议

开发规范
命名约定：cb_条件描述（如cb_vip_status）
注释标准：复杂逻辑添加SQL注释
版本控制：记录布尔表达式变更历史
性能基准
布尔类型单次执行耗时内存消耗并发能力
简单条件 <1ms 低高
复合条件 1-5ms 中中
嵌套子查询 5-20ms 高低
监控体系

-- 创建布尔表达式监控视图
CREATE VIEW bool_monitor AS
SELECT 
  query,
  total_time,
  rows,
  CASE 
    WHEN total_time > 1000 THEN '高危' 
    WHEN total_time > 500 THEN '警告' 
    ELSE '正常' 
  END AS risk_level
FROM pg_stat_statements
WHERE query LIKE '%AND%' 
   OR query LIKE '%OR%';

-- 自动报警规则
CREATE OR REPLACE FUNCTION bool_alert()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT count(*) FROM bool_monitor WHERE risk_level = '高危') > 5 THEN
    PERFORM pg_notify('bool_alert', '发现高危布尔查询!');
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;