单表查询中算术运算符与比较运算符的实战指南

最开始接触数据库的时候我总觉得“单表数据记录查询”这节没什么好学的。不就一条SELECT嘛会写字段、会写WHERE就能交差。直到某次自己写报表一条简单的订单金额查询因为把算术运算符和比较运算符用混了返回的结果比我预想的整整多出三分之一业务对账怎么都对不上。那一次我才明白单表查询里算术运算符和比较运算符不是“小学算术”它们组合起来之后的优先级、空值处理、隐式转换才是真正决定查询结果对不对的分水岭。这篇就围绕单表数据记录查询里的算术运算符和比较运算符把原理、案例、还有我踩过的坑一次讲透。1. 先搞清楚单表查询的执行顺序运算符才能用对地方1.1 表面上是“查数据”实际是一条逻辑管道很多初学者拿到一条 SQL习惯从SELECT开始读。比如下面这条SELECT order_id, amount * discount AS final_amount FROM orders WHERE status paid AND amount 100 ORDER BY final_amount DESC;如果你问“先算什么”有人会说是amount * discount有人会说是status paid。其实 SQL 并不是按书写顺序执行的。数据库引擎拿到这条语句后先看的是FROM确定从哪张表取数然后看WHERE把满足条件的行筛出来接着才轮到SELECT里的算术计算算出final_amount最后是ORDER BY排序。这个顺序理解错了后面好多坑都解释不了。最典型的就是“别名不能用”。上面这条 SQL 里ORDER BY final_amount能用别名是因为排序发生在SELECT之后但如果你改成WHERE final_amount 100数据库一定会报错因为WHERE执行的时候final_amount这个别名根本还不存在。理解了执行顺序你就不用死记“别名不能用在 WHERE”而是自然明白先有筛选后有计算列生成。1.2 我给新手抓 SQL 执行顺序的土办法我自己带人的时候会让他们把一条单表查询拆成五步按这个顺序默念从哪张表取数FROM先过滤哪些行WHERE对剩下的行做分组过滤GROUP BY / HAVING如果没有就不管生成结果列、做运算SELECT排序、限制行数ORDER BY / LIMIT。这里特别要强调第二步和第四步的分工比较运算符几乎都出现在WHERE阶段它决定“哪些行留下”算术运算符多数出现在SELECT阶段它决定“每行显示出来的值是什么”。两个阶段混在一起就会把“行的筛选”和“值的计算”搅成一锅粥最后连结果都不太敢相信。2. 算术运算符让查询结果直接在数据库里完成计算2.1 五类算术运算符的用法以及容易被忽略的“副作用”单表查询里的算术运算符其实就是加减乘除和取余、-、*、/、%。我在实际业务里见得最多的是*和/比如单价乘数量算金额、总价除以件数算均价。举一个典型的“商品订单明细”场景SELECT product_id, price, quantity, price * quantity AS total_price, price * discount AS discounted_price FROM order_items;这条查询很直白price * quantity算出小计price * discount算出折扣后的价格。注意我用了别名AS这是写计算列的标配否则结果集的表头就是一个看不懂的“price * quantity”。关于除法不同数据库表现得不太一样这点必须说清楚。在 MySQL 里10 / 4结果是2.5000因为 MySQL 的/默认做浮点除法哪怕两个数都是整数。但是网上很多教程里教的 SQL Server、SQLite 之类如果两个操作数都是整数10 / 4可能直接是2小数部分被截断。这个差异非常坑。如果你写了一个total_amount / quantity结果却发现均价全是整数、分钱全没了先别急着怀疑代码先确认你们数据库对两个整数的除法到底怎么处理。稳妥的做法是显式让其中一个数带小数比如total_amount / (quantity * 1.0)或者用CAST转成小数类型。取余%的应用场景比想象中多。比如你要把订单按用户尾号分组user_id % 10就能拿到尾号要按天做分表逻辑判断也常拿日期相关的数值取余。它看起来简单但同样注意如果某个订单数量是 0price % quantity这种写法会直接报除以零错误。2.2 算术运算遇到 NULL结果直接“一票否决”这是算术运算符里最大的坑没有之一。我用最简单的话说任何算术运算只要有一个操作数是 NULL结果一定是 NULL。SELECT price, discount, price * discount AS paid_price FROM order_items;如果某条记录的discount字段是 NULL意思是打折信息还没维护那么paid_price不会是 price 本身而是 NULL。很多新人第一次看到结果时会懵为什么计算列全是空我再强调一遍NULL 不是一个数字它表示“未知值”。未知乘任何数结果仍然是未知。我在实际项目里被这个问题坑过至少三次。最典型的一次是给客户写报表应付金额 订单金额 - 退款金额只要有一部分订单没有退款记录、退款字段不是 0 而是 NULL最终结果就整片变成 NULL汇总金额直接归零。后来我们统一了规则业务上“没有退款”应该记为 0如果历史数据里已经出现 NULL查询时就用IFNULL兜底SELECT order_amount, IFNULL(refund_amount, 0) AS refund_amount, order_amount - IFNULL(refund_amount, 0) AS real_amount FROM orders;2.3 把计算结果变成业务可用的字段别名与格式化算术算出来了不算完实际交付报表时结果列的名字和格式往往比计算本身更花时间。我会建议把计算列统一加别名并用数据库自带的格式化函数做一轮处理。比如金额要保留两位小数MySQL 里可以这样SELECT product_id, ROUND(price * quantity, 2) AS total_price FROM order_items;这一步看起来可有可无但如果你把结果接到前端页面或者导出给财务字段名是total_price还是ROUND(price * quantity, 2)完全两回事。命名清晰的计算列能让看 SQL 的人少猜很多。3. 比较运算符WHERE 筛选中真正分胜负的地方3.1 等值、不等值与范围判断的用法细节比较运算符的经典阵容是、!或、、、、再加上BETWEEN AND、IN、LIKE这类扩展。单表查询的过滤条件里比较运算符是第一主力。先说等值判断。WHERE status paid没毛病但有两个细节容易犯错第一字符串要用单引号包起来第二和!不会匹配 NULL。如果你写WHERE refund_status ! done那些 refund_status 为 NULL 的订单不会被查出来。这在业务上可能就是“漏单”大事故。后文我会在 NULL 专项里展开。范围判断里BETWEEN AND是个好东西但它包含两端的边界值。比如WHERE amount BETWEEN 100 AND 200会把 amount 恰好等于 100 和恰好等于 200 的记录都算进去。如果你的业务说的是“金额在 100 到 200 之间不包含边界”就不能用 BETWEEN得写成amount 100 AND amount 200。还有一个常见错误是把上下限写反比如BETWEEN 200 AND 100这种写法在不同数据库里表现不确定有的是空结果有的会报错保险起见永远从小到大写。IN列表适合离散值比如查几个指定状态SELECT * FROM orders WHERE status IN (paid, shipped, completed);它的等价写法是一串 OR但IN更简洁。同样需要注意IN ()空列表、列表里带 NULL 的情况都会让结果变得微妙我也会在排查部分一起说。3.2 比较运算符与 NULL 的关系永远别用等号判断空值如果有一条 SQL 要选出一个“还没有设置退款金额”的订单直觉写法是SELECT * FROM orders WHERE refund_amount NULL;但结果一条都查不出来。原因还是那个NULL 不是值是“未知”。未知和未知之间不能用等号比较因为“未知 未知”在逻辑上仍然无法断定真假。数据库里判断空值只能用IS NULL或者IS NOT NULLSELECT * FROM orders WHERE refund_amount IS NULL;这条才能正常查出来。与之对应refund_amount IS NOT NULL表示有退款金额的记录。我见过太多同事在 NULL 判断上翻车这里我必须说得再重一些只要你的表里字段允许为空!、、这类比较全都不会命中 NULL 记录该用IS NULL就得用。3.3 字符串字段与数字比较的隐式转换以及索引失效问题比较运算符还有个隐性话题类型不一致的时候数据库会自动做隐式转换。表面上看挺方便但是坑很深。举个例子某张表的phone字段是varchar类型存的是手机号。你写SELECT * FROM users WHERE phone 13512345678;数据库通常会把这个数字转成字符串再比较结果一般没问题。但如果表里有一些“脏数据”比如手机号存成了135-1234-5678或013512345678隐式转换的规则就可能让你要么匹配不到要么匹配到一堆不想要的行。更麻烦的是如果字段是字符串类型你拿数字去和它比较很多数据库无法正常使用该字段上的索引。全表扫描一触发查询慢得肉眼可见。反过来如果字段是数字类型你拿字符串去比较同样可能造成索引失效。我的建议非常朴素写 WHERE 条件时永远保证左边字段的类型和右边常量的类型一致。如果需要转换就显式写CAST别把主动权交给数据库的“好心”隐式转换。还有一种更隐蔽的索引失效在比较时对字段本身做算术运算。比如SELECT * FROM products WHERE price * 0.8 100;这个条件逻辑上等价于“价格大于 125”但数据库很难用上price字段的索引因为字段被包了一层乘法。正确的写法是先把计算挪到常量这一侧SELECT * FROM products WHERE price 100 / 0.8;这样数据库会先算出右边的值然后直接拿price和常量比较索引基本不会丢。这个习惯可以说是我给所有写 SQL 的人强调次数最多的一个能把计算放在常量侧就绝不要放在字段侧。4. 运算符优先级看似简单却最容易翻车的组合题4.1 优先级顺序以及一个终身受益的写法算术运算符和比较运算符一旦同时出现在一个查询里就避不开优先级问题。教材里通常会给一张长长的表告诉你乘除优先于加减比较优先于逻辑运算等等。但说句实话这张表我从来没背全过因为我的书写习惯帮我规避了所有优先级问题。我的习惯只有一个括号能加就加不要相信自己的短期记忆也不要相信后来维护代码的人能记住优先级。比如下面这条逻辑SELECT price * quantity shipping_fee AS total FROM orders;price * quantity和shipping_fee相加因为乘法优先结果正好是“小计加运费”。但如果某天来了个新人想表达“数量加运费再乘单价”却没写括号写成了price * quantity shipping_fee那就已经完全走样了。你很难说清是他人出了问题还是代码误导了他。所以我宁可写成SELECT (price * quantity) shipping_fee AS total FROM orders;一眼看过去逻辑清清楚楚多写的两个括号换来的代价几乎为零。4.2 AND 与 OR 混时的经典坑为什么结果比预期多单表查询里最经典的优先级坑发生在 AND 和 OR 混用的 WHERE 条件里。比如我想查“状态是已完成或者退款金额大于 100 且状态是处理中”的订单但条件宽松一点写成这样SELECT * FROM orders WHERE status completed OR refund_amount 100 AND status processing;多数数据库里AND优先于OR所以这条 SQL 实际解读为所有状态为 completed 的订单加上那些退款金额大于 100 且状态为 processing 的订单。这正好是我想要的。但是如果我不小心写反了预期实际想要的是“状态为 completed 或者 refund_amount 100并且 status processing”那就必须加括号SELECT * FROM orders WHERE (status completed OR refund_amount 100) AND status processing;两种写法差一层括号结果完全两个世界。我在代码评审里见过不少 OR 和 AND 混在一起还不加括号的 SQL阅读成本极高。每次我都会建议凡是超过三个条件的 WHERE先把条件按业务含义分组建括号每组括号之间再用 AND 连接。4.3 算术与比较混写时的“常量放左边”习惯算术运算符和比较运算符常常一起出现比如“会员价大于等于 100 的订单”SELECT * FROM orders WHERE price * 0.9 100;这条同样犯了我前面说的毛病——对字段做了运算。改成等价写法SELECT * FROM orders WHERE price 100 / 0.9;100 除以 0.9 先算出来然后price直接和约等于 111.11 比较索引保持可用。这就是“常量放左边、计算挪到常量侧”的典型应用。还有个小细节字段放左边时price 111.11这种表达可读性比price * 0.9 100更好因为看 SQL 的人第一眼就能看到字段和阈值而不是先心算一道折扣。5. 一个完整案例从零写一条带运算和筛选的查询5.1 业务场景查询有效订单的应收金额理论讲了不少我们来做一个完整的模拟场景。假设某公司的订单表orders里有以下字段order_id订单编号user_id用户编号product_count商品总件数unit_price单价discount_rate折扣率0.9 表示九折NULL 表示没有折扣status订单状态取值范围包括paid、pending、cancelledorder_time下单时间。现在业务方要一份“已支付订单的应收金额清单”要求只查paid状态的订单应收金额 件数 × 单价 × 折扣率没有折扣率的直接按原价按应收金额从高到低排序。5.2 逐步拆解过滤、计算、兜底、排序第一步先写过滤条件。这是比较运算符的主场WHERE status paid第二步处理计算列。因为有 NULL 折扣率的风险必须用IFNULL兜底product_count * unit_price * IFNULL(discount_rate, 1) AS receivable_amount第三步加上排序并且顺手给结果加一个行号方便对账。完整的 SQLSELECT order_id, user_id, product_count, unit_price, product_count * unit_price * IFNULL(discount_rate, 1) AS receivable_amount, order_time FROM orders WHERE status paid ORDER BY receivable_amount DESC;这条 SQL 同时用到了比较运算符status paid和算术运算符乘法还用IFNULL处理了 NULL。如果业务还要限制“只查应收金额大于 200 的订单”这时候不能直接写WHERE receivable_amount 200因为别名在 WHERE 阶段还不存在。正确做法是把计算条件完整写到 WHERE 里或者用子查询包一层我一般直接在 WHERE 里重复一遍计算WHERE status paid AND product_count * unit_price * IFNULL(discount_rate, 1) 200条件重复看起来有点丑但单表查询里这是最直接、也最容易让新手理解的做法。5.3 什么时候在 SQL 里算术什么时候拿到程序里算这里想多说一句选型思考。有人觉得“所有计算都应该在 SQL 里完成”也有人认为“SQL 只负责取原始数据计算全放程序里”。以我的实践经验看要在两者之间找平衡点。适合在 SQL 里算的计算逻辑简单、与行内数据强相关、汇总聚合类需求。比如订单金额、订单数量、SUM 汇总这些天然和数据库的行、列结构绑定在 SQL 里一条语句搞定传输的数据量还小。适合拿到程序里算的计算逻辑复杂、需要一系列业务判断分支、涉及不同表多次取数的场景。比如要根据订单金额计算阶梯佣金金额在 1000 以下提 5%1000 到 5000 提 8%这个逻辑写 SQL 也能写但可读性和维护性远不如程序里写个函数。我自己的判断标准是一行 SQL 能写完就在 SQL 里算写超过三行就开始提醒自己要拆解。6. 查询结果不对时的排查链路按什么顺序找问题6.1 结果多了一条先查 NULL 和 OR结果数量比预期多最常见的两个原因一是!或NOT IN条件把 NULL 记录留了下来或者排除掉了不该排除的看你怎么预期二是 OR 和 AND 优先级写错。排查步骤我建议这样来先把查询条件复制出来一条条单独执行。比如把status paid OR refund_amount 100 AND status processing拆成两条分别数行数再看组合后的行数是否等于两条记录集之和。只要行数不吻合优先级八成有问题。再查NOT IN的坑。如果你的条件是WHERE status NOT IN (cancelled)而某条订单的 status 是 NULL那么这条记录不会出现在结果里因为你拿 NULL 去和列表比较结果不是 TRUE 而是“未知”最终被判为不满足条件。这是一个非常隐蔽的“丢数据”点很多人对IN有直觉对NOT IN反而容易失手。6.2 结果全是 NULL先确认计算列里的脏数据计算列整片是 NULL几乎一定是某个参与运算的字段里带着 NULL。排查办法是把计算列拆开查先分别看两个原始字段SELECT product_count, unit_price, product_count * unit_price AS amount FROM orders;如果product_count或unit_price有 NULLamount就会是 NULL。确定之后再用IFNULL或COALESCE把业务默认值补上。这里要注意不是所有 NULL 都应该被补成 0。比如“没有折扣”补成 1 是合理的但“没有退款”补成 0 得先和业务确认别自己拍脑袋。6.3 结果变慢看一下运算符是否拖垮索引查询突然变慢又没动过表结构八成是条件里的字段被“包装”了。常见包装方式包括对字段做算术运算price * 2 100、对字段做函数DATE(create_time) 2024-01-01、隐式类型转换字符串字段比较数字。排查思路很简单把 WHERE 条件里每个字段的写法过一遍凡是字段本身被包了函数或者运算的都尝试改写成“字段直接和常量比较”。比如DATE(create_time) 2024-01-01可以改写成create_time 2024-01-01 AND create_time 2024-01-02虽然条件变长但索引能用了查询速度立刻不同。6.4 快速自查清单写查询前过一遍我把自己这些年经常犯的错做成了一个小清单写 SQL 之前过一遍能省大量排查时间检查项正确做法用 NULL判断空值改用IS NULL计算列出现 NULL用IFNULL兜底并确认默认值对不对AND 与 OR 混用加括号拆分组不心算优先级字段参与运算把计算挪到常量侧如price 100 / 0.8字符串字段比较数字统一字段和常量的类型显式转换需要计算后再筛选不能在 WHERE 用 SELECT 别名重复条件或用子查询金额小数被截断显式转成小数类型再除7. 最后聊一点个人体会我个人写单表查询的体会总结起来就一句把运算符当成表达思想的标点符号而不是单纯的计算符号。写之前先在纸上用自然语言把业务逻辑翻一遍比如“已支付的、折扣后超过 200 的、按金额倒序”再翻译成 SQL你会发现每个运算符放哪儿、要不要加括号全都顺理成章。反过来看到一条复杂查询先别急着运行先在脑内模拟一遍执行顺序重点是分清哪个运算符管“行”的筛选哪个运算符管“列”的计算。这个习惯我坚持了十多年帮我减少的返工量远比记住任何一张优先级表多。希望这篇关于单表数据记录查询、算术运算符和比较运算符的内容能帮你在写查询时就避开我当年踩过的坑。