我造了個(gè)輪子，完整開源！
大家好，我是魚皮，最近為了幫助自己完成寫超長 SQL 語句（幾千行）的工作，我花幾個(gè)小時(shí)開發(fā)了一個(gè)小工具 —— 結(jié)構(gòu)化 SQL 生成器，可以使用扁平的 JSON 結(jié)構(gòu)來輕松生成層層嵌套的、復(fù)雜的 SQL，從而大幅提高寫 SQL 的效率！
工具地址：http://sql.yupi.icu ，很好記吧~
工具如圖，在左側(cè)輸入 JSON、右側(cè)自動(dòng)生成 SQL 語句，保證重復(fù)的語句只用寫一次即可，更利于維護(hù)：
關(guān)于這個(gè)項(xiàng)目的背景、我為什么要開發(fā)這個(gè)工具，可以先看下面這個(gè)視頻了解下（看封面感覺就很慘 hh）：
視頻地址：https://www.bilibili.com/video/BV1qa411J7vh/
雖然這個(gè)工具是為了自己完成需求開發(fā)的，但和具體業(yè)務(wù)無關(guān)，所以我把它完整開源了，也是感謝大家的 star：
開源地址：https://github.com/liyupi/sql-generator
那在本文中主要給大家介紹這個(gè)項(xiàng)目的作用、用法以及大致的實(shí)現(xiàn)原理，由于項(xiàng)目本身并不復(fù)雜，純 browser 前端 + 遞歸解析生成算法實(shí)現(xiàn)，所以還是比較適合學(xué)習(xí)前端和算法的朋友學(xué)習(xí)參考的~
項(xiàng)目作用
將 SQL 的編寫邏輯 結(jié)構(gòu)化，像寫文章大綱一樣編寫和閱讀 SQL重復(fù)的 SQL 只需編寫一次 ，SQL 變動(dòng)時(shí)修改一處即可 可以針對(duì)某部分 SQL 進(jìn)行傳參和調(diào)試 查看 SQL 語句的引用樹和替換過程，便于分析理解 SQL
應(yīng)用場景
如果你要寫一句復(fù)雜的 SQL，且 SQL 中很多代碼是 相似 但又不相同的。那么推薦使用該工具，可以不用重復(fù)編寫 SQL，更有利于修改、維護(hù)和理解。
尤其是在大數(shù)據(jù)分析場景下，經(jīng)常會(huì)有編寫復(fù)雜 SQL 的需求。
之所以會(huì)有這個(gè)輪子，也正是因?yàn)轸~皮在工作中要寫一句長達(dá) 3000 行的 SQL 來離線分析數(shù)據(jù)，手寫真的人要瘋了！
當(dāng)然，這玩意本質(zhì)上就是一個(gè)文本解析 + 替換工具嘛，所以你也完全可以把它當(dāng)做一個(gè) 重復(fù)代碼生成器 ~
有同學(xué)會(huì)說：不是有存儲(chǔ)過程么？咳咳，存儲(chǔ)過程在大數(shù)據(jù)引擎上的通用性嘛。。。
示例
需求：計(jì)算 id = 1 和 id = 2 的兩位同學(xué)的身高差
SQL 大概是這樣的：
select?(s1.height?-?s2.height)?as?身高差
from?
(select?*?from?student?where?id?=?1)?s1,
(select?*?from?student?where?id?=?2)?s2
顯然，上述 SQL 中學(xué)生表查詢了 2 次，而且除了查詢的 id 不同外，查詢邏輯完全一致！
如果后面查詢學(xué)生的邏輯發(fā)生修改，那么以上 2 個(gè)子查詢都要同時(shí)修改，不利于維護(hù)。
而使用本工具，只需編寫如下 JSON，就能自動(dòng)生成完整的 SQL 了：
{
????"main":?"select?@身高差()?from?(@學(xué)生表(id?=?1))?s1,?(@學(xué)生表(id?=?2))?s2",
????"身高差":?"(s1.height?-?s2.height)?as?身高差",
????"學(xué)生表":?"select?*?from?student?where?id?=?#{id}"
}
結(jié)果如圖：
通過類似 函數(shù)調(diào)用 + 傳參 的方式，我們無需重復(fù)編寫 SQL，而且整個(gè) SQL 的邏輯更清晰！
當(dāng)然，以上只是一個(gè)示例，真實(shí)大數(shù)據(jù)離線分析的場景下，SQL 可比這復(fù)雜 N 倍！
如果感興趣的話，歡迎往下看文檔，還有更復(fù)雜的示例~
項(xiàng)目優(yōu)勢
支持在線編輯 JSON 和 SQL，支持代碼高亮、語法校驗(yàn)、一鍵格式化、查找和替換、代碼塊折疊等，體驗(yàn)良好 支持一鍵生成 SQL 支持參數(shù)透傳，比如 @a(xx = #{yy})，yy 變量可傳遞給 @a 公式 支持嵌套傳參（將子查詢作為參數(shù)），比如 @a(xx = @b(yy = 1)) 不限制用戶在 JSON 中編寫的內(nèi)容，因此該工具也可以作為重復(fù)代碼生成器來使用 支持查看 SQL 語句的調(diào)用樹和替換詳情，便于分析引用關(guān)系
文檔
可以把下面的代碼放到生成器中試試，一下就明白如何使用啦~
{
??"main":?"必填,?代碼從這里開始生成,?用?@規(guī)則名()?引用其他語句",
??"規(guī)則名":?"可以編寫任意?SQL?語句?@規(guī)則名2()?@動(dòng)態(tài)傳參(a?=?求給?|||?b?=?star)",
??"規(guī)則名2":?{
????"sql":?"用?#{參數(shù)名}?指定可被替換的值",
????"params":?{
??????"參數(shù)名":?"在?params?中指定靜態(tài)參數(shù),?會(huì)優(yōu)先被替換"
????}
??},
??"動(dòng)態(tài)傳參":?"#{a}魚皮#"
}
補(bǔ)充說明
對(duì)象鍵：定義 SQL 生成規(guī)則名稱，main 表示入口 SQL，從該 SQL 語句開始生成。
對(duì)象值：定義具體生成規(guī)則?？梢允?SQL 字符串或者對(duì)象。
sql：定義模板 SQL 語句，可以是任意字符串，比如一組字段、一段查詢條件、一段計(jì)算邏輯、完整 SQL 等。
params：靜態(tài)參數(shù)，解析器會(huì)優(yōu)先將該變量替換到當(dāng)前語句的 #{變量名} 中
#{xxx}：定義可被替換的變量，優(yōu)先用當(dāng)前層級(jí) params 替換，否則由外層傳遞
@xxx(yy = 1 ||| zz = #{變量})：引用其他 SQL，可傳參，參數(shù)可再用變量來表示，使用 |||（三個(gè)豎線）來分隔參數(shù)。
復(fù)雜示例
需求：用一句 SQL 查詢出以下表格
這個(gè)表格的難點(diǎn)在哪？
查匯總和查明細(xì)的粒度不同，不能用 group by 區(qū)分，只能用 union（紅色） 分類列中不同行的數(shù)據(jù)有交叉，不能用 group by 區(qū)分，只能用 union 每一列由多張表共同 join 而成，且不同分類可關(guān)聯(lián)的表不同，須進(jìn)行區(qū)分（灰色表示無法關(guān)聯(lián)），并將缺失的字段補(bǔ)齊（否則無法 union） 不同行的同一列計(jì)算公式可能不同（藍(lán)色） 不同列的過濾條件不同（比如最后兩列墨綠色是要查全校，其余列只查 1 年級(jí)） 要查詢同環(huán)比，只能用 2 份完整的數(shù)據(jù)去 join 然后錯(cuò)位計(jì)算來得出
顯然，這個(gè)表中很多查詢邏輯是重復(fù)但又不同的。
這么算下來，最后這個(gè) SQL 中到底會(huì)包含多少個(gè)基礎(chǔ)表的 select 呢？每個(gè)基礎(chǔ)表查詢要重復(fù)編寫多少遍呢？大家可以思考一下。
然而，這個(gè)表格也只是魚皮對(duì)實(shí)際需求簡化后才得來的，實(shí)際需求比這還復(fù)雜幾倍！
可想而知，人工寫有多惡心？！
但是使用本工具，最內(nèi)層的 SQL 表查詢只需要用 JSON 來定義一次，就可以重復(fù)使用：
其實(shí)和 with 語法是很像的，但比 with 更靈活和通用
技術(shù)實(shí)現(xiàn)
這個(gè)工具使用和 JSON 相性最好的 JavaScript 來實(shí)現(xiàn)，只需編寫一份邏輯 JS 文件，可同時(shí)應(yīng)用于 browser 和 server 端。
功能比較輕量，因此我選擇優(yōu)先在純 browser 端實(shí)現(xiàn)。
前端使用 Vue3 + Vite + Ant Design Vue 開發(fā)界面，選用 Monaco Editor 實(shí)現(xiàn)代碼編輯、高亮、格式化等功能，使用 TypeScript + ESLint 保證代碼規(guī)范。
SQL 生成邏輯如下：
JSON 字符串轉(zhuǎn)對(duì)象 從入口開始，先替換 params 靜態(tài)參數(shù)，得到當(dāng)前層解析 對(duì) @xxx 語法進(jìn)行遞歸解析，遞歸解析時(shí)，優(yōu)先替換靜態(tài)參數(shù)，再替換外層傳來的調(diào)用參數(shù) 得到最終 SQL
解析器原本采用正則非貪婪替換方式實(shí)現(xiàn)，但無法實(shí)現(xiàn)嵌套調(diào)用，比如 @a(xx = @b()) ，會(huì)被識(shí)別為 @a(xx = @b() ，匹配到了最近的右括號(hào)。因此針對(duì)括號(hào)嵌套的情況對(duì)子查詢替換算法做了優(yōu)化，已支持包含括號(hào)語句的嵌套調(diào)用。
OK，以上就是本期分享，希望這個(gè)項(xiàng)目對(duì)大家工作以及學(xué)習(xí)編程有幫助吧~
對(duì)了，今天晚上會(huì)在星球里直播分享之前開源的面試鴨項(xiàng)目的完整創(chuàng)造、開發(fā)、運(yùn)營過程和經(jīng)驗(yàn)，大家感興趣的話，歡迎加入 魚皮的編程知識(shí)星球（點(diǎn)擊了解詳情），和 8000 名小伙伴們一起交流學(xué)習(xí)，向魚皮和大廠同學(xué) 1 對(duì) 1 提問、幫你制定學(xué)習(xí)計(jì)劃不迷茫、跟著魚皮直播做項(xiàng)目（往期項(xiàng)目可無限回看）。
往期推薦