寫SQL的人沒有一天能繞開數值處理。上一篇文章把字符串函數過了一遍這篇輪到數值函數。你別覺得數值函數就是加減乘除加個四舍五入真到用的時候round、truncate、ceil、floor這幾個函數到底誰是誰金額對不上賬、評分統(tǒng)計偏了0.1、隨機抽樣抽出一堆重復數據這些問題十有八九出在數值函數的細節(jié)上。這篇把MySQL里常見的數值函數拆開講清楚重點說清楚每個函數背后的計算邏輯以及實際寫SQL時怎么選、怎么避坑。適合剛接觸MySQL的開發(fā)者也適合寫了好幾年SQL但沒認真摳過數值邊界的朋友看完能直接照著手里的業(yè)務場景用。1. 數值函數到底在解決什么問題1.1 先看兩個實際場景你就知道數值函數有多重要第一個場景是訂單金額處理。某電商項目的訂單表里每筆訂單有商品單價和數量需要計算實付金額并保留兩位小數。很多開發(fā)者第一反應用round(price * quantity, 2)但跑批后發(fā)現(xiàn)有筆訂單折扣價是9.35數量是3算出來是28.05沒錯可另一筆訂單單價5.10數量是0.47乘出來是2.397round之后變成2.40。問題來了財務要求按分結算2.397到底該進到2.40還是直接截斷成2.39不同業(yè)務規(guī)則需要不同函數這個選擇直接影響真金白銀的差異。第二個場景是評分統(tǒng)計。某學習類App要給課程評分算均值比如課程A的評分明細是4.5、4.5、4.8、4.8、4.9平均值算出來之后要展示成一位小數。有人直接round(avg(score), 1)有人用cast(avg(score) as decimal(10,1))還有人用format(avg(score), 1)?？雌饋斫Y果都是4.7但底層返回類型完全不一樣。界面上的展示、后續(xù)的排序、再參與計算的精度都會因為選錯函數而出問題。這兩個場景很典型數值函數不只是“把數變好看”它決定了對數據的舍入策略、精度控制、參與后續(xù)計算時的數據類型。搞清楚數值函數本質上是在搞清楚業(yè)務希望你怎么對待一個帶小數位的數字。1.2 數值函數在MySQL里的分類與定位MySQL里的數值函數大致分成四類舍入與截斷類round、truncate、ceil、ceiling、floor基礎數學運算類abs、sign、mod、pow、power、sqrt、exp、ln、log、log2、log10隨機數類rand三角函數與進制轉換類sin、cos、tan、asin、acos、atan、atan2、cot、degrees、radians以及bin、oct、hex、conv這個系列我不展開講聚合函數里的sum、avg、max、min那些是聚合場景跟標量函數的使用邏輯不太一樣。這篇聊的是一行數據進去、一個數值出來的標量函數它們的作用是對單個數值做變換或者對某個表達式的結果做二次計算。有個很容易被忽略的點數值函數里不少函數返回的是DECIMAL或DOUBLE類型這決定了結果在后續(xù)join、union、排序、比較時的行為。很多人只關注“算出來是多少”沒關注“算出來是什么類型”這是很多詭異bug的來源后面專題講。2. 舍入與截斷最值錢的四個函數2.1 ROUND的四舍五入和那個容易翻車的“四舍六入五成雙”round(x)直接對x取整round(x, d)保留d位小數。MySQL的round遵循四舍五入規(guī)則如果被保留位數的下一位是5則向前進位。重點是MySQL在某些情況下對正好處于.5的邊界值會出現(xiàn)“五成雙”現(xiàn)象。這不是bug而是底層浮點數的二進制表示誤差。比如round(2.5)結果是3但round(2.565, 2)可能得到2.56而不是2.57因為2.565在二進制里實際存儲為2.5649999999999999。如果用DECIMAL類型存儲的2.565round(2.565, 2)就能正確得到2.57。我踩過這個坑。某賬單系統(tǒng)的金額字段用的是DECIMAL(10, 2)后來改成從第三方接口讀DOUBLE類型結果日志里一堆舍入差異。排查到最后問題就出在存儲類型上。實操建議金額計算盡量用DECIMAL不要用FLOAT或DOUBLE如果需要絕對精確的四舍五入先把字段轉成DECIMAL再roundround的第二個參數d可以省略取整也可以傳負數比如round(1234, -2)得到1200round(1234, -3)得到1000這是對整數位做舍入做粗略統(tǒng)計時有用2.2 TRUNCATE直接截斷不跟你商量truncate(x, d)和round長得像但行為完全不同它把小數點后d位之后的數字直接砍掉不四舍五入。truncate(2.567, 2)結果是2.56truncate(2.999, 1)結果是2.9。業(yè)務里什么時候必須用truncate銀行存款利息計算、手續(xù)費分賬、部分業(yè)務統(tǒng)計里“只舍不入”規(guī)則。比如某支付通道的分潤規(guī)則是“按交易金額的0.6%計算分潤金額保留兩位小數不足一分的部分不做四舍五入直接舍去”。用round就會出現(xiàn)多給的情況用truncate才對。truncate也支持負數位truncate(1234, -2)結果是1200truncate(1299, -2)也是1200它純截斷不是四舍五入。注意truncate返回類型和round有細微差別前者返回DECIMAL后者在某些參數下返回DECIMAL但處理浮點輸入時會有類型提升。建議在寫查詢前先明確你想要什么類型別把結果直接塞進VARCHAR字段再做比較。2.3 CEIL與FLOOR向上取整和向下取整的邊界場景ceil(x)返回大于等于x的最小整數floor(x)返回小于等于x的最大整數。注意MySQL里還有一個ceiling跟ceil完全等價別以為一個是大寫一個變小寫了。邊界場景很值得琢磨ceil(5.0)結果是5不是6因為5.0本身就是整數ceil(-5.1)結果是-5因為-5大于-5.1floor(-5.1)結果是-6ceil(0)是0用floor分頁的場景很常見比如要把一批數據按每500條分一個桶需要給每條數據算桶號常見寫法是floor((rownum - 1) / 500) 1。這里不用round因為分桶必須要嚴格的向下邊界。向上取整的典型場景是算最少需要幾輛車、幾個盒子。比如有47件貨每箱最多裝12件需要ceil(47 / 12)得到4箱。有人寫floor(47 / 12) 1這種方法當47恰好能被12整除時結果會錯會多算1箱。所以能整除的場景老老實實用ceil。MySQL里5.0 / 2返回2.5000是DECIMAL注意它會保留小數點后4位跟整數除法的預期不一樣。涉及取整除法時建議顯式寫ceil(a / b)不要依賴隱式轉換。2.4 實操案例金額按分處理與評分統(tǒng)計案例一訂單金額分攤。某訂單總金額100元有3個商品需要把100元按比例分攤到每個商品上且分攤金額之和必須等于總金額不能因為四舍五入多一分或少一分。錯誤做法每個商品round(100 * item_price / total_price, 2)三個四舍五入后的數相加大概率不等于100.00。正確做法是最后一個商品用倒擠法SELECT item_name, CASE WHEN item_id (SELECT MAX(item_id) FROM order_items WHERE order_id 100) THEN 100 - SUM(rounded_amount) OVER (ORDER BY item_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) ELSE ROUND(100 * item_price / total_price, 2) END AS allocated_amount FROM order_items;這個寫法里前幾個商品按四舍五入分攤最后一個商品用100減去前面分攤金額之和來兜底保證總數不差。案例二課程評分展示。評分平均分要求保留1位小數直接round(avg(score), 1)沒問題。但如果你后續(xù)要按這個評分排序建議在ORDER BY里再算一次不要依賴界面展示已經格式化好的值因為如果界面層用format返回了字符串排序會變成字典序9.5會排在8.9前面。3. 基礎數學運算ABS、SIGN、MOD、POW、SQRT3.1 ABS與SIGN這兩個函數往往被忽略abs(x)取絕對值這個不復雜但它常被用在“兩條數據差異多少”的統(tǒng)計里。比如對比兩個時間戳字段的差值abs(timestampdiff(second, start_time, end_time))避免出現(xiàn)負數。sign(x)返回x的符號正數返回1負數返回-1零返回0。這個函數在做趨勢判斷時非常方便。比如統(tǒng)計本月銷售額與上月銷售額的環(huán)比可以用sign(current_sales - last_sales)直接看是增長、下降還是持平避免寫一堆case when。某運單系統(tǒng)里有一段邏輯判斷車輛GPS經緯度偏移是否在允許范圍內需要算偏移方向sign返回的結果直接作為方向調整系數配合絕對值計算使用邏輯很清晰。3.2 MOD取模的負數場景容易跟業(yè)務預期不一致mod(a, b)返回a除以b的余數也可以用a % b兩者在MySQL里等價。不過要注意MySQL里對負數的處理mod(-7, 3)結果是2而不是-1。這是由“余數必須與被除數符號無關、結果非負”的數學約定決定的。但有的語言里比如C或JavaScript的%-7 % 3結果是-1。如果你是從其他語言剛轉過來寫SQL這一點會坑到你怎么都想不明白。實際業(yè)務中取模最常見的是分表、分桶、奇偶判斷。比如訂單號取模分表order_id % 16把訂單散到16張表。這里你只要保證order_id是正數就沒問題。但如果order_id可能是負數比如某些補償單號帶負號就會出現(xiàn)負數桶號導致查詢走錯表。奇偶行篩選WHERE id % 2 1取奇數行WHERE id % 2 0取偶數行。3.3 POW、SQRT、EXP、LOG誰說用不到pow(a, b)和power(a, b)返回a的b次方。sqrt(x)返回平方根。exp(x)返回e的x次方。ln(x)返回以e為底的自然對數log(b, x)返回以b為底x的對數log10(x)返回以10為底的對數log2(x)返回以2為底的對數。這些函數在純業(yè)務SQL里確實不常用但在某些數據清洗、特征計算場景里非常管用。比如某推薦系統(tǒng)項目里要給用戶行為次數做特征縮放直接用log10(cnt 1)把幾十萬的大數值壓到個位數模型訓練效果明顯好于原始值。這里1是為了避免log10(0)報錯或返回NULL。功率計算場景某IOT項目計算設備功耗電流I的平方乘以電阻R直接pow(current, 2) * resistance比起先算current * current語義更清晰。有一個坑ln(0)會返回NULL并產生警告sqrt(-1)也是NULL不是報錯。在數據清洗時建議先用WHERE過濾掉非法值或者在表達式外面套一層ifnull兜底。4. RAND隨機數與抽樣4.1 RAND()與RAND(N)的區(qū)別rand()返回0到1之間的隨機浮點數包含0但不包含1。rand(n)是帶種子參數的隨機數用相同種子的情況下每次調用返回的隨機序列是一致的。這一點在做可復現(xiàn)的A/B測試分組時很有用。舉個例子某活動運營要給用戶隨機分成兩組做紅包測試要求每次跑SQL時分組結果一致這樣才能復現(xiàn)同一批用戶的分組?？梢栽诜纸M字段上用rand(user_id)而不是rand()這樣同一個user_id永遠返回同一個偽隨機數用戶分組穩(wěn)定。4.2 生成指定范圍隨機整數的方法最常見的是FLOOR(RAND() * (max - min 1)) min。比如生成1到100之間的隨機整數寫floor(rand() * 100) 1。如果想生成更多位數的驗證碼可以用LPAD(FLOOR(RAND() * 1000000), 6, 0)生成6位數字。LPAD是字符串函數這里借用了上一篇文章的內容可見函數是組合使用的單靠數值函數做不了完整的格式化。某抽獎項目里中獎名單要隨機抽取100個用戶直接ORDER BY RAND() LIMIT 100。這個寫法在小表上沒問題但在千萬級用戶表上ORDER BY RAND()會給每一行都生成隨機數再排序全表掃描加文件排序性能非常差。更優(yōu)的做法是先SELECT COUNT(*)拿到總行數然后用隨機偏移量去取SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users)) 1) LIMIT 100;這種方式只做一次隨機數計算加索引查詢比ORDER BY RAND()快一個數量級。但要注意如果id有空洞最終條數可能不足100通常可以配合LIMIT多取一些再補齊或者接受少量偏差。4.3 RAND在WHERE條件里每行執(zhí)行一次這個坑很隱蔽WHERE id FLOOR(RAND() * 1000)這類寫法很多人以為只執(zhí)行一次隨機數實際上MySQL會對每一行都重新計算RAND。也就是說每一行的返回條件都不一樣最終結果不可控甚至查不出來。如果你需要“隨機取一行”應該把隨機數算好放進變量或者用子查詢先算好SET rand_id FLOOR(RAND() * 1000) 1; SELECT * FROM users WHERE id rand_id;在存儲過程或腳本里這樣寫沒問題。如果是純SQL可以用SELECT * FROM users WHERE id (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users)) 1);這里的子查詢先執(zhí)行外層比較用的是固定的隨機值。5. 三角函數與進制轉換冷門但關鍵時刻救命5.1 三角函數先把角度轉弧度再說MySQL里的sin、cos、tan、asin、acos、atan、atan2、cot參數單位都是弧度不是角度。所以如果你拿到的是45度直接sin(45)會得到一個奇怪的結果必須先轉成弧度sin(radians(45))。radians(x)將角度轉弧度degrees(x)將弧度轉角度。atan2(y, x)返回點(x, y)與x軸正方向之間的角度范圍是-pi到pi。這個函數在計算兩個坐標點之間的方位角時非常實用。某地圖路徑計算項目里需要算兩個GPS點之間的初始方位角公式就是atan2(lon2 - lon1, lat2 - lat1)再轉成角度。這類場景雖然不常見但一旦遇到分不清弧度和角度就會算出一個完全不對勁的方位。實際開發(fā)中還有一類用法把二維平面上的角度轉換成弧度后配合cos和sin做坐標旋轉。某圖像處理Demo里要把標注框旋轉30度就用到了cos、sin和radians的組合。5.2 進制轉換BIN、OCT、HEX與CONVbin(x)將十進制轉為二進制字符串oct(x)轉八進制字符串hex(x)轉十六進制字符串。conv(n, from_base, to_base)是通用函數可以在任意進制間轉換。某項目里需要把設備上報的十六進制狀態(tài)碼轉成二進制判斷第幾位是1直接SELECT CONV(HEX(status_code), 16, 10);或者更直接地用conv(status_code, 10, 2)把十進制轉二進制再用字符串函數SUBSTRING提取對應位。hex還有個隱藏用法把字符串轉成十六進制比如hex(ABC)返回414243。但這不是數值函數的本意容易混淆使用前最好確認參數類型是數字還是字符串。這里建議在做IP地址等場景時優(yōu)先用精確的數值運算函數。6. 常見問題排查與實戰(zhàn)避坑指南6.1 ROUND返回類型不一致導致排序錯亂round(字段, 2)在處理不同字段類型時返回類型可能不同。比如字段是DECIMAL返回DECIMAL字段是DOUBLE返回DOUBLE。DOUBLE是浮點數在小數比較時可能出現(xiàn)精度問題。某報表項目里同一個金額字段有的表是DECIMAL(10,2)有的表是FLOATunion all之后按金額排序結果出現(xiàn)103.10排在103.9后面的情況。排查后發(fā)現(xiàn)是FLOAT字段經過round后變成DOUBLE精度已經丟失。建議做法統(tǒng)一用CAST(ROUND(amount, 2) AS DECIMAL(10,2))保證所有表的結果類型一致。6.2 FORMAT返回值是字符串別拿去參與計算format(12345.678, 2)返回的是12,345.68帶千位分隔符而且類型是字符串。有人做報表時用format做展示再把這個字段參與計算比如SUM(format(amount, 2))MySQL在隱式轉換時會把字符串轉成數值但帶逗號的字符串會變成奇怪的數字或者直接導致警告。要區(qū)分兩個需求展示用format沒問題計算用round或truncate。不要在計算中間層使用格式化函數。6.3 RAND在ORDER BY里能做抽樣但代價要心里有數前面提過ORDER BY RAND()在大表上的性能問題。這里再補充一個經驗如果表有主鍵且數字分布相對連續(xù)用偏移量抽樣的方式比ORDER BY RAND()快得多。但如果主鍵空洞特別多比如大量刪除導致id不連續(xù)建議用JOIN一張隨機生成的序號表或者直接接受取樣偏差。隨機抽樣還有一個常見場景從100個用戶里抽10個做測試。此時用SELECT * FROM users WHERE id IN ( SELECT id FROM users ORDER BY RAND() LIMIT 10 );配合IN子查詢相對可控。6.4 浮點精度丟失金額永遠別用FLOAT/DOUBLE這件事怎么強調都不過分。0.1 0.2在計算機里不等于0.3這在MySQL的DOUBLE里同樣存在。比如某賬單系統(tǒng)用DOUBLE存金額0.1 * 3算出0.30000000000000004頁面上顯示出來就是0.30000000000000004財務看到會直接炸。MySQL里浮點數與DECIMAL的區(qū)別就類似于你用分數表示1/3和用小數表示0.333333333的差別后者總會丟掉一點精度。金額字段用DECIMAL(10, 2)或DECIMAL(12, 4)計算時用DECIMAL運算最終展示再format。如果歷史表已經用了FLOAT遷移時做數據訂正千萬不要在SQL里依賴round去修補那只會把問題隱藏起來。6.5 數值函數速查表下表是實際開發(fā)中最常用到的幾個函數及其典型場景可以截圖保存。函數作用典型場景注意點ROUND(x, d)四舍五入保留d位小數賬單金額、評分計算浮點輸入可能導致邊界誤差TRUNCATE(x, d)直接截斷保留d位小數手續(xù)費只舍不入不四舍五入CEIL(x)向上取整計算需要幾個箱子/車子恰好整數時不進位FLOOR(x)向下取整分桶、分頁負數邊界注意ABS(x)取絕對值差異統(tǒng)計無特殊情況SIGN(x)返回符號1/0/-1趨勢判斷結果非正即負或零MOD(a, b)取模分表、奇偶判斷MySQL負數取模結果非負POW(a, b)冪運算特征計算注意返回DOUBLE精度SQRT(x)平方根幾何計算負數返回NULLRAND()0到1隨機數抽樣、分組WHERE里每行執(zhí)行一次RADIANS(x)角度轉弧度三角函數別忘記轉換CONV(n, a, b)任意進制轉換狀態(tài)碼解析返回字符串我在實際項目里最大的體會是數值函數本身不復雜復雜的是業(yè)務對精度的要求。你寫SQL之前一定要確認好三個問題——這個字段參與計算嗎結果要參與排序嗎要不要再和其他類型比較確認完這三個問題函數選型基本不會出偏差。最后分享一個小技巧如果要用ROUND但結果又要保證絕對精確可以在ROUND之前先CAST成DECIMAL比如ROUND(CAST(amount AS DECIMAL(20, 4)), 2)。這看起來多了一步實際上能幫你躲掉90%的浮點精度坑。數值函數的坑我踩了一遍把你的原始字段類型和結果類型管理好這張表里的函數就用得明明白白。