SQL阶梯费率表to_purchase无上限区间取值设计最佳实践

现有背景 #

当前有两套按月度采购量划分的阶梯定价规则：

Source A 定价 #

Source B 定价 #

初始计划搭建的费率表结构如下，最高档位的to_purchase字段暂时用'and above'占位：

后续将直接使用BETWEEN语法关联费率表与交易表，关联逻辑如下：

INNER JOIN fees f ON f.Source_name = t.Source_name AND t.transaction_count BETWEEN f.from_purchase AND f.to_purchase;

设计建议 #

• 首先要规避基础设计错误：to_purchase是存储采购量数值的字段，必须定义为整数/数值类型，绝对不能存入'and above'这类字符串值。字符串和数值做比较时会触发隐式类型转换，直接导致BETWEEN判断失效，还会造成字段类型混乱的脏数据问题。
• 如果你不想修改已经写好的BETWEEN关联逻辑，最优方案是给无上限区间填入对应数值类型的理论最大值作为哨兵值：
  • 若to_purchase定义为INT类型，填入2147483647（INT类型上限，对应21亿以上的采购量，常规采购场景都不可能触达这个数值）
  • 若to_purchase定义为BIGINT类型，填入9223372036854775807即可
    这种写法完全兼容现有关联逻辑，所有大于等于11的交易数都会正常命中最高档位费率，不需要额外改代码。
• 如果你追求语义上的绝对严谨，也可以把to_purchase设为可空字段，无上限区间填入NULL，但注意**BETWEEN无法直接兼容NULL值**——SQL中任何值和NULL做比较的结果都是UNKNOWN，会直接导致关联匹配失败，必须把关联逻辑改成显式区间判断：

  INNER JOIN fees f 
  ON f.Source_name = t.Source_name 
  AND t.transaction_count >= f.from_purchase 
  AND (t.transaction_count <= f.to_purchase OR f.to_purchase IS NULL);
  

  这种方案可以从字段值上明确区分“有明确上限”和“无上限”两类区间，不存在哨兵值被极端业务数据触达的隐患，但需要调整现有查询逻辑，可根据业务实际情况选择。

内容的提问来源于stack exchange，提问作者dan origami