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