1. 問題現(xiàn)場(chǎng)一個(gè)“詭異”的數(shù)據(jù)不一致事件最近在排查一個(gè)數(shù)據(jù)同步任務(wù)時(shí)遇到了一個(gè)相當(dāng)“詭異”的問題。我們的業(yè)務(wù)系統(tǒng)使用達(dá)夢(mèng)數(shù)據(jù)庫Dameng Database作為核心數(shù)據(jù)倉庫在一次從源表到目標(biāo)表的ETL過程中發(fā)現(xiàn)目標(biāo)表中某些記錄的ID值與源表對(duì)不上。這可不是小事ID通常是主鍵或唯一標(biāo)識(shí)一旦錯(cuò)亂后續(xù)的關(guān)聯(lián)查詢、數(shù)據(jù)一致性校驗(yàn)都會(huì)出大問題。初步排查源表和目標(biāo)表的結(jié)構(gòu)定義看起來一模一樣都是DECIMAL(20, 0)類型理論上可以存儲(chǔ)20位精度的整數(shù)。同步程序邏輯也很簡(jiǎn)單就是直接的INSERT INTO ... SELECT ...。但偏偏有幾條記錄的ID在目標(biāo)表里尾數(shù)變成了0。比如源表ID是12345678901234567890到了目標(biāo)表卻成了12345678901234567800最后兩位“90”莫名其妙地變成了“00”。這種精度丟失問題如果發(fā)生在金額字段上大家會(huì)立刻警覺。但當(dāng)它發(fā)生在DECIMAL類型、且被用作ID的字段時(shí)很容易被忽視或者被誤認(rèn)為是程序邏輯錯(cuò)誤、網(wǎng)絡(luò)傳輸問題。實(shí)際上這正是達(dá)夢(mèng)數(shù)據(jù)庫乃至許多數(shù)據(jù)庫中DECIMAL/NUMERIC類型處理的一個(gè)深水區(qū)。今天我就結(jié)合這次踩坑經(jīng)歷把DECIMAL類型精度丟失的來龍去脈、根因定位和解決方案徹底講清楚。2. DECIMAL類型精度的本質(zhì)與達(dá)夢(mèng)的實(shí)現(xiàn)特點(diǎn)要理解精度丟失首先得拋開“DECIMAL就是絕對(duì)精確”的慣性思維。DECIMAL或NUMERIC類型在SQL標(biāo)準(zhǔn)中被定義為“精確數(shù)字類型”其精度Precision和小數(shù)位數(shù)Scale在定義時(shí)確定。例如DECIMAL(20, 0)表示總共20位數(shù)字其中小數(shù)位為0即一個(gè)20位的整數(shù)。然而“精確”的實(shí)現(xiàn)依賴于數(shù)據(jù)庫底層如何存儲(chǔ)和計(jì)算。達(dá)夢(mèng)數(shù)據(jù)庫在此有其特定的實(shí)現(xiàn)方式這也是問題的根源之一。2.1 達(dá)夢(mèng)DECIMAL的底層存儲(chǔ)與計(jì)算邏輯達(dá)夢(mèng)數(shù)據(jù)庫的DECIMAL類型并非以純粹的字符串或二進(jìn)制原樣存儲(chǔ)。為了優(yōu)化存儲(chǔ)空間和計(jì)算效率它內(nèi)部會(huì)采用一種壓縮的二進(jìn)制格式。在進(jìn)行數(shù)值運(yùn)算包括賦值、類型轉(zhuǎn)換、甚至某些查詢條件處理時(shí)數(shù)據(jù)庫引擎可能會(huì)在內(nèi)部對(duì)數(shù)值進(jìn)行中間轉(zhuǎn)換或計(jì)算。關(guān)鍵在于這個(gè)內(nèi)部處理過程可能存在“隱式”的精度取舍規(guī)則。當(dāng)從一個(gè)“高精度”的數(shù)值上下文如一個(gè)計(jì)算中間結(jié)果賦值給一個(gè)“低精度”的列定義時(shí)如果未明確指定處理方式數(shù)據(jù)庫可能會(huì)按照其默認(rèn)規(guī)則進(jìn)行四舍五入或截?cái)?。在我們的案例中DECIMAL(20,0)看似精度很高但如果同步過程中涉及了某些隱式轉(zhuǎn)換或函數(shù)處理就可能觸發(fā)這個(gè)機(jī)制。注意很多開發(fā)者認(rèn)為只有FLOAT或DOUBLE才會(huì)丟失精度DECIMAL是安全的。這個(gè)觀念在“理想”的純存儲(chǔ)場(chǎng)景下成立但一旦卷入數(shù)據(jù)庫的運(yùn)算引擎、客戶端驅(qū)動(dòng)序列化/反序列化、甚至不同版本間的差異DECIMAL的精度邊界就可能被觸及。2.2 精度丟失的常見觸發(fā)場(chǎng)景分析結(jié)合這次排查和其他案例精度丟失通常發(fā)生在以下幾個(gè)環(huán)節(jié)隱式類型轉(zhuǎn)換這是最隱蔽的坑。例如在INSERT ... SELECT語句中如果源表達(dá)式的結(jié)果在數(shù)據(jù)庫內(nèi)部被推斷為一種臨時(shí)的、精度可能不足的數(shù)值類型再賦值給目標(biāo)DECIMAL列時(shí)就會(huì)發(fā)生截?cái)唷？蛻舳蓑?qū)動(dòng)處理通過JDBC、ODBC等客戶端接口傳輸DECIMAL數(shù)據(jù)時(shí)驅(qū)動(dòng)庫可能先將數(shù)值轉(zhuǎn)換為Java的BigDecimal或C/C的某種高精度類型但在某些配置下如BigDecimal的scale處理不當(dāng)序列化/反序列化過程可能導(dǎo)致精度信息變化。計(jì)算過程中的中間結(jié)果即使是最簡(jiǎn)單的SELECT id * 1.0 FROM table這個(gè)* 1.0的操作可能會(huì)迫使id參與浮點(diǎn)運(yùn)算上下文雖然結(jié)果仍以DECIMAL顯示但中間計(jì)算過程可能已經(jīng)引入了誤差。版本或配置差異不同版本的達(dá)夢(mèng)數(shù)據(jù)庫對(duì)于DECIMAL運(yùn)算的默認(rèn)精度規(guī)則可能有細(xì)微調(diào)整。從低版本遷移數(shù)據(jù)到高版本或者不同的服務(wù)器參數(shù)配置如數(shù)值相關(guān)的兼容性參數(shù)都可能影響最終結(jié)果。我們的案例經(jīng)過深度排查最終鎖定在了第一個(gè)場(chǎng)景隱式類型轉(zhuǎn)換。但定位過程并非一蹴而就。3. 完整的排查鏈路從現(xiàn)象到根因當(dāng)發(fā)現(xiàn)數(shù)據(jù)不一致時(shí)切忌盲目修改代碼或調(diào)整表結(jié)構(gòu)。一個(gè)系統(tǒng)化的排查思路至關(guān)重要。以下是我們這次采用的排查步驟具有普適的參考價(jià)值。3.1 第一步確認(rèn)不一致的范圍與模式首先不能只盯著一條記錄。我們編寫了一個(gè)對(duì)比腳本核心SQL如下-- 假設(shè)源表為 source_table 目標(biāo)表為 target_table 連接鍵為 other_key SELECT s.id as source_id, t.id as target_id, s.other_key FROM source_table s INNER JOIN target_table t ON s.other_key t.other_key WHERE s.id t.id;通過這個(gè)查詢我們找出了所有ID不一致的記錄。然后人工分析這些不一致的ID尋找規(guī)律。我們發(fā)現(xiàn)了一個(gè)關(guān)鍵特征所有發(fā)生變化的ID其最后兩位原本都是“90”且全部變成了“00”。這個(gè)規(guī)律強(qiáng)烈暗示了問題不是隨機(jī)的比特位翻轉(zhuǎn)而是有規(guī)則的截?cái)嗷蛏崛搿?.2 第二步審查數(shù)據(jù)同步的完整鏈路我們的同步任務(wù)邏輯并不復(fù)雜但為了排除所有環(huán)節(jié)我們將其拆解源端查詢SELECT id, ... FROM source_table WHERE ...數(shù)據(jù)傳輸通過ETL工具或程序從達(dá)夢(mèng)數(shù)據(jù)庫讀取結(jié)果集。目標(biāo)端寫入INSERT INTO target_table (id, ...) VALUES (?, ...)我們?cè)贓TL工具中配置了詳細(xì)的日志打印出從源庫讀出的id值和準(zhǔn)備插入目標(biāo)庫的id值。日志顯示在ETL工具的內(nèi)存中id值已經(jīng)是丟失精度后的值如12345678901234567800。這說明問題發(fā)生在“從達(dá)夢(mèng)數(shù)據(jù)庫源端讀取數(shù)據(jù)”這個(gè)環(huán)節(jié)而不是在寫入目標(biāo)庫時(shí)。3.3 第三步在數(shù)據(jù)庫層面進(jìn)行隔離測(cè)試既然問題出在“讀”的階段我們直接在達(dá)夢(mèng)數(shù)據(jù)庫的SQL命令行工具DIsql中進(jìn)行最簡(jiǎn)化的復(fù)現(xiàn)測(cè)試?yán)@過任何客戶端程序。這是定位數(shù)據(jù)庫內(nèi)部問題的黃金法則。我們構(gòu)造了測(cè)試表和數(shù)據(jù)-- 創(chuàng)建測(cè)試表 模擬源表結(jié)構(gòu) CREATE TABLE test_source (id DECIMAL(20,0), name VARCHAR(50)); INSERT INTO test_source VALUES (12345678901234567890, test1); -- 直接查詢 觀察原始輸出 SELECT id FROM test_source;在DIsql中執(zhí)行顯示結(jié)果正確為12345678901234567890。這說明單純的存儲(chǔ)和簡(jiǎn)單查詢沒有問題。接下來我們模擬了同步任務(wù)中可能存在的、更復(fù)雜的查詢場(chǎng)景。最終通過逐行比對(duì)同步任務(wù)中使用的真實(shí)源SQL我們發(fā)現(xiàn)了端倪。原始SQL中為了進(jìn)行某種數(shù)據(jù)清洗使用了一個(gè)CASE WHEN表達(dá)式并且在這個(gè)表達(dá)式里對(duì)id進(jìn)行了一個(gè)看似無害的算術(shù)操作-- 這是簡(jiǎn)化后的問題SQL片段 SELECT CASE WHEN some_condition THEN id / 10000 * 10000 -- 問題出在這里 ELSE id END AS transformed_id, other_columns FROM source_table根因找到了id / 10000 * 10000這個(gè)表達(dá)式是罪魁禍?zhǔn)住ｉ_發(fā)者的本意可能是想將ID對(duì)齊到某個(gè)萬位區(qū)間。但在達(dá)夢(mèng)數(shù)據(jù)庫以及許多其他數(shù)據(jù)庫中id / 10000這個(gè)除法運(yùn)算其結(jié)果的數(shù)據(jù)類型并不是DECIMAL。3.4 第四步根因深度解析——除法的類型推導(dǎo)陷阱在達(dá)夢(mèng)數(shù)據(jù)庫中當(dāng)DECIMAL類型與整數(shù)進(jìn)行除法運(yùn)算時(shí)結(jié)果的數(shù)據(jù)類型會(huì)發(fā)生變化。根據(jù)達(dá)夢(mèng)的運(yùn)算規(guī)則整數(shù)除法可能會(huì)產(chǎn)生一個(gè)精度和小數(shù)位數(shù)都發(fā)生變化的數(shù)值。數(shù)據(jù)庫為了保存除法可能產(chǎn)生的小數(shù)結(jié)果會(huì)分配一個(gè)臨時(shí)的、具有小數(shù)位數(shù)的DECIMAL類型。對(duì)于DECIMAL(20,0) / 10000數(shù)據(jù)庫會(huì)先計(jì)算一個(gè)中間結(jié)果。這個(gè)中間結(jié)果為了容納小數(shù)其scale小數(shù)位數(shù)可能被擴(kuò)展。隨后這個(gè)中間結(jié)果再乘以10000。然而乘法運(yùn)算并不能保證完美地還原所有原始精度信息尤其是在中間結(jié)果的精度和標(biāo)度已經(jīng)改變的情況下。最終這個(gè)表達(dá)式的結(jié)果再被賦值給一個(gè)DECIMAL(20,0)的列或別名時(shí)數(shù)據(jù)庫會(huì)執(zhí)行一個(gè)隱式的CAST操作。在這個(gè)隱式轉(zhuǎn)換中如果結(jié)果值的小數(shù)部分不為零數(shù)據(jù)庫會(huì)按照默認(rèn)的舍入規(guī)則進(jìn)行處理。而對(duì)于恰好處于舍入邊界的情況如 .90就可能出現(xiàn)我們看到的“90”變“00”的現(xiàn)象。實(shí)際上12345678901234567890 / 10000 1234567890123456.7890。這個(gè)結(jié)果是一個(gè)DECIMAL(20,4)類型假設(shè)。再乘以10000理論上得到12345678901234567890.0000。但在內(nèi)部浮點(diǎn)計(jì)算或精度轉(zhuǎn)換中這個(gè).0000可能并沒有被完美地表示為整數(shù)而是存在一個(gè)極其微小的誤差比如12345678901234567889.999999999...。當(dāng)將這個(gè)值隱式轉(zhuǎn)換為DECIMAL(20,0)時(shí)達(dá)夢(mèng)的默認(rèn)舍入規(guī)則可能是四舍五入也可能是銀行家舍入法導(dǎo)致其被舍入為12345678901234567890。然而在某些邊界條件下或特定版本中這個(gè)舍入行為可能出錯(cuò)直接截?cái)嗔诵?shù)部分導(dǎo)致了精度丟失。實(shí)操心得永遠(yuǎn)不要對(duì)高精度的DECIMAL類型尤其是用作ID時(shí)進(jìn)行除法運(yùn)算除非你完全清楚并顯式控制了運(yùn)算結(jié)果的類型。對(duì)于ID這類需要絕對(duì)精確的整數(shù)所有運(yùn)算都應(yīng)放在應(yīng)用層進(jìn)行或者使用數(shù)據(jù)庫的整數(shù)類型如BIGINT如果值域允許的話。4. 解決方案與防御性編程實(shí)踐定位到根因后解決起來就有方向了。我們的目標(biāo)不僅是修復(fù)當(dāng)前SQL更要建立防止此類問題再次發(fā)生的機(jī)制。4.1 立即修復(fù)重寫問題SQL避免隱式轉(zhuǎn)換對(duì)于有問題的SQL最直接的修復(fù)是消除危險(xiǎn)的隱式轉(zhuǎn)換。我們有幾種方案方案一使用顯式類型轉(zhuǎn)換CAST在除法運(yùn)算后立即將結(jié)果明確轉(zhuǎn)換回我們需要的精度。這是最清晰的做法。SELECT CASE WHEN some_condition THEN CAST(id / 10000 * 10000 AS DECIMAL(20,0)) ELSE id END AS transformed_id, other_columns FROM source_table通過CAST(... AS DECIMAL(20,0))我們明確告知數(shù)據(jù)庫最終需要的類型強(qiáng)制其在此規(guī)則下進(jìn)行轉(zhuǎn)換避免了不可控的隱式行為。方案二重構(gòu)業(yè)務(wù)邏輯避免對(duì)ID進(jìn)行數(shù)值運(yùn)算這是更根本的解決方案。經(jīng)過和業(yè)務(wù)方確認(rèn)id / 10000 * 10000這個(gè)操作的本意是為了分組。我們可以用其他方式實(shí)現(xiàn)例如使用數(shù)值范圍或字符串函數(shù)。SELECT CASE WHEN some_condition THEN id -- 直接使用原ID分組邏輯在應(yīng)用層或通過其他字段實(shí)現(xiàn) ELSE id END AS transformed_id, FLOOR(id / 10000) as group_range, -- 如果需要分組信息單獨(dú)作為一個(gè)字段 other_columns FROM source_table我們將分組邏輯剝離id字段保持原樣不動(dòng)從源頭上杜絕了精度風(fēng)險(xiǎn)。4.2 長(zhǎng)期防御設(shè)計(jì)規(guī)范與審查清單一次踩坑全員受益。我們團(tuán)隊(duì)據(jù)此更新了數(shù)據(jù)庫開發(fā)規(guī)范ID字段類型選型優(yōu)先順序BIGINTDECIMAL(N,0) 字符串類型。如果ID是純數(shù)字且范圍在BIGINT內(nèi)±922億億優(yōu)先使用BIGINT。BIGINT是整數(shù)運(yùn)算沒有精度丟失風(fēng)險(xiǎn)。禁止對(duì)DECIMAL ID進(jìn)行算術(shù)運(yùn)算在SQL中嚴(yán)禁對(duì)DECIMAL類型的ID進(jìn)行加、減、乘、除、取模等任何算術(shù)運(yùn)算。相關(guān)業(yè)務(wù)邏輯必須上提到應(yīng)用層使用BigInteger(Java) 等無損類型處理。顯式轉(zhuǎn)換原則如果必須進(jìn)行涉及DECIMAL的復(fù)雜計(jì)算在關(guān)鍵節(jié)點(diǎn)使用CAST或CONVERT函數(shù)明確指定結(jié)果的數(shù)據(jù)類型和精度。同步任務(wù)校驗(yàn)所有ETL數(shù)據(jù)同步任務(wù)必須在流程中增加“數(shù)據(jù)一致性校驗(yàn)”步驟。不僅僅是計(jì)數(shù)校驗(yàn)必須包含關(guān)鍵字段尤其是ID的逐行比對(duì)采樣。SQL審核聚焦點(diǎn)在代碼審查時(shí)對(duì)SQL中的數(shù)值運(yùn)算保持高度警惕特別是DECIMAL列的參與。審查CASE WHEN、WHERE條件中的計(jì)算表達(dá)式、聚合函數(shù)內(nèi)的計(jì)算等。4.3 達(dá)夢(mèng)數(shù)據(jù)庫特定參數(shù)檢查雖然我們的問題主要出在SQL寫法但了解數(shù)據(jù)庫本身的配置也能防患于未然?？梢詸z查達(dá)夢(mèng)數(shù)據(jù)庫的以下參數(shù)通過SELECT * FROM V$PARAMETER WHERE NAME LIKE %NUMERIC% or NAME LIKE %DECIMAL%;查詢COMPATIBLE_MODE是否啟用了與其他數(shù)據(jù)庫如Oracle、MySQL的兼容模式不同模式下數(shù)值運(yùn)算規(guī)則可能有差異。NUMERIC_ROUND_MODE數(shù)值舍入模式。了解其設(shè)置如四舍五入、向上取整等有助于理解邊界情況下的行為。不過不建議為了修復(fù)一個(gè)具體的SQL問題而去隨意修改全局?jǐn)?shù)據(jù)庫參數(shù)這可能會(huì)帶來未知的副作用。修正SQL語句本身是更安全、更可控的方式。5. 擴(kuò)展思考其他數(shù)據(jù)庫的類似問題與通用法則精度丟失并非達(dá)夢(mèng)數(shù)據(jù)庫獨(dú)有。這是一個(gè)在各類數(shù)據(jù)庫中都可能遇到的通用性問題。MySQL/PostgreSQL它們的DECIMAL/NUMERIC類型在除法運(yùn)算時(shí)結(jié)果精度會(huì)根據(jù)操作數(shù)的精度和數(shù)據(jù)庫的規(guī)則進(jìn)行擴(kuò)展但同樣存在隱式轉(zhuǎn)換和舍入的風(fēng)險(xiǎn)。在復(fù)雜表達(dá)式賦值時(shí)也需要特別注意。OracleOracle的NUMBER類型非常強(qiáng)大但除法運(yùn)算也可能產(chǎn)生無限循環(huán)小數(shù)導(dǎo)致存儲(chǔ)或顯示時(shí)被舍入。SQL ServerDECIMAL除法運(yùn)算時(shí)結(jié)果精度和小數(shù)位數(shù)的計(jì)算規(guī)則更為復(fù)雜隱式轉(zhuǎn)換也可能導(dǎo)致意外截?cái)唷Ｍㄓ梅烙▌t整數(shù)用整數(shù)類型自增ID、業(yè)務(wù)編號(hào)等純整數(shù)優(yōu)先使用數(shù)據(jù)庫的整數(shù)類型INT,BIGINT。精確計(jì)算用明確精度對(duì)于財(cái)務(wù)等要求精確計(jì)算的DECIMAL字段在表設(shè)計(jì)時(shí)就確定好合理的(precision, scale)并在所有計(jì)算中保持一致性。避免數(shù)據(jù)庫層復(fù)雜計(jì)算將復(fù)雜的、尤其是涉及高精度數(shù)值的業(yè)務(wù)邏輯盡可能放在應(yīng)用層處理。應(yīng)用層語言如Java的BigDecimal的精度控制通常更直觀、更符合開發(fā)者預(yù)期。測(cè)試邊界數(shù)據(jù)在測(cè)試階段不僅要測(cè)試正常數(shù)據(jù)更要測(cè)試邊界數(shù)據(jù)。對(duì)于DECIMAL字段要特意測(cè)試極大值、極小值、以及可能引發(fā)舍入的臨界值如以4、5、9結(jié)尾的數(shù)字。這次達(dá)夢(mèng)數(shù)據(jù)庫DECIMAL類型ID的精度丟失問題給我上了一堂生動(dòng)的“數(shù)據(jù)庫精確類型”課。它提醒我們即使是最基礎(chǔ)的字段類型在復(fù)雜的數(shù)據(jù)庫引擎和SQL上下文中也可能表現(xiàn)出非直覺的行為。解決問題的關(guān)鍵不在于記住所有數(shù)據(jù)庫的特定規(guī)則而在于建立嚴(yán)謹(jǐn)?shù)脑O(shè)計(jì)規(guī)范、養(yǎng)成防御性的編程習(xí)慣并掌握一套從現(xiàn)象到根因的系統(tǒng)化排查方法。當(dāng)數(shù)據(jù)不一致發(fā)生時(shí)耐心地、像偵探一樣層層剝離假設(shè)最終總能找到那個(gè)隱藏在細(xì)節(jié)中的“魔鬼”。