夢(mèng)數(shù)據(jù)庫(kù)大小寫(xiě)敏感機(jī)制詳解：從原理到實(shí)踐避坑)
1. 項(xiàng)目概述達(dá)夢(mèng)大小寫(xiě)敏感到底是個(gè)什么事做數(shù)據(jù)庫(kù)開(kāi)發(fā)或者運(yùn)維的朋友只要是第一次接觸達(dá)夢(mèng)數(shù)據(jù)庫(kù)十有八九會(huì)遇到一個(gè)讓人摸不著頭腦的現(xiàn)象明明建表時(shí)寫(xiě)的是 USER_INFO去查詢(xún)的時(shí)候?qū)?user_info 卻報(bào)表或視圖不存在。如果之前一直用的是 MySQL碰上這種情況的第一反應(yīng)往往是這什么鬼數(shù)據(jù)庫(kù)連大小寫(xiě)都不認(rèn)。先給結(jié)論達(dá)夢(mèng)數(shù)據(jù)庫(kù)默認(rèn)是大小寫(xiě)敏感的這一點(diǎn)和 Oracle 的對(duì)齊策略一致。也就是說(shuō)你在達(dá)夢(mèng)里建表、建字段、寫(xiě)存儲(chǔ)過(guò)程、調(diào)用函數(shù)凡是涉及標(biāo)識(shí)符的解析系統(tǒng)都會(huì)嚴(yán)格區(qū)分 A 和 a。而很多從 MySQL 轉(zhuǎn)過(guò)來(lái)的人思維慣性還停留在表名隨便寫(xiě)、字段隨便引的寬松模式一上來(lái)就踩坑。這個(gè)特性不是某個(gè)版本臨時(shí)加進(jìn)去的而是達(dá)夢(mèng)從架構(gòu)設(shè)計(jì)層面就定下來(lái)的默認(rèn)行為。它直接影響建表語(yǔ)句的書(shū)寫(xiě)規(guī)范、遷移腳本的兼容性、應(yīng)用代碼里的 SQL 拼接方式甚至影響你初始化實(shí)例時(shí)參數(shù)怎么配。所以不管你是 DBA、后端開(kāi)發(fā)、數(shù)據(jù)遷移工程師還是剛接觸達(dá)夢(mèng)的初學(xué)者第一課都應(yīng)該把大小寫(xiě)敏感這四個(gè)字徹底搞明白。這篇文章就圍繞達(dá)夢(mèng)數(shù)據(jù)庫(kù)的大小寫(xiě)敏感機(jī)制把原理、驗(yàn)證方法、實(shí)操步驟、踩坑案例全都拆開(kāi)講一遍。文章里沒(méi)有廢話(huà)全部是我在實(shí)際項(xiàng)目里驗(yàn)證過(guò)的東西照著操作就能復(fù)現(xiàn)結(jié)論。2. 核心機(jī)制拆解達(dá)夢(mèng)大小寫(xiě)敏感的工作原理2.1 標(biāo)識(shí)符解析引號(hào)才是真正的開(kāi)關(guān)達(dá)夢(mèng)數(shù)據(jù)庫(kù)對(duì)標(biāo)識(shí)符表名、列名、視圖名、索引名等的大小寫(xiě)處理核心規(guī)則可以概括成一句話(huà)默認(rèn)情況下不帶雙引號(hào)的標(biāo)識(shí)符會(huì)被系統(tǒng)統(tǒng)一轉(zhuǎn)換為大寫(xiě)字母存儲(chǔ)帶雙引號(hào)的標(biāo)識(shí)符則嚴(yán)格保留原始大小寫(xiě)。舉個(gè)例子執(zhí)行建表語(yǔ)句CREATE TABLE user_info ( id INT, name VARCHAR(20) );達(dá)夢(mèng)實(shí)際存儲(chǔ)的表名是USER_INFO而不是user_info。后續(xù)查詢(xún)時(shí)如果你寫(xiě)SELECT * FROM user_info系統(tǒng)會(huì)把user_info轉(zhuǎn)成USER_INFO再去找所以能查到。但你如果寫(xiě)SELECT * FROM user_info系統(tǒng)會(huì)按雙引號(hào)內(nèi)的原始內(nèi)容去找即小寫(xiě)的user_info結(jié)果就會(huì)報(bào)表不存在。反過(guò)來(lái)如果你建表時(shí)用了雙引號(hào)CREATE TABLE user_info ( id INT, name VARCHAR(20) );那么這張表的名字就是小寫(xiě)的user_info不帶引號(hào)查詢(xún)SELECT * FROM USER_INFO反而會(huì)失敗因?yàn)橄到y(tǒng)把 USER_INFO 當(dāng)成名稱(chēng)去找而真實(shí)表名是小寫(xiě)。這個(gè)機(jī)制和 Oracle 完全一致。換句通俗的話(huà)講雙引號(hào)就是大小寫(xiě)敏感的開(kāi)關(guān)加了雙引號(hào)字母就原樣保留不加雙引號(hào)全部自動(dòng)轉(zhuǎn)大寫(xiě)。這也是為什么有些老 Oracle 開(kāi)發(fā)習(xí)慣把所有 SQL 關(guān)鍵字和標(biāo)識(shí)符都寫(xiě)大寫(xiě)因?yàn)樵谶_(dá)夢(mèng)里不帶引號(hào)的標(biāo)識(shí)符最終都會(huì)被轉(zhuǎn)成大寫(xiě)你寫(xiě)小寫(xiě)只是讓系統(tǒng)多了一步轉(zhuǎn)換過(guò)程最終解析結(jié)果是一樣的。2.2 字符串比較VARCHAR 與 CHAR 的判定邏輯很多人在沒(méi)弄清楚之前會(huì)下意識(shí)認(rèn)為大小寫(xiě)敏感就是所有字符串比較都敏感這是錯(cuò)誤的理解需要拆分到兩個(gè)層面看。在達(dá)夢(mèng)數(shù)據(jù)庫(kù)中大小寫(xiě)敏感特性主要體現(xiàn)在兩個(gè)地方第一是上面講的標(biāo)識(shí)符解析第二是字符串比較的規(guī)則。但這里有個(gè)關(guān)鍵細(xì)節(jié)——標(biāo)識(shí)符的大小寫(xiě)敏感性跟字符串?dāng)?shù)據(jù)的大小寫(xiě)敏感性參數(shù)是兩套東西不能混為一談。達(dá)夢(mèng)的字符串敏感度實(shí)際上有三個(gè)枚舉值SENSITIVE敏感、INSENSITIVE不敏感、INVISITIVE忽略實(shí)際上就是 cast 時(shí)不感知比較時(shí)按敏感處理。這個(gè)參數(shù)叫LENGTH_IN_CHAR之外的一個(gè)配置在部分版本的文檔里也被稱(chēng)為大小寫(xiě)敏感配置項(xiàng)它作用在像WHERE name ABC這種條件判斷上。具體來(lái)說(shuō)如果實(shí)例初始化時(shí)設(shè)置了字符串大小寫(xiě)不敏感那么abc ABC為真如果是敏感模式abc ABC為假必須通過(guò)UPPER(abc) ABC方式繞行。這個(gè)配置項(xiàng)在初始化實(shí)例時(shí)通過(guò)參數(shù)控制一旦實(shí)例創(chuàng)建完成后期不能直接動(dòng)態(tài)修改只能通過(guò)重建實(shí)例或者專(zhuān)門(mén)的工具處理。舉一個(gè)實(shí)際場(chǎng)景達(dá)夢(mèng)數(shù)據(jù)庫(kù)中如果默認(rèn)大小寫(xiě)敏感那么查詢(xún)WHERE user_name admin和WHERE user_name ADMIN返回的結(jié)果可能完全不同。如果你在開(kāi)發(fā)時(shí)沒(méi)注意登錄模塊就很容易出問(wèn)題——用戶(hù)表里存的是 Admin程序里傳參是 admin結(jié)果就是校驗(yàn)永遠(yuǎn)對(duì)不上。2.3 三個(gè)層面看大小寫(xiě)命名、數(shù)據(jù)、排序要把大小寫(xiě)敏感徹底吃透我建議你把這個(gè)問(wèn)題想成三個(gè)獨(dú)立維度第一維度是命名層即表名、字段名等標(biāo)識(shí)符的大小寫(xiě)解析規(guī)則。默認(rèn)轉(zhuǎn)大寫(xiě)雙引號(hào)保留原樣。這是建表、寫(xiě) SQL 時(shí)必須時(shí)刻記住的。第二維度是數(shù)據(jù)層即存儲(chǔ)在表里的字符串字面值在比較時(shí)是否區(qū)分大小寫(xiě)。這取決于初始化參數(shù)配置默認(rèn)敏感可配置為不敏感。這是編寫(xiě)業(yè)務(wù) SQL、處理用戶(hù)輸入、做登錄校驗(yàn)等操作時(shí)需要關(guān)注的。第三維度是排序?qū)蛹磁判驎r(shí)的字符序規(guī)則。達(dá)夢(mèng)的默認(rèn)排序規(guī)則通常是二進(jìn)制排序也就是按字符編碼值排序。在二進(jìn)制排序下大寫(xiě)字母比如 ASCII 65 的 A和小寫(xiě)字母ASCII 97 的 a天然被區(qū)分開(kāi)所以排序結(jié)果里所有大寫(xiě)字母會(huì)排在小寫(xiě)字母前面。如果你想實(shí)現(xiàn)忽略大小寫(xiě)的排序需要顯式使用 LOWER 或 UPPER 函數(shù)處理或者初始化時(shí)指定不區(qū)分大小寫(xiě)的排序規(guī)則。下面用一個(gè)表格把三個(gè)維度整理清楚層面默認(rèn)行為影響對(duì)象調(diào)整方式標(biāo)識(shí)符解析不帶引號(hào)轉(zhuǎn)大寫(xiě)表名、列名、索引名、視圖名書(shū)寫(xiě)時(shí)加雙引號(hào)保留大小寫(xiě)字符串比較敏感WHERE 條件、JOIN 關(guān)聯(lián)、去重判斷初始化參數(shù)控制后期可通過(guò)函數(shù)繞行字符排序二進(jìn)制序區(qū)分大小寫(xiě)ORDER BY 結(jié)果、索引排序初始化時(shí)指定排序規(guī)則或 SQL 中函數(shù)處理理解這三個(gè)維度后面所有問(wèn)題都可以對(duì)號(hào)入座。3. 不同數(shù)據(jù)庫(kù)的遷移陷阱與初始化參數(shù)選擇3.1 MySQL 用戶(hù)的重災(zāi)區(qū)習(xí)慣性裸寫(xiě)表名我接觸過(guò)的項(xiàng)目中凡是達(dá)夢(mèng)適配翻車(chē)的十有八九是從 MySQL 遷移過(guò)來(lái)的開(kāi)發(fā)人員。為什么因?yàn)?MySQL 在 Linux 下默認(rèn)表名區(qū)分大小寫(xiě)、字段名不區(qū)分在 Windows 下都不區(qū)分而且 MySQL 的表名大小寫(xiě)規(guī)則是由lower_case_table_names參數(shù)控制的很多人從來(lái)沒(méi)研究過(guò)這個(gè)參數(shù)完全不知道自己的表名到底是大寫(xiě)還是小寫(xiě)。到了達(dá)夢(mèng)這里默認(rèn)全部轉(zhuǎn)大寫(xiě)導(dǎo)致的問(wèn)題非常多。舉一個(gè)真實(shí)發(fā)生過(guò)的案例某公司的業(yè)務(wù)系統(tǒng)原來(lái)跑在 MySQL 上數(shù)據(jù)庫(kù)里有兩張表一張叫OrderInfo一張叫orderinfo。在 MySQL 的 Windows 環(huán)境下這兩個(gè)名字其實(shí)指向同一張表因?yàn)椴粎^(qū)分大小寫(xiě)。開(kāi)發(fā)人員建表的時(shí)候手抖多建了一張也沒(méi)發(fā)現(xiàn)。遷移到達(dá)夢(mèng)時(shí)遷移工具執(zhí)行建表腳本先是建了OrderInfo然后建orderinfo結(jié)果達(dá)夢(mèng)把兩個(gè)都轉(zhuǎn)成大寫(xiě)ORDERINFO第二個(gè)建表語(yǔ)句直接報(bào)表名重復(fù)。這種錯(cuò)誤還算好排查。更隱蔽的是程序代碼里的動(dòng)態(tài) SQL。Java 代碼里用 MyBatis 寫(xiě) mapperXML 里寫(xiě)的表名是駝峰命名userLoginLog在 MySQL 下跑了幾年都沒(méi)事改成達(dá)夢(mèng)后突然暴露出ORA-00942: table or view does not exist式的錯(cuò)誤實(shí)際錯(cuò)誤信息是表或視圖不存在。原因就是達(dá)夢(mèng)把不帶引號(hào)的標(biāo)識(shí)符轉(zhuǎn)成USERLOGINLOG跟真實(shí)表名對(duì)不上。解決這類(lèi)問(wèn)題的思路有兩個(gè)要么把建表腳本全部改成大寫(xiě)統(tǒng)一風(fēng)格要么所有 SQL 里的表名都帶雙引號(hào)完全保留原始大小寫(xiě)。我個(gè)人的建議是第一種也就是規(guī)范成大寫(xiě)。因?yàn)橐坏┠汩_(kāi)始到處加雙引號(hào)后續(xù)的維護(hù)成本會(huì)非常高每個(gè) SQL 都得小心翼翼檢查引號(hào)位置人力成本不劃算。3.2 Oracle 用戶(hù)的天然默契達(dá)夢(mèng)默認(rèn)對(duì)齊 Oracle跟 MySQL 用戶(hù)相反有 Oracle 開(kāi)發(fā)經(jīng)驗(yàn)的人到達(dá)夢(mèng)幾乎是無(wú)縫切換。達(dá)夢(mèng)的默認(rèn)大小寫(xiě)敏感特性、標(biāo)識(shí)符轉(zhuǎn)大寫(xiě)規(guī)則、雙引號(hào)保留原樣規(guī)則跟 Oracle 幾乎一致。這也是達(dá)夢(mèng)在設(shè)計(jì)上的一個(gè)明確取向——兼容 Oracle 語(yǔ)法體系。達(dá)夢(mèng)的很多行為都是以 Oracle 為藍(lán)本的包括內(nèi)置函數(shù)NVL、DECODE、SYSDATE、PL/SQL 塊語(yǔ)法、包機(jī)制等。如果你是從 Oracle 遷移過(guò)來(lái)幾乎不需要在大小寫(xiě)問(wèn)題上做額外適配只要遵循原來(lái)的開(kāi)發(fā)規(guī)范建表時(shí)用大寫(xiě)、不帶雙引號(hào)SQL 里的關(guān)鍵字大寫(xiě)標(biāo)識(shí)符保持統(tǒng)一基本不會(huì)踩坑。但要注意Oracle 里有一個(gè)隱含細(xì)節(jié)某些工具比如舊版 PL/SQL Developer在執(zhí)行 SQL 時(shí)可能自動(dòng)加雙引號(hào)或自動(dòng)轉(zhuǎn)換大小寫(xiě)造成在 Oracle 里能跑到達(dá)夢(mèng)卻不能跑的假象。實(shí)質(zhì)不是達(dá)夢(mèng)的問(wèn)題而是工具行為不一致。建議在達(dá)夢(mèng)相關(guān)的開(kāi)發(fā)環(huán)境里統(tǒng)一使用達(dá)夢(mèng)自帶的 SQL 交互工具或通用客戶(hù)端同時(shí)打開(kāi) SQL 日志觀察實(shí)際執(zhí)行的語(yǔ)句被轉(zhuǎn)換成了什么形式。3.3 初始化參數(shù)大小寫(xiě)敏感配置必須在建庫(kù)前定達(dá)夢(mèng)的大小寫(xiě)敏感配置不是一個(gè)可以在實(shí)例運(yùn)行后隨便改的運(yùn)行參數(shù)它是在初始化實(shí)例即執(zhí)行 dminit 創(chuàng)建數(shù)據(jù)庫(kù)時(shí)寫(xiě)入控制文件的。所以你在部署達(dá)夢(mèng)環(huán)境的第一步就要想清楚這個(gè)庫(kù)要不要區(qū)分大小寫(xiě)在達(dá)夢(mèng)中控制大小寫(xiě)敏感相關(guān)的參數(shù)主要有兩個(gè)維度一個(gè)是CASE_SENSITIVE它是初始化參數(shù)取值為 1 或 01 表示敏感0 表示不敏感另一個(gè)是字符集和長(zhǎng)度單位配置比如LENGTH_IN_CHAR、CHARSET等這些參數(shù)會(huì)和大小寫(xiě)敏感配置一起固化到數(shù)據(jù)庫(kù)的初始化信息中。如果你在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)設(shè)置CASE_SENSITIVE0那么帶不帶雙引號(hào)的標(biāo)識(shí)符都會(huì)被統(tǒng)一處理成大寫(xiě)不敏感時(shí)雙引號(hào)不再保留大小寫(xiě)差異同時(shí)字符串比較也不再區(qū)分大小寫(xiě)。這意味著后面建的user_info和USER_INFO會(huì)被當(dāng)作同一個(gè)對(duì)象查abc和查ABC結(jié)果也是一樣的。聽(tīng)起來(lái)很省心是不是但我不建議你為了省事直接把所有庫(kù)都初始化成不敏感模式理由有三點(diǎn)第一不敏感模式下應(yīng)用層的一些大小寫(xiě)差異問(wèn)題會(huì)被數(shù)據(jù)庫(kù)掩蓋。比如程序里有個(gè)邏輯要判斷驗(yàn)證碼是否區(qū)分大小寫(xiě)如果在數(shù)據(jù)庫(kù)層就不敏感驗(yàn)證碼判斷就會(huì)失效這是嚴(yán)重的業(yè)務(wù)安全隱患。第二達(dá)夢(mèng)官方文檔和社區(qū)里的方案經(jīng)驗(yàn)大多數(shù)基于敏感模式展開(kāi)如果你搞成不敏感某些場(chǎng)景下的 SQL 調(diào)優(yōu)、函數(shù)行為、遷移工具表現(xiàn)可能跟文檔出現(xiàn)出入排查問(wèn)題時(shí)參考資料會(huì)少很多。第三以后如果要把數(shù)據(jù)遷移到 Oracle 或其他敏感庫(kù)存儲(chǔ)過(guò)程中用到的字符串比較邏輯會(huì)發(fā)生語(yǔ)義變化容易在遷移后產(chǎn)生數(shù)據(jù)校驗(yàn)不一致的問(wèn)題。所以我的建議是除非業(yè)務(wù)有明確需求否則默認(rèn)用敏感模式CASE_SENSITIVE1跟 Oracle 行為對(duì)齊未來(lái)無(wú)論遷移還是外部協(xié)同都最穩(wěn)妥。3.4 已經(jīng)創(chuàng)建好的實(shí)例如何確認(rèn)大小寫(xiě)敏感狀態(tài)如果你接手的是一套已經(jīng)跑起來(lái)的達(dá)夢(mèng)環(huán)境不清楚當(dāng)初建庫(kù)時(shí)到底選了哪種模式有兩個(gè)方法快速確認(rèn)。方法一是查詢(xún)視圖達(dá)夢(mèng)中可以通過(guò)系統(tǒng)視圖或動(dòng)態(tài)視圖獲取初始化參數(shù)。常見(jiàn)的查詢(xún)思路是查實(shí)例的建庫(kù)參數(shù)不同版本視圖名稱(chēng)略有差異通?？梢杂妙?lèi)似下面的方式查看SELECT N_PARAM_NAME, PARA_VALUE FROM V$DM_INI WHERE N_PARAM_NAME IN (CASE_SENSITIVE);如果不能直接查到 CASE_SENSITIVE可以查帶實(shí)例信息的視圖比如在一些版本中用SELECT CASE_SENSITIVE FROM V$DM_INI;或者通過(guò)初始化參數(shù)視圖查看SELECT * FROM SYS.SYSDM_INI;方法二是直接用 SQL 實(shí)測(cè)。建一張測(cè)試表CREATE TABLE test_case_table (id INT); -- 然后分別執(zhí)行下面兩條 SELECT * FROM TEST_CASE_TABLE; -- 不帶引號(hào) SELECT * FROM test_case_table; -- 帶引號(hào)小寫(xiě)如果第一條成功、第二條報(bào)表不存在說(shuō)明是敏感模式如果兩條都成功說(shuō)明是不敏感模式。這個(gè)方法最直觀不需要查任何源碼適合快速判斷。注意上面查詢(xún)中 V$DM_INI 的具體字段名在不同版本間可能略有出入。如果查出來(lái)是空集建議先用TABNAME LIKE V$DM_INI的方式確認(rèn)視圖存在性再根據(jù)實(shí)際字段結(jié)構(gòu)調(diào)整查詢(xún)語(yǔ)句。4. 實(shí)操驗(yàn)證與檢測(cè)一次跑通的大小寫(xiě)敏感實(shí)驗(yàn)4.1 實(shí)驗(yàn)環(huán)境與前置準(zhǔn)備為了讓你能夠照著復(fù)現(xiàn)我這里給出一個(gè)最小化的實(shí)驗(yàn)方案。環(huán)境方面需要一臺(tái)安裝了達(dá)夢(mèng)數(shù)據(jù)庫(kù)的服務(wù)器版本建議選擇 8.x 及以上當(dāng)前主流版本都可以運(yùn)行下面的語(yǔ)句。沒(méi)有達(dá)夢(mèng)環(huán)境的朋友可以使用達(dá)夢(mèng)官方提供的 Docker 鏡像快速起一個(gè)測(cè)試實(shí)例初始化參數(shù)采用默認(rèn)設(shè)置默認(rèn)即為大小寫(xiě)敏感。前置準(zhǔn)備只有一句話(huà)確保你能用 SYSDBA 用戶(hù)或其他具備建表權(quán)限的賬號(hào)登錄數(shù)據(jù)庫(kù)。登錄后第一步先確認(rèn)當(dāng)前實(shí)例的行為模式。執(zhí)行SELECT CASE_SENSITIVE FROM V$DM_INI;如果返回值為 1說(shuō)明當(dāng)前實(shí)例是大小寫(xiě)敏感模式如果返回值為 0則是不敏感模式。返回結(jié)果會(huì)告訴你接下里的實(shí)驗(yàn)預(yù)期是什么。4.2 實(shí)驗(yàn)一標(biāo)識(shí)符大小寫(xiě)解析驗(yàn)證這個(gè)實(shí)驗(yàn)用來(lái)驗(yàn)證不帶引號(hào)和帶引號(hào)的建表差異。步驟 1創(chuàng)建一張不帶引號(hào)的表CREATE TABLE SENSITIVE_TEST ( ID INT PRIMARY KEY, NAME VARCHAR(30) );步驟 2嘗試用全大寫(xiě)查詢(xún)、全小寫(xiě)查詢(xún)和引號(hào)查詢(xún)-- 全大寫(xiě)預(yù)期成功 SELECT COUNT(*) FROM SENSITIVE_TEST; -- 全小寫(xiě)預(yù)期在敏感模式下也成功系統(tǒng)自動(dòng)轉(zhuǎn)為大寫(xiě) SELECT COUNT(*) FROM sensitive_test; -- 帶雙引號(hào)小寫(xiě)預(yù)期報(bào)表或視圖不存在 SELECT COUNT(*) FROM sensitive_test;實(shí)驗(yàn)結(jié)果說(shuō)明前兩條都能跑通因?yàn)橄到y(tǒng)把不帶引號(hào)的標(biāo)識(shí)符統(tǒng)一轉(zhuǎn)成了大寫(xiě)。第三條報(bào)錯(cuò)因?yàn)殡p引號(hào)讓系統(tǒng)按原始小寫(xiě)名稱(chēng)去尋找而實(shí)際存在的表是大寫(xiě)。步驟 3再創(chuàng)建一張帶雙引號(hào)的表CREATE TABLE mixed_case_table ( ID INT );然后嘗試訪(fǎng)問(wèn)-- 帶引號(hào)訪(fǎng)問(wèn)預(yù)期成功 SELECT COUNT(*) FROM mixed_case_table; -- 不帶引號(hào)訪(fǎng)問(wèn)預(yù)期失敗 SELECT COUNT(*) FROM mixed_case_table;第二次查詢(xún)失敗的原因是不帶引號(hào)時(shí)系統(tǒng)將mixed_case_table轉(zhuǎn)成了大寫(xiě)MIXED_CASE_TABLE去找但實(shí)際表名是小寫(xiě)的mixed_case_table所以找不到。實(shí)驗(yàn)結(jié)論你建表時(shí)寫(xiě)了什么不重要重要的是你有沒(méi)有加雙引號(hào)。沒(méi)加雙引號(hào)系統(tǒng)全轉(zhuǎn)大寫(xiě)加了雙引號(hào)系統(tǒng)原樣保留。4.3 實(shí)驗(yàn)二字符串?dāng)?shù)據(jù)比較驗(yàn)證這個(gè)實(shí)驗(yàn)需要一張有數(shù)據(jù)的表。沿用SENSITIVE_TEST插入幾條不同大小寫(xiě)的數(shù)據(jù)INSERT INTO SENSITIVE_TEST VALUES (1, Admin); INSERT INTO SENSITIVE_TEST VALUES (2, admin); INSERT INTO SENSITIVE_TEST VALUES (3, ADMIN); COMMIT;分別執(zhí)行SELECT * FROM SENSITIVE_TEST WHERE NAME admin; -- 預(yù)期只返回 ID2 那一行因?yàn)槟J(rèn)敏感模式下 admin 與 Admin、ADMIN 不相等 SELECT * FROM SENSITIVE_TEST WHERE UPPER(NAME) ADMIN; -- 預(yù)期返回全部三行因?yàn)?UPPER 把所有名字都轉(zhuǎn)成大寫(xiě)再比較 SELECT * FROM SENSITIVE_TEST WHERE LOWER(NAME) admin; -- 預(yù)期返回全部三行同理這個(gè)實(shí)驗(yàn)直觀展示了默認(rèn)模式的字符串比較行為實(shí)際業(yè)務(wù)中如果用戶(hù)表里的賬號(hào)存在大小寫(xiě)混用而你用精確等值去匹配結(jié)果會(huì)非常詭異。比如登錄時(shí)用戶(hù)輸入的賬號(hào)是admin數(shù)據(jù)庫(kù)里存的注冊(cè)賬號(hào)是Admin默認(rèn)模式下就永遠(yuǎn)匹配不上。4.4 實(shí)驗(yàn)三ORDER BY 排序規(guī)則驗(yàn)證繼續(xù)用剛才的數(shù)據(jù)SELECT NAME FROM SENSITIVE_TEST ORDER BY NAME;二進(jìn)制排序下結(jié)果大概率是ADMIN、Admin、admin因?yàn)榇髮?xiě)字母的 ASCII 碼值小于小寫(xiě)字母。如果業(yè)務(wù)要求排序時(shí)忽略大小寫(xiě)可以寫(xiě)成SELECT NAME FROM SENSITIVE_TEST ORDER BY LOWER(NAME);這樣三個(gè)值都會(huì)被轉(zhuǎn)成admin后參與排序并列順序取決于數(shù)據(jù)庫(kù)內(nèi)部的二次排序規(guī)則但至少不會(huì)出現(xiàn)大寫(xiě)全部排在小寫(xiě)前面的現(xiàn)象。這里要注意對(duì)列使用函數(shù)會(huì)導(dǎo)致該列上的索引失效如果排序字段數(shù)據(jù)量大需要考慮函數(shù)索引或初始化時(shí)優(yōu)化排序規(guī)則。5. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄5.1 建表后查詢(xún)報(bào)表或視圖不存在這個(gè)問(wèn)題的本質(zhì)幾乎永遠(yuǎn)是標(biāo)識(shí)符大小寫(xiě)不匹配。排查思路是確認(rèn)你建表時(shí)有沒(méi)有用雙引號(hào)以及你用查詢(xún)語(yǔ)句時(shí)表名的實(shí)際解析形態(tài)。最常見(jiàn)的情況是建表腳本里某些對(duì)象名加了雙引號(hào)而查詢(xún)腳本里沒(méi)加或者反過(guò)來(lái)。比如某同事建的視圖名稱(chēng)是v_UserInfo查詢(xún)時(shí)寫(xiě)SELECT * FROM v_UserInfo系統(tǒng)轉(zhuǎn)大寫(xiě)后變成V_USERINFO和真實(shí)的小寫(xiě)視圖名對(duì)不上于是報(bào)不存在。解決辦法也很簡(jiǎn)單優(yōu)先去掉所有雙引號(hào)讓系統(tǒng)統(tǒng)一轉(zhuǎn)大寫(xiě)如果由于歷史原因已經(jīng)產(chǎn)生了大量帶引號(hào)的對(duì)象名那就只能改 SQL 里的訪(fǎng)問(wèn)方式把查詢(xún)語(yǔ)句里的對(duì)象名也加上同樣的雙引號(hào)。5.2 存儲(chǔ)過(guò)程編譯成功但執(zhí)行報(bào)錯(cuò)有些存儲(chǔ)過(guò)程在創(chuàng)建時(shí)沒(méi)問(wèn)題一調(diào)用就報(bào)無(wú)效對(duì)象或表或視圖不存在。這種問(wèn)題往往埋藏在過(guò)程體內(nèi)的動(dòng)態(tài) SQL 中。比如直接在存儲(chǔ)過(guò)程里拼字符串EXECUTE IMMEDIATE SELECT * FROM || v_table_name;如果變量 v_table_name 存的是 UserInfo動(dòng)態(tài)拼接后執(zhí)行時(shí)不帶引號(hào)就被轉(zhuǎn)成USERINFO和真實(shí)表名對(duì)不上。而存儲(chǔ)過(guò)程在編譯階段不會(huì)解析動(dòng)態(tài) SQL所以創(chuàng)建時(shí)一切正常運(yùn)行到這一段才報(bào)錯(cuò)。這類(lèi)問(wèn)題的排查方法是在存儲(chǔ)過(guò)程里臨時(shí)輸出拼接后的 SQL 文本仔細(xì)觀察表名是否被轉(zhuǎn)成了大寫(xiě)或者干脆統(tǒng)一規(guī)范建表時(shí)全部大寫(xiě)命名動(dòng)態(tài) SQL 拼表名時(shí)也傳大寫(xiě)。5.3 從 MySQL 遷到達(dá)夢(mèng)后應(yīng)用登錄失效前文提過(guò)登錄模塊是大小寫(xiě)敏感重災(zāi)區(qū)。遷移后用戶(hù)反饋密碼正確但是登錄失敗十有八九是字符串比較導(dǎo)致的而非密碼加密算法出了問(wèn)題。舉個(gè)例子原系統(tǒng)在 MySQL 里查詢(xún)用戶(hù)時(shí)的邏輯是WHERE user_name ? AND pwd_hash ?MySQL 的排序規(guī)則如果是utf8_general_ci比較時(shí)不區(qū)分大小寫(xiě)用戶(hù)輸入ADMIN和庫(kù)里的admin也能匹配上。到了達(dá)夢(mèng)默認(rèn)敏感輸入ADMIN就匹配不上admin于是登錄失敗。遇到這類(lèi)問(wèn)題不要急著改代碼里的查詢(xún)邏輯。先定位比較字段是否需要大小寫(xiě)不敏感。如果業(yè)務(wù)上確實(shí)不區(qū)分推薦方案是查詢(xún)時(shí)統(tǒng)一調(diào)用UPPER()函數(shù)處理兩側(cè)入?yún)ELECT * FROM t_user WHERE UPPER(user_name) UPPER(Admin);這種方式不影響存儲(chǔ)層數(shù)據(jù)只改變比較行為。但要留意加了函數(shù)后該字段上的索引可能失效如果這個(gè)查詢(xún)是高頻接口建議把賬號(hào)列統(tǒng)一轉(zhuǎn)成大寫(xiě)存儲(chǔ)或者建立一個(gè)函數(shù)索引。另外一個(gè)思路是在導(dǎo)入數(shù)據(jù)時(shí)統(tǒng)一轉(zhuǎn)換賬號(hào)和密碼字段的大小寫(xiě)格式比如賬號(hào)一律存小寫(xiě)、密碼哈希不留大小寫(xiě)語(yǔ)義從源頭規(guī)避問(wèn)題。5.4 DM 管理工具里的顯示與 SQL 里的實(shí)際大小寫(xiě)不一致偶爾會(huì)遇到用 DM 管理工具查看表顯示的是UserInfo可 SQL 查詢(xún)里寫(xiě)UserInfo卻報(bào)不存在。這個(gè)現(xiàn)象其實(shí)不矛盾。管理工具在展示對(duì)象時(shí)可能按創(chuàng)建語(yǔ)句的原始文本顯示而 SQL 解析時(shí)默認(rèn)轉(zhuǎn)大寫(xiě)。如果創(chuàng)建時(shí)用了雙引號(hào)保留了大小寫(xiě)展示就是小寫(xiě)或混寫(xiě)如果查詢(xún)時(shí)不帶雙引號(hào)就會(huì)因?yàn)檗D(zhuǎn)成大寫(xiě)而找不到。為了避免這種混亂我個(gè)人的習(xí)慣是用管理工具查看對(duì)象的創(chuàng)建語(yǔ)句或者DDL 文本確認(rèn)真實(shí)名稱(chēng)是否帶引號(hào)所有手動(dòng)書(shū)寫(xiě)的 SQL 統(tǒng)一按大寫(xiě)標(biāo)識(shí)符書(shū)寫(xiě)且不帶雙引號(hào)。5.5 常見(jiàn)問(wèn)題排查速查表問(wèn)題現(xiàn)象可能原因排查方法解決方案表或視圖不存在標(biāo)識(shí)符大小寫(xiě)不匹配查看真實(shí)表名是否含引號(hào)保留的大小寫(xiě)去掉雙引號(hào)統(tǒng)一大寫(xiě)或訪(fǎng)問(wèn)時(shí)加引號(hào)登錄校驗(yàn)失敗字符串比較區(qū)分大小寫(xiě)比對(duì)輸入值和庫(kù)存值的 UPPER 結(jié)果SQL 中顯式使用 UPPER 函數(shù)遷移腳本報(bào)對(duì)象重復(fù)表名轉(zhuǎn)大寫(xiě)后重名檢查建表腳本中是否存在差異大小寫(xiě)表名合并同名對(duì)象統(tǒng)一命名規(guī)范存儲(chǔ)過(guò)程動(dòng)態(tài) SQL 報(bào)錯(cuò)運(yùn)行時(shí)表名被轉(zhuǎn)換打印拼接結(jié)果確認(rèn)實(shí)際解析名稱(chēng)變量傳值使用大寫(xiě)或雙引號(hào)包裹排序結(jié)果出乎意料二進(jìn)制排序規(guī)則查看 ORDER BY 原始結(jié)果使用 LOWER/UPPER 包裹排序字段6. 從開(kāi)發(fā)規(guī)范層面根治大小寫(xiě)敏感問(wèn)題大小寫(xiě)敏感真正折磨人的地方不在某一個(gè)單獨(dú)的坑而是它散布在 SQL 編寫(xiě)的各個(gè)角落。這里分享幾個(gè)我實(shí)際踩過(guò)坑之后沉淀下來(lái)的開(kāi)發(fā)規(guī)范照著執(zhí)行基本能把坑填平。第一個(gè)規(guī)范是建表語(yǔ)句里的對(duì)象名一律不加雙引號(hào)同時(shí)使用大寫(xiě)命名。別依賴(lài)編輯器幫你轉(zhuǎn)大小寫(xiě)直接用大寫(xiě)字母寫(xiě)表名、字段名、存儲(chǔ)過(guò)程名。比如建表就寫(xiě)CREATE TABLE USER_INFO而不是CREATE TABLE user_info。后者雖然也能跑但在團(tuán)隊(duì)協(xié)作時(shí)不同成員編碼習(xí)慣不同代碼里一會(huì)大寫(xiě)一會(huì)小寫(xiě)排查成本就上來(lái)了。第二個(gè)規(guī)范是 SQL 關(guān)鍵字使用大寫(xiě)。這個(gè)不是為了裝樣子而是為了保證團(tuán)隊(duì) SQL 風(fēng)格統(tǒng)一降低誤讀風(fēng)險(xiǎn)。特別是動(dòng)態(tài) SQL 拼接時(shí)統(tǒng)一大寫(xiě)能夠減少拼寫(xiě)錯(cuò)誤的概率。第三個(gè)規(guī)范是 Java/Mapper 層 XML 里不要使用雙引號(hào)包表名。有些開(kāi)發(fā)喜歡把 XML 里的 SQL 寫(xiě)成SELECT * FROM user_info WHERE ...這能跑通但一旦別人接手不知道這里用了雙引號(hào)直接在數(shù)據(jù)庫(kù)客戶(hù)端里執(zhí)行SELECT * FROM user_info就會(huì)報(bào)錯(cuò)還以為代碼有問(wèn)題。為了少制造這種困惑對(duì)象引用上要克制使用雙引號(hào)。第四個(gè)規(guī)范是字符串比較統(tǒng)一處理。涉及用戶(hù)輸入與數(shù)據(jù)庫(kù)值比較的場(chǎng)景業(yè)務(wù)沒(méi)特殊要求時(shí)統(tǒng)一調(diào)用 UPPER 或 LOWER 函數(shù)。比如手機(jī)號(hào)、郵箱、用戶(hù)名這類(lèi)通常不區(qū)分大小寫(xiě)的字段查詢(xún)時(shí)兩側(cè)都套上函數(shù)保持語(yǔ)義一致。第五個(gè)規(guī)范是測(cè)試用例里必須覆蓋大小寫(xiě)邊界。每張涉及登錄、權(quán)限、唯一性校驗(yàn)的表準(zhǔn)備大小寫(xiě)不同的輸入數(shù)據(jù)跑一遍查詢(xún)和校驗(yàn)邏輯確認(rèn)行為和業(yè)務(wù)預(yù)期一致。這些規(guī)范看著瑣碎但確實(shí)能救命。我見(jiàn)過(guò)太多項(xiàng)目上線(xiàn)后因?yàn)榇笮?xiě)問(wèn)題出事故事后一查都是最基礎(chǔ)的問(wèn)題早一點(diǎn)定好規(guī)范后面能省大量排查時(shí)間。7. 進(jìn)階思考大小寫(xiě)敏感與性能、遷移的關(guān)聯(lián)7.1 函數(shù)包裹導(dǎo)致索引失效的取舍前面提到過(guò)為了讓字符串比較不敏感常常會(huì)在字段上套UPPER()函數(shù)。這個(gè)做法簡(jiǎn)單直接但代價(jià)是字段上的普通索引可能無(wú)法使用查詢(xún)?nèi)頀呙栊阅軙?huì)明顯下滑。如果遇到登錄接口這種高頻低數(shù)據(jù)量的場(chǎng)景全表掃描問(wèn)題不大。但如果是千萬(wàn)級(jí)數(shù)據(jù)量的流水表按編碼字段查詢(xún)且業(yè)務(wù)需要忽略大小寫(xiě)就要考慮建立函數(shù)索引CREATE INDEX IDX_USER_NAME_UPPER ON SENSITIVE_TEST (UPPER(NAME));建了函數(shù)索引之后WHERE UPPER(NAME) ADMIN才能走索引掃描。這個(gè)點(diǎn)很容易被忽略因?yàn)槠胀ㄋ饕秃瘮?shù)索引的成本完全不同純粹靠數(shù)據(jù)量小掩蓋問(wèn)題的話(huà)上線(xiàn)之后遲早會(huì)暴露。7.2 遷移工具與大小寫(xiě)敏感的聯(lián)動(dòng)使用達(dá)夢(mèng)的遷移工具從 MySQL 導(dǎo)數(shù)據(jù)時(shí)工具通常會(huì)自動(dòng)根據(jù)源端和目標(biāo)端的參數(shù)做名稱(chēng)轉(zhuǎn)換。但工具不是萬(wàn)能的它不會(huì)替你想清楚業(yè)務(wù)語(yǔ)義。比如源端 MySQL 的表名是小寫(xiě)工具遷移到達(dá)夢(mèng)后默認(rèn)轉(zhuǎn)大寫(xiě)這個(gè)過(guò)程一般沒(méi)問(wèn)題但如果你后續(xù)在達(dá)夢(mèng)上執(zhí)行源端導(dǎo)出的 SQL 備份腳本腳本里的小寫(xiě)表名可能因?yàn)檎也坏綄?duì)象而報(bào)錯(cuò)。我建議的做法是遷移完成后寫(xiě)一個(gè)巡檢腳本把數(shù)據(jù)庫(kù)中所有用戶(hù)表和索引的最終名稱(chēng)查出來(lái)與原系統(tǒng)做一輪比對(duì)專(zhuān)門(mén)核對(duì)大小寫(xiě)是否有差異。不要只抽樣要全量比對(duì)這類(lèi)問(wèn)題隱蔽性極強(qiáng)抽樣大概率發(fā)現(xiàn)不了。7.3 是否要把整庫(kù)改成不敏感模式有些團(tuán)隊(duì)被大小寫(xiě)問(wèn)題折騰幾輪后會(huì)想直接把新庫(kù)初始化成不敏感模式。這個(gè)思路短期看省事長(zhǎng)期看隱患不小。不敏感模式下你確實(shí)不會(huì)再遇到表名大小寫(xiě)報(bào)錯(cuò)的問(wèn)題但同時(shí)也失去了一種約束力——數(shù)據(jù)庫(kù)不再倒逼開(kāi)發(fā)團(tuán)隊(duì)維持命名規(guī)范和比較規(guī)范。等到未來(lái)某一天業(yè)務(wù)要求對(duì)密碼做大小寫(xiě)敏感校驗(yàn)或者要與外部系統(tǒng)做數(shù)據(jù)對(duì)接而外部系統(tǒng)區(qū)分大小寫(xiě)你會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)層面的不敏感反而成了阻礙。這里的建議是敏感模式的成本集中在前期開(kāi)發(fā)和建設(shè)規(guī)范不敏感模式的成本分散在后續(xù)業(yè)務(wù)演進(jìn)和數(shù)據(jù)交互。從控制長(zhǎng)期風(fēng)險(xiǎn)的角度我更傾向敏感模式加規(guī)范化的組合拳。如果你確實(shí)有合規(guī)或特殊業(yè)務(wù)要求必須用不敏感模式建議在項(xiàng)目文檔里顯式記錄這個(gè)決策至少讓后來(lái)接手的同事知道這個(gè)庫(kù)和默認(rèn)行為不一樣。8. 實(shí)際操作中我的體會(huì)接觸達(dá)夢(mèng)這些年我最大的感受是大小寫(xiě)敏感本身不算難難的是它帶來(lái)的連鎖問(wèn)題太分散。從一個(gè)表名查不到的小報(bào)錯(cuò)能牽扯出動(dòng)態(tài) SQL、字符串比較、索引失效、遷移腳本兼容性、團(tuán)隊(duì)編碼規(guī)范一系列問(wèn)題。正因?yàn)檫@樣我建議所有剛接手達(dá)夢(mèng)項(xiàng)目的團(tuán)隊(duì)開(kāi)工第一天就把大小寫(xiě)相關(guān)的事項(xiàng)寫(xiě)進(jìn)團(tuán)隊(duì)公約里實(shí)例初始化參數(shù)怎么定、建表命名風(fēng)格怎么統(tǒng)一、查詢(xún)語(yǔ)句里遇到字符串比較怎么處理、動(dòng)態(tài) SQL 拼接要注意什么。別覺(jué)得這是小題大做等線(xiàn)上出了事故再補(bǔ)規(guī)范代價(jià)不是一個(gè)量級(jí)的。還有一點(diǎn)小技巧可以分享如果你要調(diào)試一段在達(dá)夢(mèng)上報(bào)錯(cuò)的 SQL先在數(shù)據(jù)庫(kù)客戶(hù)端里不帶引號(hào)執(zhí)行一遍看系統(tǒng)報(bào)不報(bào)表或視圖不存在如果報(bào)就把真實(shí)表名查出來(lái)對(duì)照一下是不是大小寫(xiě)的問(wèn)題。很多看似復(fù)雜的排查最后一步就解決在這上面。最后再給新接觸達(dá)夢(mèng)的朋友一個(gè)建議先建一張測(cè)試表把大小寫(xiě)相關(guān)的幾個(gè)實(shí)驗(yàn)自己跑一遍花不了十分鐘但對(duì)后續(xù)理解達(dá)夢(mèng)的各種報(bào)錯(cuò)信息非常有幫助。數(shù)據(jù)和環(huán)境都有了之后很多問(wèn)題一眼就能看穿。 達(dá)夢(mèng)數(shù)據(jù)庫(kù)大小寫(xiě)敏感機(jī)制詳解：從原理到實(shí)踐避坑
1. 項(xiàng)目概述達(dá)夢(mèng)大小寫(xiě)敏感到底是個(gè)什么事做數(shù)據(jù)庫(kù)開(kāi)發(fā)或者運(yùn)維的朋友只要是第一次接觸達(dá)夢(mèng)數(shù)據(jù)庫(kù)十有八九會(huì)遇到一個(gè)讓人摸不著頭腦的現(xiàn)象明明建表時(shí)寫(xiě)的是 USER_INFO去查詢(xún)的時(shí)候?qū)?user_info 卻報(bào)表或視圖不存在。如果之前一直用的是 MySQL碰上這種情況的第一反應(yīng)往往是這什么鬼數(shù)據(jù)庫(kù)連大小寫(xiě)都不認(rèn)。先給結(jié)論達(dá)夢(mèng)數(shù)據(jù)庫(kù)默認(rèn)是大小寫(xiě)敏感的這一點(diǎn)和 Oracle 的對(duì)齊策略一致。也就是說(shuō)你在達(dá)夢(mèng)里建表、建字段、寫(xiě)存儲(chǔ)過(guò)程、調(diào)用函數(shù)凡是涉及標(biāo)識(shí)符的解析系統(tǒng)都會(huì)嚴(yán)格區(qū)分 A 和 a。而很多從 MySQL 轉(zhuǎn)過(guò)來(lái)的人思維慣性還停留在表名隨便寫(xiě)、字段隨便引的寬松模式一上來(lái)就踩坑。這個(gè)特性不是某個(gè)版本臨時(shí)加進(jìn)去的而是達(dá)夢(mèng)從架構(gòu)設(shè)計(jì)層面就定下來(lái)的默認(rèn)行為。它直接影響建表語(yǔ)句的書(shū)寫(xiě)規(guī)范、遷移腳本的兼容性、應(yīng)用代碼里的 SQL 拼接方式甚至影響你初始化實(shí)例時(shí)參數(shù)怎么配。所以不管你是 DBA、后端開(kāi)發(fā)、數(shù)據(jù)遷移工程師還是剛接觸達(dá)夢(mèng)的初學(xué)者第一課都應(yīng)該把大小寫(xiě)敏感這四個(gè)字徹底搞明白。這篇文章就圍繞達(dá)夢(mèng)數(shù)據(jù)庫(kù)的大小寫(xiě)敏感機(jī)制把原理、驗(yàn)證方法、實(shí)操步驟、踩坑案例全都拆開(kāi)講一遍。文章里沒(méi)有廢話(huà)全部是我在實(shí)際項(xiàng)目里驗(yàn)證過(guò)的東西照著操作就能復(fù)現(xiàn)結(jié)論。2. 核心機(jī)制拆解達(dá)夢(mèng)大小寫(xiě)敏感的工作原理2.1 標(biāo)識(shí)符解析引號(hào)才是真正的開(kāi)關(guān)達(dá)夢(mèng)數(shù)據(jù)庫(kù)對(duì)標(biāo)識(shí)符表名、列名、視圖名、索引名等的大小寫(xiě)處理核心規(guī)則可以概括成一句話(huà)默認(rèn)情況下不帶雙引號(hào)的標(biāo)識(shí)符會(huì)被系統(tǒng)統(tǒng)一轉(zhuǎn)換為大寫(xiě)字母存儲(chǔ)帶雙引號(hào)的標(biāo)識(shí)符則嚴(yán)格保留原始大小寫(xiě)。舉個(gè)例子執(zhí)行建表語(yǔ)句CREATE TABLE user_info ( id INT, name VARCHAR(20) );達(dá)夢(mèng)實(shí)際存儲(chǔ)的表名是USER_INFO而不是user_info。后續(xù)查詢(xún)時(shí)如果你寫(xiě)SELECT * FROM user_info系統(tǒng)會(huì)把user_info轉(zhuǎn)成USER_INFO再去找所以能查到。但你如果寫(xiě)SELECT * FROM user_info系統(tǒng)會(huì)按雙引號(hào)內(nèi)的原始內(nèi)容去找即小寫(xiě)的user_info結(jié)果就會(huì)報(bào)表不存在。反過(guò)來(lái)如果你建表時(shí)用了雙引號(hào)CREATE TABLE user_info ( id INT, name VARCHAR(20) );那么這張表的名字就是小寫(xiě)的user_info不帶引號(hào)查詢(xún)SELECT * FROM USER_INFO反而會(huì)失敗因?yàn)橄到y(tǒng)把 USER_INFO 當(dāng)成名稱(chēng)去找而真實(shí)表名是小寫(xiě)。這個(gè)機(jī)制和 Oracle 完全一致。換句通俗的話(huà)講雙引號(hào)就是大小寫(xiě)敏感的開(kāi)關(guān)加了雙引號(hào)字母就原樣保留不加雙引號(hào)全部自動(dòng)轉(zhuǎn)大寫(xiě)。這也是為什么有些老 Oracle 開(kāi)發(fā)習(xí)慣把所有 SQL 關(guān)鍵字和標(biāo)識(shí)符都寫(xiě)大寫(xiě)因?yàn)樵谶_(dá)夢(mèng)里不帶引號(hào)的標(biāo)識(shí)符最終都會(huì)被轉(zhuǎn)成大寫(xiě)你寫(xiě)小寫(xiě)只是讓系統(tǒng)多了一步轉(zhuǎn)換過(guò)程最終解析結(jié)果是一樣的。2.2 字符串比較VARCHAR 與 CHAR 的判定邏輯很多人在沒(méi)弄清楚之前會(huì)下意識(shí)認(rèn)為大小寫(xiě)敏感就是所有字符串比較都敏感這是錯(cuò)誤的理解需要拆分到兩個(gè)層面看。在達(dá)夢(mèng)數(shù)據(jù)庫(kù)中大小寫(xiě)敏感特性主要體現(xiàn)在兩個(gè)地方第一是上面講的標(biāo)識(shí)符解析第二是字符串比較的規(guī)則。但這里有個(gè)關(guān)鍵細(xì)節(jié)——標(biāo)識(shí)符的大小寫(xiě)敏感性跟字符串?dāng)?shù)據(jù)的大小寫(xiě)敏感性參數(shù)是兩套東西不能混為一談。達(dá)夢(mèng)的字符串敏感度實(shí)際上有三個(gè)枚舉值SENSITIVE敏感、INSENSITIVE不敏感、INVISITIVE忽略實(shí)際上就是 cast 時(shí)不感知比較時(shí)按敏感處理。這個(gè)參數(shù)叫LENGTH_IN_CHAR之外的一個(gè)配置在部分版本的文檔里也被稱(chēng)為大小寫(xiě)敏感配置項(xiàng)它作用在像WHERE name ABC這種條件判斷上。具體來(lái)說(shuō)如果實(shí)例初始化時(shí)設(shè)置了字符串大小寫(xiě)不敏感那么abc ABC為真如果是敏感模式abc ABC為假必須通過(guò)UPPER(abc) ABC方式繞行。這個(gè)配置項(xiàng)在初始化實(shí)例時(shí)通過(guò)參數(shù)控制一旦實(shí)例創(chuàng)建完成后期不能直接動(dòng)態(tài)修改只能通過(guò)重建實(shí)例或者專(zhuān)門(mén)的工具處理。舉一個(gè)實(shí)際場(chǎng)景達(dá)夢(mèng)數(shù)據(jù)庫(kù)中如果默認(rèn)大小寫(xiě)敏感那么查詢(xún)WHERE user_name admin和WHERE user_name ADMIN返回的結(jié)果可能完全不同。如果你在開(kāi)發(fā)時(shí)沒(méi)注意登錄模塊就很容易出問(wèn)題——用戶(hù)表里存的是 Admin程序里傳參是 admin結(jié)果就是校驗(yàn)永遠(yuǎn)對(duì)不上。2.3 三個(gè)層面看大小寫(xiě)命名、數(shù)據(jù)、排序要把大小寫(xiě)敏感徹底吃透我建議你把這個(gè)問(wèn)題想成三個(gè)獨(dú)立維度第一維度是命名層即表名、字段名等標(biāo)識(shí)符的大小寫(xiě)解析規(guī)則。默認(rèn)轉(zhuǎn)大寫(xiě)雙引號(hào)保留原樣。這是建表、寫(xiě) SQL 時(shí)必須時(shí)刻記住的。第二維度是數(shù)據(jù)層即存儲(chǔ)在表里的字符串字面值在比較時(shí)是否區(qū)分大小寫(xiě)。這取決于初始化參數(shù)配置默認(rèn)敏感可配置為不敏感。這是編寫(xiě)業(yè)務(wù) SQL、處理用戶(hù)輸入、做登錄校驗(yàn)等操作時(shí)需要關(guān)注的。第三維度是排序?qū)蛹磁判驎r(shí)的字符序規(guī)則。達(dá)夢(mèng)的默認(rèn)排序規(guī)則通常是二進(jìn)制排序也就是按字符編碼值排序。在二進(jìn)制排序下大寫(xiě)字母比如 ASCII 65 的 A和小寫(xiě)字母ASCII 97 的 a天然被區(qū)分開(kāi)所以排序結(jié)果里所有大寫(xiě)字母會(huì)排在小寫(xiě)字母前面。如果你想實(shí)現(xiàn)忽略大小寫(xiě)的排序需要顯式使用 LOWER 或 UPPER 函數(shù)處理或者初始化時(shí)指定不區(qū)分大小寫(xiě)的排序規(guī)則。下面用一個(gè)表格把三個(gè)維度整理清楚層面默認(rèn)行為影響對(duì)象調(diào)整方式標(biāo)識(shí)符解析不帶引號(hào)轉(zhuǎn)大寫(xiě)表名、列名、索引名、視圖名書(shū)寫(xiě)時(shí)加雙引號(hào)保留大小寫(xiě)字符串比較敏感WHERE 條件、JOIN 關(guān)聯(lián)、去重判斷初始化參數(shù)控制后期可通過(guò)函數(shù)繞行字符排序二進(jìn)制序區(qū)分大小寫(xiě)ORDER BY 結(jié)果、索引排序初始化時(shí)指定排序規(guī)則或 SQL 中函數(shù)處理理解這三個(gè)維度后面所有問(wèn)題都可以對(duì)號(hào)入座。3. 不同數(shù)據(jù)庫(kù)的遷移陷阱與初始化參數(shù)選擇3.1 MySQL 用戶(hù)的重災(zāi)區(qū)習(xí)慣性裸寫(xiě)表名我接觸過(guò)的項(xiàng)目中凡是達(dá)夢(mèng)適配翻車(chē)的十有八九是從 MySQL 遷移過(guò)來(lái)的開(kāi)發(fā)人員。為什么因?yàn)?MySQL 在 Linux 下默認(rèn)表名區(qū)分大小寫(xiě)、字段名不區(qū)分在 Windows 下都不區(qū)分而且 MySQL 的表名大小寫(xiě)規(guī)則是由lower_case_table_names參數(shù)控制的很多人從來(lái)沒(méi)研究過(guò)這個(gè)參數(shù)完全不知道自己的表名到底是大寫(xiě)還是小寫(xiě)。到了達(dá)夢(mèng)這里默認(rèn)全部轉(zhuǎn)大寫(xiě)導(dǎo)致的問(wèn)題非常多。舉一個(gè)真實(shí)發(fā)生過(guò)的案例某公司的業(yè)務(wù)系統(tǒng)原來(lái)跑在 MySQL 上數(shù)據(jù)庫(kù)里有兩張表一張叫OrderInfo一張叫orderinfo。在 MySQL 的 Windows 環(huán)境下這兩個(gè)名字其實(shí)指向同一張表因?yàn)椴粎^(qū)分大小寫(xiě)。開(kāi)發(fā)人員建表的時(shí)候手抖多建了一張也沒(méi)發(fā)現(xiàn)。遷移到達(dá)夢(mèng)時(shí)遷移工具執(zhí)行建表腳本先是建了OrderInfo然后建orderinfo結(jié)果達(dá)夢(mèng)把兩個(gè)都轉(zhuǎn)成大寫(xiě)ORDERINFO第二個(gè)建表語(yǔ)句直接報(bào)表名重復(fù)。這種錯(cuò)誤還算好排查。更隱蔽的是程序代碼里的動(dòng)態(tài) SQL。Java 代碼里用 MyBatis 寫(xiě) mapperXML 里寫(xiě)的表名是駝峰命名userLoginLog在 MySQL 下跑了幾年都沒(méi)事改成達(dá)夢(mèng)后突然暴露出ORA-00942: table or view does not exist式的錯(cuò)誤實(shí)際錯(cuò)誤信息是表或視圖不存在。原因就是達(dá)夢(mèng)把不帶引號(hào)的標(biāo)識(shí)符轉(zhuǎn)成USERLOGINLOG跟真實(shí)表名對(duì)不上。解決這類(lèi)問(wèn)題的思路有兩個(gè)要么把建表腳本全部改成大寫(xiě)統(tǒng)一風(fēng)格要么所有 SQL 里的表名都帶雙引號(hào)完全保留原始大小寫(xiě)。我個(gè)人的建議是第一種也就是規(guī)范成大寫(xiě)。因?yàn)橐坏┠汩_(kāi)始到處加雙引號(hào)后續(xù)的維護(hù)成本會(huì)非常高每個(gè) SQL 都得小心翼翼檢查引號(hào)位置人力成本不劃算。3.2 Oracle 用戶(hù)的天然默契達(dá)夢(mèng)默認(rèn)對(duì)齊 Oracle跟 MySQL 用戶(hù)相反有 Oracle 開(kāi)發(fā)經(jīng)驗(yàn)的人到達(dá)夢(mèng)幾乎是無(wú)縫切換。達(dá)夢(mèng)的默認(rèn)大小寫(xiě)敏感特性、標(biāo)識(shí)符轉(zhuǎn)大寫(xiě)規(guī)則、雙引號(hào)保留原樣規(guī)則跟 Oracle 幾乎一致。這也是達(dá)夢(mèng)在設(shè)計(jì)上的一個(gè)明確取向——兼容 Oracle 語(yǔ)法體系。達(dá)夢(mèng)的很多行為都是以 Oracle 為藍(lán)本的包括內(nèi)置函數(shù)NVL、DECODE、SYSDATE、PL/SQL 塊語(yǔ)法、包機(jī)制等。如果你是從 Oracle 遷移過(guò)來(lái)幾乎不需要在大小寫(xiě)問(wèn)題上做額外適配只要遵循原來(lái)的開(kāi)發(fā)規(guī)范建表時(shí)用大寫(xiě)、不帶雙引號(hào)SQL 里的關(guān)鍵字大寫(xiě)標(biāo)識(shí)符保持統(tǒng)一基本不會(huì)踩坑。但要注意Oracle 里有一個(gè)隱含細(xì)節(jié)某些工具比如舊版 PL/SQL Developer在執(zhí)行 SQL 時(shí)可能自動(dòng)加雙引號(hào)或自動(dòng)轉(zhuǎn)換大小寫(xiě)造成在 Oracle 里能跑到達(dá)夢(mèng)卻不能跑的假象。實(shí)質(zhì)不是達(dá)夢(mèng)的問(wèn)題而是工具行為不一致。建議在達(dá)夢(mèng)相關(guān)的開(kāi)發(fā)環(huán)境里統(tǒng)一使用達(dá)夢(mèng)自帶的 SQL 交互工具或通用客戶(hù)端同時(shí)打開(kāi) SQL 日志觀察實(shí)際執(zhí)行的語(yǔ)句被轉(zhuǎn)換成了什么形式。3.3 初始化參數(shù)大小寫(xiě)敏感配置必須在建庫(kù)前定達(dá)夢(mèng)的大小寫(xiě)敏感配置不是一個(gè)可以在實(shí)例運(yùn)行后隨便改的運(yùn)行參數(shù)它是在初始化實(shí)例即執(zhí)行 dminit 創(chuàng)建數(shù)據(jù)庫(kù)時(shí)寫(xiě)入控制文件的。所以你在部署達(dá)夢(mèng)環(huán)境的第一步就要想清楚這個(gè)庫(kù)要不要區(qū)分大小寫(xiě)在達(dá)夢(mèng)中控制大小寫(xiě)敏感相關(guān)的參數(shù)主要有兩個(gè)維度一個(gè)是CASE_SENSITIVE它是初始化參數(shù)取值為 1 或 01 表示敏感0 表示不敏感另一個(gè)是字符集和長(zhǎng)度單位配置比如LENGTH_IN_CHAR、CHARSET等這些參數(shù)會(huì)和大小寫(xiě)敏感配置一起固化到數(shù)據(jù)庫(kù)的初始化信息中。如果你在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)設(shè)置CASE_SENSITIVE0那么帶不帶雙引號(hào)的標(biāo)識(shí)符都會(huì)被統(tǒng)一處理成大寫(xiě)不敏感時(shí)雙引號(hào)不再保留大小寫(xiě)差異同時(shí)字符串比較也不再區(qū)分大小寫(xiě)。這意味著后面建的user_info和USER_INFO會(huì)被當(dāng)作同一個(gè)對(duì)象查abc和查ABC結(jié)果也是一樣的。聽(tīng)起來(lái)很省心是不是但我不建議你為了省事直接把所有庫(kù)都初始化成不敏感模式理由有三點(diǎn)第一不敏感模式下應(yīng)用層的一些大小寫(xiě)差異問(wèn)題會(huì)被數(shù)據(jù)庫(kù)掩蓋。比如程序里有個(gè)邏輯要判斷驗(yàn)證碼是否區(qū)分大小寫(xiě)如果在數(shù)據(jù)庫(kù)層就不敏感驗(yàn)證碼判斷就會(huì)失效這是嚴(yán)重的業(yè)務(wù)安全隱患。第二達(dá)夢(mèng)官方文檔和社區(qū)里的方案經(jīng)驗(yàn)大多數(shù)基于敏感模式展開(kāi)如果你搞成不敏感某些場(chǎng)景下的 SQL 調(diào)優(yōu)、函數(shù)行為、遷移工具表現(xiàn)可能跟文檔出現(xiàn)出入排查問(wèn)題時(shí)參考資料會(huì)少很多。第三以后如果要把數(shù)據(jù)遷移到 Oracle 或其他敏感庫(kù)存儲(chǔ)過(guò)程中用到的字符串比較邏輯會(huì)發(fā)生語(yǔ)義變化容易在遷移后產(chǎn)生數(shù)據(jù)校驗(yàn)不一致的問(wèn)題。所以我的建議是除非業(yè)務(wù)有明確需求否則默認(rèn)用敏感模式CASE_SENSITIVE1跟 Oracle 行為對(duì)齊未來(lái)無(wú)論遷移還是外部協(xié)同都最穩(wěn)妥。3.4 已經(jīng)創(chuàng)建好的實(shí)例如何確認(rèn)大小寫(xiě)敏感狀態(tài)如果你接手的是一套已經(jīng)跑起來(lái)的達(dá)夢(mèng)環(huán)境不清楚當(dāng)初建庫(kù)時(shí)到底選了哪種模式有兩個(gè)方法快速確認(rèn)。方法一是查詢(xún)視圖達(dá)夢(mèng)中可以通過(guò)系統(tǒng)視圖或動(dòng)態(tài)視圖獲取初始化參數(shù)。常見(jiàn)的查詢(xún)思路是查實(shí)例的建庫(kù)參數(shù)不同版本視圖名稱(chēng)略有差異通?？梢杂妙?lèi)似下面的方式查看SELECT N_PARAM_NAME, PARA_VALUE FROM V$DM_INI WHERE N_PARAM_NAME IN (CASE_SENSITIVE);如果不能直接查到 CASE_SENSITIVE可以查帶實(shí)例信息的視圖比如在一些版本中用SELECT CASE_SENSITIVE FROM V$DM_INI;或者通過(guò)初始化參數(shù)視圖查看SELECT * FROM SYS.SYSDM_INI;方法二是直接用 SQL 實(shí)測(cè)。建一張測(cè)試表CREATE TABLE test_case_table (id INT); -- 然后分別執(zhí)行下面兩條 SELECT * FROM TEST_CASE_TABLE; -- 不帶引號(hào) SELECT * FROM test_case_table; -- 帶引號(hào)小寫(xiě)如果第一條成功、第二條報(bào)表不存在說(shuō)明是敏感模式如果兩條都成功說(shuō)明是不敏感模式。這個(gè)方法最直觀不需要查任何源碼適合快速判斷。注意上面查詢(xún)中 V$DM_INI 的具體字段名在不同版本間可能略有出入。如果查出來(lái)是空集建議先用TABNAME LIKE V$DM_INI的方式確認(rèn)視圖存在性再根據(jù)實(shí)際字段結(jié)構(gòu)調(diào)整查詢(xún)語(yǔ)句。4. 實(shí)操驗(yàn)證與檢測(cè)一次跑通的大小寫(xiě)敏感實(shí)驗(yàn)4.1 實(shí)驗(yàn)環(huán)境與前置準(zhǔn)備為了讓你能夠照著復(fù)現(xiàn)我這里給出一個(gè)最小化的實(shí)驗(yàn)方案。環(huán)境方面需要一臺(tái)安裝了達(dá)夢(mèng)數(shù)據(jù)庫(kù)的服務(wù)器版本建議選擇 8.x 及以上當(dāng)前主流版本都可以運(yùn)行下面的語(yǔ)句。沒(méi)有達(dá)夢(mèng)環(huán)境的朋友可以使用達(dá)夢(mèng)官方提供的 Docker 鏡像快速起一個(gè)測(cè)試實(shí)例初始化參數(shù)采用默認(rèn)設(shè)置默認(rèn)即為大小寫(xiě)敏感。前置準(zhǔn)備只有一句話(huà)確保你能用 SYSDBA 用戶(hù)或其他具備建表權(quán)限的賬號(hào)登錄數(shù)據(jù)庫(kù)。登錄后第一步先確認(rèn)當(dāng)前實(shí)例的行為模式。執(zhí)行SELECT CASE_SENSITIVE FROM V$DM_INI;如果返回值為 1說(shuō)明當(dāng)前實(shí)例是大小寫(xiě)敏感模式如果返回值為 0則是不敏感模式。返回結(jié)果會(huì)告訴你接下里的實(shí)驗(yàn)預(yù)期是什么。4.2 實(shí)驗(yàn)一標(biāo)識(shí)符大小寫(xiě)解析驗(yàn)證這個(gè)實(shí)驗(yàn)用來(lái)驗(yàn)證不帶引號(hào)和帶引號(hào)的建表差異。步驟 1創(chuàng)建一張不帶引號(hào)的表CREATE TABLE SENSITIVE_TEST ( ID INT PRIMARY KEY, NAME VARCHAR(30) );步驟 2嘗試用全大寫(xiě)查詢(xún)、全小寫(xiě)查詢(xún)和引號(hào)查詢(xún)-- 全大寫(xiě)預(yù)期成功 SELECT COUNT(*) FROM SENSITIVE_TEST; -- 全小寫(xiě)預(yù)期在敏感模式下也成功系統(tǒng)自動(dòng)轉(zhuǎn)為大寫(xiě) SELECT COUNT(*) FROM sensitive_test; -- 帶雙引號(hào)小寫(xiě)預(yù)期報(bào)表或視圖不存在 SELECT COUNT(*) FROM sensitive_test;實(shí)驗(yàn)結(jié)果說(shuō)明前兩條都能跑通因?yàn)橄到y(tǒng)把不帶引號(hào)的標(biāo)識(shí)符統(tǒng)一轉(zhuǎn)成了大寫(xiě)。第三條報(bào)錯(cuò)因?yàn)殡p引號(hào)讓系統(tǒng)按原始小寫(xiě)名稱(chēng)去尋找而實(shí)際存在的表是大寫(xiě)。步驟 3再創(chuàng)建一張帶雙引號(hào)的表CREATE TABLE mixed_case_table ( ID INT );然后嘗試訪(fǎng)問(wèn)-- 帶引號(hào)訪(fǎng)問(wèn)預(yù)期成功 SELECT COUNT(*) FROM mixed_case_table; -- 不帶引號(hào)訪(fǎng)問(wèn)預(yù)期失敗 SELECT COUNT(*) FROM mixed_case_table;第二次查詢(xún)失敗的原因是不帶引號(hào)時(shí)系統(tǒng)將mixed_case_table轉(zhuǎn)成了大寫(xiě)MIXED_CASE_TABLE去找但實(shí)際表名是小寫(xiě)的mixed_case_table所以找不到。實(shí)驗(yàn)結(jié)論你建表時(shí)寫(xiě)了什么不重要重要的是你有沒(méi)有加雙引號(hào)。沒(méi)加雙引號(hào)系統(tǒng)全轉(zhuǎn)大寫(xiě)加了雙引號(hào)系統(tǒng)原樣保留。4.3 實(shí)驗(yàn)二字符串?dāng)?shù)據(jù)比較驗(yàn)證這個(gè)實(shí)驗(yàn)需要一張有數(shù)據(jù)的表。沿用SENSITIVE_TEST插入幾條不同大小寫(xiě)的數(shù)據(jù)INSERT INTO SENSITIVE_TEST VALUES (1, Admin); INSERT INTO SENSITIVE_TEST VALUES (2, admin); INSERT INTO SENSITIVE_TEST VALUES (3, ADMIN); COMMIT;分別執(zhí)行SELECT * FROM SENSITIVE_TEST WHERE NAME admin; -- 預(yù)期只返回 ID2 那一行因?yàn)槟J(rèn)敏感模式下 admin 與 Admin、ADMIN 不相等 SELECT * FROM SENSITIVE_TEST WHERE UPPER(NAME) ADMIN; -- 預(yù)期返回全部三行因?yàn)?UPPER 把所有名字都轉(zhuǎn)成大寫(xiě)再比較 SELECT * FROM SENSITIVE_TEST WHERE LOWER(NAME) admin; -- 預(yù)期返回全部三行同理這個(gè)實(shí)驗(yàn)直觀展示了默認(rèn)模式的字符串比較行為實(shí)際業(yè)務(wù)中如果用戶(hù)表里的賬號(hào)存在大小寫(xiě)混用而你用精確等值去匹配結(jié)果會(huì)非常詭異。比如登錄時(shí)用戶(hù)輸入的賬號(hào)是admin數(shù)據(jù)庫(kù)里存的注冊(cè)賬號(hào)是Admin默認(rèn)模式下就永遠(yuǎn)匹配不上。4.4 實(shí)驗(yàn)三ORDER BY 排序規(guī)則驗(yàn)證繼續(xù)用剛才的數(shù)據(jù)SELECT NAME FROM SENSITIVE_TEST ORDER BY NAME;二進(jìn)制排序下結(jié)果大概率是ADMIN、Admin、admin因?yàn)榇髮?xiě)字母的 ASCII 碼值小于小寫(xiě)字母。如果業(yè)務(wù)要求排序時(shí)忽略大小寫(xiě)可以寫(xiě)成SELECT NAME FROM SENSITIVE_TEST ORDER BY LOWER(NAME);這樣三個(gè)值都會(huì)被轉(zhuǎn)成admin后參與排序并列順序取決于數(shù)據(jù)庫(kù)內(nèi)部的二次排序規(guī)則但至少不會(huì)出現(xiàn)大寫(xiě)全部排在小寫(xiě)前面的現(xiàn)象。這里要注意對(duì)列使用函數(shù)會(huì)導(dǎo)致該列上的索引失效如果排序字段數(shù)據(jù)量大需要考慮函數(shù)索引或初始化時(shí)優(yōu)化排序規(guī)則。5. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄5.1 建表后查詢(xún)報(bào)表或視圖不存在這個(gè)問(wèn)題的本質(zhì)幾乎永遠(yuǎn)是標(biāo)識(shí)符大小寫(xiě)不匹配。排查思路是確認(rèn)你建表時(shí)有沒(méi)有用雙引號(hào)以及你用查詢(xún)語(yǔ)句時(shí)表名的實(shí)際解析形態(tài)。最常見(jiàn)的情況是建表腳本里某些對(duì)象名加了雙引號(hào)而查詢(xún)腳本里沒(méi)加或者反過(guò)來(lái)。比如某同事建的視圖名稱(chēng)是v_UserInfo查詢(xún)時(shí)寫(xiě)SELECT * FROM v_UserInfo系統(tǒng)轉(zhuǎn)大寫(xiě)后變成V_USERINFO和真實(shí)的小寫(xiě)視圖名對(duì)不上于是報(bào)不存在。解決辦法也很簡(jiǎn)單優(yōu)先去掉所有雙引號(hào)讓系統(tǒng)統(tǒng)一轉(zhuǎn)大寫(xiě)如果由于歷史原因已經(jīng)產(chǎn)生了大量帶引號(hào)的對(duì)象名那就只能改 SQL 里的訪(fǎng)問(wèn)方式把查詢(xún)語(yǔ)句里的對(duì)象名也加上同樣的雙引號(hào)。5.2 存儲(chǔ)過(guò)程編譯成功但執(zhí)行報(bào)錯(cuò)有些存儲(chǔ)過(guò)程在創(chuàng)建時(shí)沒(méi)問(wèn)題一調(diào)用就報(bào)無(wú)效對(duì)象或表或視圖不存在。這種問(wèn)題往往埋藏在過(guò)程體內(nèi)的動(dòng)態(tài) SQL 中。比如直接在存儲(chǔ)過(guò)程里拼字符串EXECUTE IMMEDIATE SELECT * FROM || v_table_name;如果變量 v_table_name 存的是 UserInfo動(dòng)態(tài)拼接后執(zhí)行時(shí)不帶引號(hào)就被轉(zhuǎn)成USERINFO和真實(shí)表名對(duì)不上。而存儲(chǔ)過(guò)程在編譯階段不會(huì)解析動(dòng)態(tài) SQL所以創(chuàng)建時(shí)一切正常運(yùn)行到這一段才報(bào)錯(cuò)。這類(lèi)問(wèn)題的排查方法是在存儲(chǔ)過(guò)程里臨時(shí)輸出拼接后的 SQL 文本仔細(xì)觀察表名是否被轉(zhuǎn)成了大寫(xiě)或者干脆統(tǒng)一規(guī)范建表時(shí)全部大寫(xiě)命名動(dòng)態(tài) SQL 拼表名時(shí)也傳大寫(xiě)。5.3 從 MySQL 遷到達(dá)夢(mèng)后應(yīng)用登錄失效前文提過(guò)登錄模塊是大小寫(xiě)敏感重災(zāi)區(qū)。遷移后用戶(hù)反饋密碼正確但是登錄失敗十有八九是字符串比較導(dǎo)致的而非密碼加密算法出了問(wèn)題。舉個(gè)例子原系統(tǒng)在 MySQL 里查詢(xún)用戶(hù)時(shí)的邏輯是WHERE user_name ? AND pwd_hash ?MySQL 的排序規(guī)則如果是utf8_general_ci比較時(shí)不區(qū)分大小寫(xiě)用戶(hù)輸入ADMIN和庫(kù)里的admin也能匹配上。到了達(dá)夢(mèng)默認(rèn)敏感輸入ADMIN就匹配不上admin于是登錄失敗。遇到這類(lèi)問(wèn)題不要急著改代碼里的查詢(xún)邏輯。先定位比較字段是否需要大小寫(xiě)不敏感。如果業(yè)務(wù)上確實(shí)不區(qū)分推薦方案是查詢(xún)時(shí)統(tǒng)一調(diào)用UPPER()函數(shù)處理兩側(cè)入?yún)ELECT * FROM t_user WHERE UPPER(user_name) UPPER(Admin);這種方式不影響存儲(chǔ)層數(shù)據(jù)只改變比較行為。但要留意加了函數(shù)后該字段上的索引可能失效如果這個(gè)查詢(xún)是高頻接口建議把賬號(hào)列統(tǒng)一轉(zhuǎn)成大寫(xiě)存儲(chǔ)或者建立一個(gè)函數(shù)索引。另外一個(gè)思路是在導(dǎo)入數(shù)據(jù)時(shí)統(tǒng)一轉(zhuǎn)換賬號(hào)和密碼字段的大小寫(xiě)格式比如賬號(hào)一律存小寫(xiě)、密碼哈希不留大小寫(xiě)語(yǔ)義從源頭規(guī)避問(wèn)題。5.4 DM 管理工具里的顯示與 SQL 里的實(shí)際大小寫(xiě)不一致偶爾會(huì)遇到用 DM 管理工具查看表顯示的是UserInfo可 SQL 查詢(xún)里寫(xiě)UserInfo卻報(bào)不存在。這個(gè)現(xiàn)象其實(shí)不矛盾。管理工具在展示對(duì)象時(shí)可能按創(chuàng)建語(yǔ)句的原始文本顯示而 SQL 解析時(shí)默認(rèn)轉(zhuǎn)大寫(xiě)。如果創(chuàng)建時(shí)用了雙引號(hào)保留了大小寫(xiě)展示就是小寫(xiě)或混寫(xiě)如果查詢(xún)時(shí)不帶雙引號(hào)就會(huì)因?yàn)檗D(zhuǎn)成大寫(xiě)而找不到。為了避免這種混亂我個(gè)人的習(xí)慣是用管理工具查看對(duì)象的創(chuàng)建語(yǔ)句或者DDL 文本確認(rèn)真實(shí)名稱(chēng)是否帶引號(hào)所有手動(dòng)書(shū)寫(xiě)的 SQL 統(tǒng)一按大寫(xiě)標(biāo)識(shí)符書(shū)寫(xiě)且不帶雙引號(hào)。5.5 常見(jiàn)問(wèn)題排查速查表問(wèn)題現(xiàn)象可能原因排查方法解決方案表或視圖不存在標(biāo)識(shí)符大小寫(xiě)不匹配查看真實(shí)表名是否含引號(hào)保留的大小寫(xiě)去掉雙引號(hào)統(tǒng)一大寫(xiě)或訪(fǎng)問(wèn)時(shí)加引號(hào)登錄校驗(yàn)失敗字符串比較區(qū)分大小寫(xiě)比對(duì)輸入值和庫(kù)存值的 UPPER 結(jié)果SQL 中顯式使用 UPPER 函數(shù)遷移腳本報(bào)對(duì)象重復(fù)表名轉(zhuǎn)大寫(xiě)后重名檢查建表腳本中是否存在差異大小寫(xiě)表名合并同名對(duì)象統(tǒng)一命名規(guī)范存儲(chǔ)過(guò)程動(dòng)態(tài) SQL 報(bào)錯(cuò)運(yùn)行時(shí)表名被轉(zhuǎn)換打印拼接結(jié)果確認(rèn)實(shí)際解析名稱(chēng)變量傳值使用大寫(xiě)或雙引號(hào)包裹排序結(jié)果出乎意料二進(jìn)制排序規(guī)則查看 ORDER BY 原始結(jié)果使用 LOWER/UPPER 包裹排序字段6. 從開(kāi)發(fā)規(guī)范層面根治大小寫(xiě)敏感問(wèn)題大小寫(xiě)敏感真正折磨人的地方不在某一個(gè)單獨(dú)的坑而是它散布在 SQL 編寫(xiě)的各個(gè)角落。這里分享幾個(gè)我實(shí)際踩過(guò)坑之后沉淀下來(lái)的開(kāi)發(fā)規(guī)范照著執(zhí)行基本能把坑填平。第一個(gè)規(guī)范是建表語(yǔ)句里的對(duì)象名一律不加雙引號(hào)同時(shí)使用大寫(xiě)命名。別依賴(lài)編輯器幫你轉(zhuǎn)大小寫(xiě)直接用大寫(xiě)字母寫(xiě)表名、字段名、存儲(chǔ)過(guò)程名。比如建表就寫(xiě)CREATE TABLE USER_INFO而不是CREATE TABLE user_info。后者雖然也能跑但在團(tuán)隊(duì)協(xié)作時(shí)不同成員編碼習(xí)慣不同代碼里一會(huì)大寫(xiě)一會(huì)小寫(xiě)排查成本就上來(lái)了。第二個(gè)規(guī)范是 SQL 關(guān)鍵字使用大寫(xiě)。這個(gè)不是為了裝樣子而是為了保證團(tuán)隊(duì) SQL 風(fēng)格統(tǒng)一降低誤讀風(fēng)險(xiǎn)。特別是動(dòng)態(tài) SQL 拼接時(shí)統(tǒng)一大寫(xiě)能夠減少拼寫(xiě)錯(cuò)誤的概率。第三個(gè)規(guī)范是 Java/Mapper 層 XML 里不要使用雙引號(hào)包表名。有些開(kāi)發(fā)喜歡把 XML 里的 SQL 寫(xiě)成SELECT * FROM user_info WHERE ...這能跑通但一旦別人接手不知道這里用了雙引號(hào)直接在數(shù)據(jù)庫(kù)客戶(hù)端里執(zhí)行SELECT * FROM user_info就會(huì)報(bào)錯(cuò)還以為代碼有問(wèn)題。為了少制造這種困惑對(duì)象引用上要克制使用雙引號(hào)。第四個(gè)規(guī)范是字符串比較統(tǒng)一處理。涉及用戶(hù)輸入與數(shù)據(jù)庫(kù)值比較的場(chǎng)景業(yè)務(wù)沒(méi)特殊要求時(shí)統(tǒng)一調(diào)用 UPPER 或 LOWER 函數(shù)。比如手機(jī)號(hào)、郵箱、用戶(hù)名這類(lèi)通常不區(qū)分大小寫(xiě)的字段查詢(xún)時(shí)兩側(cè)都套上函數(shù)保持語(yǔ)義一致。第五個(gè)規(guī)范是測(cè)試用例里必須覆蓋大小寫(xiě)邊界。每張涉及登錄、權(quán)限、唯一性校驗(yàn)的表準(zhǔn)備大小寫(xiě)不同的輸入數(shù)據(jù)跑一遍查詢(xún)和校驗(yàn)邏輯確認(rèn)行為和業(yè)務(wù)預(yù)期一致。這些規(guī)范看著瑣碎但確實(shí)能救命。我見(jiàn)過(guò)太多項(xiàng)目上線(xiàn)后因?yàn)榇笮?xiě)問(wèn)題出事故事后一查都是最基礎(chǔ)的問(wèn)題早一點(diǎn)定好規(guī)范后面能省大量排查時(shí)間。7. 進(jìn)階思考大小寫(xiě)敏感與性能、遷移的關(guān)聯(lián)7.1 函數(shù)包裹導(dǎo)致索引失效的取舍前面提到過(guò)為了讓字符串比較不敏感常常會(huì)在字段上套UPPER()函數(shù)。這個(gè)做法簡(jiǎn)單直接但代價(jià)是字段上的普通索引可能無(wú)法使用查詢(xún)?nèi)頀呙栊阅軙?huì)明顯下滑。如果遇到登錄接口這種高頻低數(shù)據(jù)量的場(chǎng)景全表掃描問(wèn)題不大。但如果是千萬(wàn)級(jí)數(shù)據(jù)量的流水表按編碼字段查詢(xún)且業(yè)務(wù)需要忽略大小寫(xiě)就要考慮建立函數(shù)索引CREATE INDEX IDX_USER_NAME_UPPER ON SENSITIVE_TEST (UPPER(NAME));建了函數(shù)索引之后WHERE UPPER(NAME) ADMIN才能走索引掃描。這個(gè)點(diǎn)很容易被忽略因?yàn)槠胀ㄋ饕秃瘮?shù)索引的成本完全不同純粹靠數(shù)據(jù)量小掩蓋問(wèn)題的話(huà)上線(xiàn)之后遲早會(huì)暴露。7.2 遷移工具與大小寫(xiě)敏感的聯(lián)動(dòng)使用達(dá)夢(mèng)的遷移工具從 MySQL 導(dǎo)數(shù)據(jù)時(shí)工具通常會(huì)自動(dòng)根據(jù)源端和目標(biāo)端的參數(shù)做名稱(chēng)轉(zhuǎn)換。但工具不是萬(wàn)能的它不會(huì)替你想清楚業(yè)務(wù)語(yǔ)義。比如源端 MySQL 的表名是小寫(xiě)工具遷移到達(dá)夢(mèng)后默認(rèn)轉(zhuǎn)大寫(xiě)這個(gè)過(guò)程一般沒(méi)問(wèn)題但如果你后續(xù)在達(dá)夢(mèng)上執(zhí)行源端導(dǎo)出的 SQL 備份腳本腳本里的小寫(xiě)表名可能因?yàn)檎也坏綄?duì)象而報(bào)錯(cuò)。我建議的做法是遷移完成后寫(xiě)一個(gè)巡檢腳本把數(shù)據(jù)庫(kù)中所有用戶(hù)表和索引的最終名稱(chēng)查出來(lái)與原系統(tǒng)做一輪比對(duì)專(zhuān)門(mén)核對(duì)大小寫(xiě)是否有差異。不要只抽樣要全量比對(duì)這類(lèi)問(wèn)題隱蔽性極強(qiáng)抽樣大概率發(fā)現(xiàn)不了。7.3 是否要把整庫(kù)改成不敏感模式有些團(tuán)隊(duì)被大小寫(xiě)問(wèn)題折騰幾輪后會(huì)想直接把新庫(kù)初始化成不敏感模式。這個(gè)思路短期看省事長(zhǎng)期看隱患不小。不敏感模式下你確實(shí)不會(huì)再遇到表名大小寫(xiě)報(bào)錯(cuò)的問(wèn)題但同時(shí)也失去了一種約束力——數(shù)據(jù)庫(kù)不再倒逼開(kāi)發(fā)團(tuán)隊(duì)維持命名規(guī)范和比較規(guī)范。等到未來(lái)某一天業(yè)務(wù)要求對(duì)密碼做大小寫(xiě)敏感校驗(yàn)或者要與外部系統(tǒng)做數(shù)據(jù)對(duì)接而外部系統(tǒng)區(qū)分大小寫(xiě)你會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)層面的不敏感反而成了阻礙。這里的建議是敏感模式的成本集中在前期開(kāi)發(fā)和建設(shè)規(guī)范不敏感模式的成本分散在后續(xù)業(yè)務(wù)演進(jìn)和數(shù)據(jù)交互。從控制長(zhǎng)期風(fēng)險(xiǎn)的角度我更傾向敏感模式加規(guī)范化的組合拳。如果你確實(shí)有合規(guī)或特殊業(yè)務(wù)要求必須用不敏感模式建議在項(xiàng)目文檔里顯式記錄這個(gè)決策至少讓后來(lái)接手的同事知道這個(gè)庫(kù)和默認(rèn)行為不一樣。8. 實(shí)際操作中我的體會(huì)接觸達(dá)夢(mèng)這些年我最大的感受是大小寫(xiě)敏感本身不算難難的是它帶來(lái)的連鎖問(wèn)題太分散。從一個(gè)表名查不到的小報(bào)錯(cuò)能牽扯出動(dòng)態(tài) SQL、字符串比較、索引失效、遷移腳本兼容性、團(tuán)隊(duì)編碼規(guī)范一系列問(wèn)題。正因?yàn)檫@樣我建議所有剛接手達(dá)夢(mèng)項(xiàng)目的團(tuán)隊(duì)開(kāi)工第一天就把大小寫(xiě)相關(guān)的事項(xiàng)寫(xiě)進(jìn)團(tuán)隊(duì)公約里實(shí)例初始化參數(shù)怎么定、建表命名風(fēng)格怎么統(tǒng)一、查詢(xún)語(yǔ)句里遇到字符串比較怎么處理、動(dòng)態(tài) SQL 拼接要注意什么。別覺(jué)得這是小題大做等線(xiàn)上出了事故再補(bǔ)規(guī)范代價(jià)不是一個(gè)量級(jí)的。還有一點(diǎn)小技巧可以分享如果你要調(diào)試一段在達(dá)夢(mèng)上報(bào)錯(cuò)的 SQL先在數(shù)據(jù)庫(kù)客戶(hù)端里不帶引號(hào)執(zhí)行一遍看系統(tǒng)報(bào)不報(bào)表或視圖不存在如果報(bào)就把真實(shí)表名查出來(lái)對(duì)照一下是不是大小寫(xiě)的問(wèn)題。很多看似復(fù)雜的排查最后一步就解決在這上面。最后再給新接觸達(dá)夢(mèng)的朋友一個(gè)建議先建一張測(cè)試表把大小寫(xiě)相關(guān)的幾個(gè)實(shí)驗(yàn)自己跑一遍花不了十分鐘但對(duì)后續(xù)理解達(dá)夢(mèng)的各種報(bào)錯(cuò)信息非常有幫助。數(shù)據(jù)和環(huán)境都有了之后很多問(wèn)題一眼就能看穿。