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