我在數(shù)據(jù)庫(kù)這個(gè)行當(dāng)里泡了十多年日常被問(wèn)到最多的往往不是復(fù)雜的性能調(diào)優(yōu)反而是那些看起來(lái)“特別基礎(chǔ)”的小問(wèn)題。就拿今天要聊的這件事來(lái)說(shuō)怎么用一條SELECT不碰任何表憑空造出一條數(shù)據(jù)來(lái)甚至一次性造出好幾條這種操作在圈里常被稱(chēng)為“構(gòu)造常量記錄”或者“偽表查詢(xún)”。聽(tīng)起來(lái)不值一提但真到了寫(xiě)的時(shí)候坑一點(diǎn)不比復(fù)雜報(bào)表少而且它還特別考驗(yàn)?zāi)銓?duì)數(shù)據(jù)庫(kù)語(yǔ)法的理解。這篇文章就把這個(gè)主題拆開(kāi)聊透從單條常量的最簡(jiǎn)寫(xiě)法到多條常量的幾種構(gòu)造方式再到不同數(shù)據(jù)庫(kù)平臺(tái)的差異最后講幾個(gè)進(jìn)階玩法和排查經(jīng)驗(yàn)希望能幫你把這個(gè)順手的小技巧真正用起來(lái)。1. 為什么需要SELECT構(gòu)造常量記錄三個(gè)高頻場(chǎng)景1.1 從一次“造數(shù)”經(jīng)歷說(shuō)起上個(gè)月幫同事排查一個(gè)報(bào)表問(wèn)題當(dāng)時(shí)的SQL邏輯是按月份統(tǒng)計(jì)銷(xiāo)售額沒(méi)有訂單的月份也要補(bǔ)0不能直接不顯示。同事的第一反應(yīng)是建一張臨時(shí)表把12個(gè)月份放進(jìn)去。我說(shuō)你先別急著建表拿一條SELECT直接把這個(gè)“月份表”造出來(lái)試試。他愣了一下然后在編輯器里敲出了類(lèi)似這樣的東西SELECT 2024-01 AS month, 0 AS cnt UNION ALL SELECT 2024-02, 0 UNION ALL SELECT 2024-03, 0;跑完之后他說(shuō)了句讓我印象很深的話(huà)“原來(lái)數(shù)據(jù)真的可以不從表里來(lái)?！边@個(gè)例子特別典型。在很多人的認(rèn)知里SELECT后面必須跟FROM必須有真實(shí)存在的表。但從SQL標(biāo)準(zhǔn)開(kāi)始SELECT就可以沒(méi)有FROM個(gè)別數(shù)據(jù)庫(kù)加個(gè)虛擬表兜底可以直接輸出常量、表達(dá)式或者函數(shù)計(jì)算結(jié)果。這就是構(gòu)造常量記錄的本質(zhì)不依賴(lài)任何持久化存儲(chǔ)直接在查詢(xún)里“造”出結(jié)構(gòu)化的數(shù)據(jù)行。1.2 具體哪些場(chǎng)景最值得用我總結(jié)下來(lái)下面這幾類(lèi)場(chǎng)景用常量記錄是最省事的快速驗(yàn)證函數(shù)和表達(dá)式。比如你想確認(rèn)某個(gè)數(shù)據(jù)庫(kù)里日期加減的寫(xiě)法一條SELECT NOW() INTERVAL 1 DAY就能試出來(lái)不用去翻文檔更不用找張帶日期字段的表。**構(gòu)造測(cè)試輸入。**寫(xiě)存儲(chǔ)過(guò)程、報(bào)表、接口前需要一組結(jié)構(gòu)化數(shù)據(jù)來(lái)驗(yàn)證邏輯。造臨時(shí)表麻煩用常量記錄幾秒搞定。**報(bào)表補(bǔ)零和基準(zhǔn)維度。**統(tǒng)計(jì)每個(gè)月、每個(gè)狀態(tài)的數(shù)據(jù)時(shí)如果某個(gè)月沒(méi)有記錄SQL結(jié)果里就少了這一行。用常量記錄構(gòu)造“基準(zhǔn)數(shù)據(jù)”再LEFT JOIN業(yè)務(wù)表就能優(yōu)雅地補(bǔ)出0值。**連接探活和工具調(diào)試。**數(shù)據(jù)庫(kù)客戶(hù)端連上之后隨手敲一行SELECT 1能通就說(shuō)明連接沒(méi)問(wèn)題。這在排查連接池、網(wǎng)絡(luò)、權(quán)限問(wèn)題時(shí)尤其常用。**臨時(shí)參數(shù)和配置。**某些查詢(xún)需要臨時(shí)拼一組配置項(xiàng)又不想動(dòng)數(shù)據(jù)庫(kù)里的配置表用常量記錄作為子查詢(xún)即可。1.3 為什么不用臨時(shí)表或INSERT你可能會(huì)問(wèn)臨時(shí)表不行嗎INSERT不行嗎當(dāng)然行但要分場(chǎng)景。臨時(shí)表適合數(shù)據(jù)量大、需要反復(fù)使用、可能需要加索引的情況它的缺點(diǎn)是重要寫(xiě)DDL涉及事務(wù)和日志用完要清理。而SELECT常量記錄完全在內(nèi)存中計(jì)算語(yǔ)句結(jié)束即消失不產(chǎn)生任何落盤(pán)數(shù)據(jù)。對(duì)于一次性的驗(yàn)證、補(bǔ)零和測(cè)試場(chǎng)景SELECT常量的心智負(fù)擔(dān)和操作成本都低得多。所以我的建議很直接**一兩行、十幾行的固定數(shù)據(jù)優(yōu)先用SELECT常量記錄上百行、要反復(fù)JOIN且要索引的才考慮臨時(shí)表或正式表。**很多人習(xí)慣了一種方式就容易忘了另一種但兩個(gè)都是工具箱里應(yīng)有備用的家伙。2. 單條常量記錄的寫(xiě)法與細(xì)節(jié)2.1 最簡(jiǎn)寫(xiě)法SELECT 1先從最簡(jiǎn)單的開(kāi)始。在MySQL、PostgreSQL、SQL Server、SQLite里直接執(zhí)行SELECT 1;結(jié)果就是一列一行數(shù)值1。改成SELECT hello; SELECT NOW(); SELECT DATABASE();都是同理。這條SQL表面上是“查詢(xún)”實(shí)際上就是在計(jì)算一個(gè)表達(dá)式輸出一個(gè)單行單列的結(jié)果。但Oracle是個(gè)例外。Oracle的SELECT語(yǔ)法強(qiáng)制要求帶FROM子句所以你在Oracle里要寫(xiě)成SELECT 1 FROM DUAL;這里的DUAL是Oracle專(zhuān)門(mén)為這類(lèi)“不查表”的場(chǎng)景準(zhǔn)備的一張?zhí)摂M表官方名字叫“DUAL”意思是“雙重的”“對(duì)偶的”它就固定提供一行一列內(nèi)容無(wú)所謂。SELECT SYSDATE FROM DUAL這類(lèi)寫(xiě)法在Oracle里可以說(shuō)是刻進(jìn)DNA的習(xí)慣。國(guó)產(chǎn)數(shù)據(jù)庫(kù)里有不少是兼容Oracle的比如達(dá)夢(mèng)數(shù)據(jù)庫(kù)同樣保留了FROM DUAL的寫(xiě)法。2.2 帶字段名的單條常量記錄實(shí)際干活時(shí)光輸出一個(gè)值往往不夠你需要的是“帶列名的結(jié)構(gòu)化記錄”。比如SELECT 張三 AS user_name, 25 AS user_age, 研發(fā)部 AS dept_name;這段SQL在MySQL里直接執(zhí)行就能返回一行數(shù)據(jù)三列分別為user_name、user_age、dept_name。這里的字段別名很重要因?yàn)楹罄m(xù)如果把這個(gè)查詢(xún)作為子查詢(xún)外部引用列名時(shí)就要靠它。從這條SQL里可以延伸出幾個(gè)容易忽略的點(diǎn)字符串常量必須用單引號(hào)。在Oracle里尤其忌諱雙引號(hào)因?yàn)殡p引號(hào)在Oracle里表示“標(biāo)識(shí)符”列名、表名你寫(xiě)SELECT abc FROM DUALOracle會(huì)理解成你要查一個(gè)叫abc的列然后報(bào)錯(cuò)ORA-00904。日期常量建議用標(biāo)準(zhǔn)日期字面量。SELECT DATE 2024-06-01 AS d可以在多個(gè)數(shù)據(jù)庫(kù)里用。如果直接寫(xiě)SELECT 2024-06-01在MySQL里會(huì)被當(dāng)成數(shù)值減法算出20072024減去6再減1這種低級(jí)錯(cuò)誤我見(jiàn)過(guò)不止一次。數(shù)值常量默認(rèn)類(lèi)型在各平臺(tái)不盡相同。比如PostgreSQL里SELECT 1得到的是integerSELECT 1.5是numeric。后面如果要和其他列做運(yùn)算要注意隱式轉(zhuǎn)換。2.3 用常量記錄當(dāng)表達(dá)式“計(jì)算器”單條常量記錄還有一個(gè)非常實(shí)用的用途當(dāng)計(jì)算器用。SELECT 1 1 AS result; SELECT (100 - 30) * 2 AS total; SELECT CONCAT(Hello, , World) AS greeting; SELECT DATE_ADD(2024-06-01, INTERVAL 30 DAY) AS new_date; SELECT DATEDIFF(2024-06-01, 2024-01-01) AS diff_days;我經(jīng)常在連上數(shù)據(jù)庫(kù)客戶(hù)端后順手驗(yàn)證一個(gè)日期函數(shù)、字符串函數(shù)的語(yǔ)法和結(jié)果比打開(kāi)系統(tǒng)計(jì)算器、翻文檔快得多。這本質(zhì)上是把SELECT語(yǔ)句當(dāng)成了一個(gè)“關(guān)系數(shù)據(jù)庫(kù)里的JS console”。有個(gè)熱詞叫select replace(getdate(), -, _)這其實(shí)是一段典型的SQL Server風(fēng)格寫(xiě)法。GETDATE()在SQL Server里取當(dāng)前時(shí)間REPLACE替換字符串中的橫杠為下劃線(xiàn)。如果換到MySQL你得寫(xiě)成SELECT REPLACE(NOW(), -, _)換到Oracle則是SELECT REPLACE(SYSDATE, -, _) FROM DUAL。三個(gè)數(shù)據(jù)庫(kù)函數(shù)名都不一樣用常量記錄一跑就能立刻發(fā)現(xiàn)差異這也是它的價(jià)值所在。2.4 單條常量記錄加條件判斷常量記錄還可以帶WHERE條件。比如SELECT 滿(mǎn)足條件 AS result WHERE 1 1; -- 返回一行 SELECT 滿(mǎn)足條件 AS result WHERE 1 2; -- 返回空結(jié)果這在排查邏輯、驗(yàn)證條件分支時(shí)很有用。但要注意在Oracle里不能直接這么寫(xiě)必須寫(xiě)成SELECT 滿(mǎn)足條件 AS result FROM DUAL WHERE 1 1;因?yàn)閃HERE子句是依附于FROM后的表來(lái)執(zhí)行的Oracle沒(méi)有FROM就談不上“哪一行滿(mǎn)足條件”。2.5 單條常量記錄的經(jīng)驗(yàn)筆記別名別碰保留字。比如SELECT x AS user在某些數(shù)據(jù)庫(kù)里可能報(bào)錯(cuò)換成user_name或加反引號(hào)、雙引號(hào)、方括號(hào)看平臺(tái)才安全。中文亂碼時(shí)先查會(huì)話(huà)字符集。SELECT character_set_clientMySQL或SELECT USERENV(language) FROM DUALOracle這類(lèi)方式能快速定位。常量記錄不是“免檢產(chǎn)品”。你自己造的數(shù)據(jù)也要保證準(zhǔn)確尤其是日期、金額這類(lèi)敏感字段后面在排查章節(jié)我會(huì)講一個(gè)真實(shí)翻車(chē)案例。3. 多條常量記錄的三種構(gòu)造方案3.1 UNION ALL拼接最通用最推薦要一次性造出多行數(shù)據(jù)最樸素也最可靠的辦法就是用UNION ALL把多個(gè)單條SELECT串起來(lái)SELECT 2024-01 AS month, 0 AS cnt UNION ALL SELECT 2024-02, 0 UNION ALL SELECT 2024-03, 0;結(jié)果就是三行兩列的一張“內(nèi)存表”。為什么這里必須用UNION ALL而不是UNION兩個(gè)原因。第一UNION會(huì)做去重操作如果常量的行本身就不重復(fù)去重就是白干還增加排序和比較的開(kāi)銷(xiāo)第二UNION的語(yǔ)義是合并去重UNION ALL的語(yǔ)義是直接拼接我們構(gòu)造數(shù)據(jù)時(shí)通常希望保留所有行所以UNION ALL從語(yǔ)義到性能都更合適。用UNION ALL構(gòu)造多行常量時(shí)還有幾個(gè)要點(diǎn)各分支的列數(shù)必須一致。第一行選了3列后續(xù)每行也得是3列否則直接報(bào)錯(cuò)。結(jié)果集的列名由第一個(gè)SELECT的別名決定。后續(xù)分支的別名寫(xiě)不寫(xiě)都不影響結(jié)果列名我建議寫(xiě)上保證可讀性。各分支列的類(lèi)型要兼容。第一個(gè)分支第一列是字符串第二個(gè)分支第一列突然給個(gè)數(shù)字部分?jǐn)?shù)據(jù)庫(kù)會(huì)做隱式轉(zhuǎn)換部分會(huì)直接報(bào)類(lèi)型不一致錯(cuò)誤。穩(wěn)妥的做法是用CAST顯式轉(zhuǎn)換。3.2 值構(gòu)造器VALUES代碼更簡(jiǎn)潔在不少數(shù)據(jù)庫(kù)里還可以用值構(gòu)造器row value constructor也常被稱(chēng)為表值構(gòu)造器來(lái)寫(xiě)多行常量。寫(xiě)法大致是MySQL 8.0.19以上版本VALUES ROW(1, 華東), ROW(2, 華北), ROW(3, 華南);PostgreSQL和SQL Server的寫(xiě)法很接近SELECT * FROM (VALUES (1, 華東), (2, 華北), (3, 華南) ) AS t(id, name);SQLite也支持類(lèi)似的語(yǔ)句SELECT * FROM (VALUES (1, 華東), (2, 華北), (3, 華南) );這種寫(xiě)法比一串UNION ALL干凈不少尤其適合行數(shù)固定的配置類(lèi)數(shù)據(jù)。但它的兼容性問(wèn)題比較明顯MySQL老版本不支持獨(dú)立的VALUES語(yǔ)句Oracle傳統(tǒng)版本也不支持把VALUES放在FROM后面當(dāng)派生表用。如果你寫(xiě)代碼要跨數(shù)據(jù)庫(kù)發(fā)布或者你不確定對(duì)方數(shù)據(jù)庫(kù)版本最穩(wěn)妥的方案仍然是UNION ALL。我自己在實(shí)際項(xiàng)目里有一個(gè)習(xí)慣代碼評(píng)審時(shí)看到UNION ALL串十行以上我就會(huì)建議改成CTE或臨時(shí)表因?yàn)榭勺x性已經(jīng)不行了。常量記錄的位置應(yīng)該在“少量、臨時(shí)、一次性”而不是承載幾百行配置數(shù)據(jù)。3.3 遞歸CTE生成序列動(dòng)態(tài)多行記錄除了手工枚舉固定行還有一種“動(dòng)態(tài)多條常量記錄”的玩法用遞歸CTE生成序列。比如要生成1到10的數(shù)字序列WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n 10 ) SELECT n FROM seq;這在以PostgreSQL、MySQL 8.0為代表的平臺(tái)上寫(xiě)法一致。SQL Server和Oracle的遞歸CTE不需要RECURSIVE關(guān)鍵字直接寫(xiě)WITH seq (n) AS (...)。實(shí)際工作中我常用遞歸CTE生成連續(xù)日期序列用于補(bǔ)全報(bào)表時(shí)間軸WITH RECURSIVE dates (d) AS ( SELECT DATE(2024-01-01) UNION ALL SELECT d INTERVAL 1 DAY FROM dates WHERE d DATE(2024-01-31) ) SELECT d FROM dates;關(guān)于遞歸CTE有幾個(gè)坑必須提醒終止條件必須寫(xiě)對(duì)。忘了寫(xiě)WHERE就是無(wú)限遞歸輕則查詢(xún)卡死重則打爆數(shù)據(jù)庫(kù)。MySQL里可以設(shè)置cte_max_recursion_depth作為兜底但千萬(wàn)別指望它。遞歸的起始行是常量但遞歸出來(lái)的每一行本質(zhì)上是通過(guò)上一層計(jì)算得到的所以它已經(jīng)不是嚴(yán)格意義上的“常量記錄”而是“規(guī)則生成的序列”。它和手工枚舉的定位不同適用于數(shù)據(jù)量大、有規(guī)律的場(chǎng)景。3.4 三種方案怎么選方案適用行數(shù)跨平臺(tái)兼容性可讀性典型場(chǎng)景UNION ALL1到幾十行最好幾乎所有數(shù)據(jù)庫(kù)都支持行數(shù)多時(shí)偏啰嗦月份表、狀態(tài)映射、少量測(cè)試數(shù)據(jù)值構(gòu)造器VALUES1到幾十行視數(shù)據(jù)庫(kù)而定新版本支持好簡(jiǎn)潔同一平臺(tái)下的固定配置遞歸CTE幾十到上萬(wàn)行主流數(shù)據(jù)庫(kù)均支持適合有規(guī)律的數(shù)據(jù)連續(xù)日期、遞增序列、層級(jí)數(shù)據(jù)4. 主流數(shù)據(jù)庫(kù)下常量記錄的寫(xiě)法差異速查4.1 一張表看懂各平臺(tái)差異我平時(shí)主要接觸MySQL、Oracle、PostgreSQL、SQL Server也用過(guò)SQLite和達(dá)夢(mèng)這里整理成一張表供參考數(shù)據(jù)庫(kù)SELECT 1要不要FROM值構(gòu)造器寫(xiě)法遞歸CTE關(guān)鍵字MySQL 5.7不需要不支持獨(dú)立VALUES只能INSERT里用不支持WITHMySQL 8.0不需要VALUES ROW(1,a), ROW(2,b)WITH RECURSIVEOracle需要FROM DUAL傳統(tǒng)版本常用UNION ALLWITH cte AS (...) 遞歸不用RECURSIVEPostgreSQL不需要SELECT * FROM (VALUES ...) AS t(...)WITH RECURSIVESQL Server不需要SELECT * FROM (VALUES ...) AS t(...)WITH cte AS (...)不用RECURSIVESQLite不需要支持VALUES派生表WITH RECURSIVE達(dá)夢(mèng)視兼容模式通常需要FROM DUAL建議UNION ALL兼容Oracle風(fēng)格這張表不需要背但值得收藏。你只要記住一個(gè)大原則寫(xiě)之前先確認(rèn)平臺(tái)別把一種數(shù)據(jù)庫(kù)的SQL習(xí)慣原樣搬到另一種上。4.2 為什么MySQL的SELECT可以沒(méi)有FROM不少新人會(huì)疑惑SELECT不是查詢(xún)表嗎怎么可以不寫(xiě)FROM這其實(shí)涉及SQL語(yǔ)法設(shè)計(jì)上的一個(gè)歷史特點(diǎn)。MySQL的語(yǔ)法從一開(kāi)始就允許SELECT省略FROM子句此時(shí)相當(dāng)于在一張“隱式的單行虛擬表”上做計(jì)算所以SELECT 1、SELECT NOW()都能直接跑。SQL Server同樣允許PostgreSQL、SQLite也允許。唯獨(dú)Oracle因?yàn)檎Z(yǔ)法樹(shù)和歷史包袱必須引入DUAL這個(gè)顯式的虛擬表來(lái)兜底。4.3 順帶說(shuō)一句熱詞里的SELECT TOP最近看到有人搜select top 1000 * from [dbo].[dc_ods_rkyxjc_lotdatacollection]這是SQL Server系工具自動(dòng)生成的“選擇前1000行”腳本。TOP是SQL Server的方言關(guān)鍵字MySQL對(duì)應(yīng)LIMITOracle對(duì)應(yīng)FETCH FIRST 1000 ROWS ONLY或ROWNUM。這類(lèi)差異和常量記錄本沒(méi)有直接關(guān)系但恰恰說(shuō)明一個(gè)問(wèn)題數(shù)據(jù)庫(kù)方言無(wú)處不在你在這個(gè)平臺(tái)寫(xiě)順手的SQL換到另一個(gè)平臺(tái)可能連跑都跑不起來(lái)。了解字段和SQL構(gòu)造差異是吃這碗飯的基本功。4.4 函數(shù)名差異也是常見(jiàn)陷阱回到常量記錄做表達(dá)式計(jì)算的場(chǎng)景很多函數(shù)在不同數(shù)據(jù)庫(kù)里名字完全不同功能MySQLOracleSQL ServerPostgreSQL當(dāng)前時(shí)間NOW()SYSDATEGETDATE()NOW()字符串拼接CONCAT()日期加天數(shù)DATE_ADD()date 1DATEADD()date INTERVAL 1 day這些差異用常量記錄一條條試是最快的驗(yàn)證方式。你不需要把文檔背下來(lái)但你要知道“怎么快速驗(yàn)證”。5. 進(jìn)階玩法常量記錄與真實(shí)業(yè)務(wù)查詢(xún)的組合5.1 報(bào)表補(bǔ)零用常量表當(dāng)基準(zhǔn)維度回到開(kāi)頭的報(bào)表需求。假設(shè)有一張sales_order表字段有order_date和amount要按月份統(tǒng)計(jì)2024年第一季度的銷(xiāo)售額沒(méi)有訂單的月份也要顯示0。用常量記錄構(gòu)造月份基準(zhǔn)表SELECT dm.month, COALESCE(SUM(s.amount), 0) AS total_amount FROM ( SELECT 2024-01 AS month UNION ALL SELECT 2024-02 UNION ALL SELECT 2024-03 ) dm LEFT JOIN sales_order s ON DATE_FORMAT(s.order_date, %Y-%m) dm.month GROUP BY dm.month ORDER BY dm.month;關(guān)鍵點(diǎn)在于常量表放在LEFT JOIN的左側(cè)這樣即使右側(cè)沒(méi)有匹配的訂單記錄左側(cè)月份仍然會(huì)保留下來(lái)配合COALESCE把NULL轉(zhuǎn)成0。如果沒(méi)有這張常量表直接對(duì)sales_order分組2024-02這個(gè)沒(méi)訂單的月份就“憑空消失”了。業(yè)務(wù)字段多時(shí)也可以先用月份常量表生成時(shí)間序列再在后續(xù)查詢(xún)里多次JOIN這種“先造基準(zhǔn)再關(guān)聯(lián)事實(shí)”的思路是做報(bào)表的同學(xué)必須熟練掌握的。5.2 用常量記錄當(dāng)“臨時(shí)字典表”SQL里經(jīng)常要給狀態(tài)碼做翻譯。比如訂單表的status字段1表示待支付2表示已支付3表示已發(fā)貨。你當(dāng)然可以用CASE WHENSELECT status, CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已發(fā)貨 END AS status_name FROM orders;但如果多個(gè)查詢(xún)都要用每處寫(xiě)一遍CASE很啰嗦。這時(shí)可以構(gòu)造一個(gè)臨時(shí)的狀態(tài)字典SELECT o.id, o.status, d.status_name FROM orders o LEFT JOIN ( SELECT 1 AS status_cd, 待支付 AS status_name UNION ALL SELECT 2, 已支付 UNION ALL SELECT 3, 已發(fā)貨 ) d ON o.status d.status_cd;這樣字典和主查詢(xún)分離代碼清晰多了。臨時(shí)性、小規(guī)模的映射直接用常量記錄不值得為它建一張正式字典表。5.3 INSERT INTO SELECT從常量記錄寫(xiě)到業(yè)務(wù)表有時(shí)候不只要“查”常量記錄還要把它插入正式表。常見(jiàn)寫(xiě)法是INSERT INTO region (id, name) VALUES (1, 華東), (2, 華北), (3, 華南);也可以用常量SELECT的方式INSERT INTO region (id, name) SELECT 1, 華東 UNION ALL SELECT 2, 華北 UNION ALL SELECT 3, 華南;INSERT INTO SELECT的優(yōu)勢(shì)是可以通過(guò)WHERE條件控制插入邏輯比如防止重復(fù)插入INSERT INTO region (id, name) SELECT 1, 華東 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM region WHERE id 1);這在初始化數(shù)據(jù)、寫(xiě)批量腳本時(shí)特別實(shí)用。如果要插入上萬(wàn)行測(cè)試數(shù)據(jù)就不再適合手工枚舉常量了用遞歸CTE批量生成是更高效的方式INSERT INTO seq_table (n) WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n 10000 ) SELECT n FROM seq;5.4 把常量記錄包裝成參數(shù)集在存儲(chǔ)過(guò)程或報(bào)表開(kāi)發(fā)里我偶爾會(huì)把“參數(shù)”包裝成常量子查詢(xún)讓整條SQL看起來(lái)更結(jié)構(gòu)化。比如SELECT * FROM ( SELECT debug AS log_level, 1 AS enabled ) param WHERE param.enabled 1;或者配合變量做動(dòng)態(tài)判斷。雖然這類(lèi)需求不算高頻但它體現(xiàn)了常量記錄的通用性常量記錄不只是“臨時(shí)測(cè)試數(shù)據(jù)”它可以作為一個(gè)獨(dú)立的邏輯單元嵌入復(fù)雜SQL中讓SQL的可讀性和可維護(hù)性都更好。6. 常量記錄常見(jiàn)報(bào)錯(cuò)與排查記錄6.1 高頻報(bào)錯(cuò)對(duì)照表報(bào)錯(cuò)信息出現(xiàn)場(chǎng)景解決方案ORA-00923: FROM keyword not found where expectedOracle/達(dá)夢(mèng)中寫(xiě)SELECT 1漏了FROM加FROM DUALERROR 1064 (42000): You have an error in your SQL syntax在MySQL老版本里用了VALUES ROW(...)改用UNION ALL或確認(rèn)版本≥8.0.19The used SELECT statements have a different number of columnsUNION ALL各分支列數(shù)不一致統(tǒng)一列數(shù)空缺補(bǔ)NULLORA-01790: expression must have same datatype as corresponding expressionOracle中UNION ALL列類(lèi)型不兼容用CAST顯式統(tǒng)一類(lèi)型ORA-00904: ABC: invalid identifierOracle中把字符串寫(xiě)成了雙引號(hào)改用單引號(hào)亂碼中文常量輸出為問(wèn)號(hào)檢查客戶(hù)端字符集、NLS_LANG或連接參數(shù)遞歸CTE超出深度限制遞歸終止條件沒(méi)寫(xiě)對(duì)或?qū)懱顧z查WHERE終止條件或調(diào)大深度限制6.2 幾個(gè)容易忽略的細(xì)節(jié)**UNION ALL的結(jié)果列名只看第一個(gè)分支。**后續(xù)分支起什么別名都不影響結(jié)果集列名。這一點(diǎn)經(jīng)常被忽略尤其是多個(gè)分支列名不一樣時(shí)最終結(jié)果列名會(huì)讓人困惑。**字符串常量寬度按第一個(gè)分支推定。**比如第一個(gè)分支是SELECT a后面拼接SELECT 很長(zhǎng)的一段字符串在某些數(shù)據(jù)庫(kù)里可能出現(xiàn)截?cái)嗷虮容^異常。穩(wěn)妥起見(jiàn)關(guān)鍵列用CAST(xxx AS VARCHAR(50))顯式聲明類(lèi)型。Oracle里的雙引號(hào)是雷區(qū)。SELECT hello FROM DUAL不是查詢(xún)字符串hello而是查詢(xún)列hello直接報(bào)ORA-00904。這是從MySQL轉(zhuǎn)Oracle的同學(xué)最容易踩的坑。**值構(gòu)造器的行數(shù)別搞太大。**值構(gòu)造器適合少量固定數(shù)據(jù)行數(shù)幾十上百時(shí)可以但如果要幾千行SQL語(yǔ)句本身的解析和內(nèi)存開(kāi)銷(xiāo)就不劃算了這時(shí)候用遞歸CTE或者臨時(shí)表更合理。6.3 一次真實(shí)的翻車(chē)案例聊一個(gè)我親眼見(jiàn)過(guò)的案例。有同事在MySQL里構(gòu)造一批測(cè)試日期寫(xiě)成SELECT 2024-02-29 AS d UNION ALL SELECT 2024-02-30 UNION ALL SELECT 2024-03-01;單看每條SELECT都能跑拼接起來(lái)也不報(bào)錯(cuò)。但下游程序解析時(shí)遇到2024-02-30這個(gè)不存在的日期就崩了。排查了半天發(fā)現(xiàn)是常量記錄本身造錯(cuò)了數(shù)據(jù)。這個(gè)案例提醒我常量記錄雖然是“造”出來(lái)的數(shù)據(jù)但它同樣需要業(yè)務(wù)正確性校驗(yàn)。日期、金額、狀態(tài)值這類(lèi)字段造數(shù)時(shí)就要想清楚業(yè)務(wù)規(guī)則不能因?yàn)椴皇遣檎鎸?shí)表就隨意填。6.4 我的排查順序真遇到常量記錄相關(guān)的SQL運(yùn)行異常我一般按這個(gè)順序排查先在腦子里過(guò)一遍數(shù)據(jù)庫(kù)類(lèi)型和版本。MySQL 5.7和MySQL 8.0、Oracle和達(dá)夢(mèng)可能踩的坑完全不一樣。單獨(dú)執(zhí)行其中一個(gè)分支看是否報(bào)錯(cuò)。如果單條能跑通問(wèn)題多半出在UNION ALL的列數(shù)或類(lèi)型上。檢查第一個(gè)分支的列名和類(lèi)型尤其注意隱式轉(zhuǎn)換。檢查字符集相關(guān)的設(shè)置。把結(jié)果逐步縮小先去掉WHERE再去掉最后一個(gè)UNION ALL分支二分定位。這套順序幫我快速定位過(guò)不少問(wèn)題也分享給你參考。最后分享一點(diǎn)使用心得這個(gè)技巧看起來(lái)小但對(duì)提升SQL開(kāi)發(fā)效率是真的有效。我個(gè)人在團(tuán)隊(duì)里帶新人時(shí)經(jīng)常把常量記錄當(dāng)作“SQL沙盒”來(lái)用不準(zhǔn)備任何測(cè)試表直接讓新同學(xué)用SELECT構(gòu)造一批數(shù)據(jù)然后練習(xí)聚合、子查詢(xún)、CTE和窗口函數(shù)。這個(gè)訓(xùn)練方式不需要維護(hù)測(cè)試庫(kù)也不影響任何業(yè)務(wù)數(shù)據(jù)練習(xí)成本幾乎為零。你也可以試試在常用的數(shù)據(jù)庫(kù)客戶(hù)端里用今天講的幾種方式造幾行常量記錄玩熟了之后你會(huì)發(fā)現(xiàn)很多原來(lái)要建臨時(shí)表的場(chǎng)景其實(shí)一條SELECT就夠了。