超前完成?。?！
原計(jì)劃 7 天的工作，1 小時(shí)完成！是我開掛了么？
大家好，我是魚皮，今天分享自己工作中的小歡喜，也希望給大家?guī)硪恍┚幊躺系乃伎肌?/p>
孽起
事情是這樣的，最近在開發(fā)一個(gè) 僅限內(nèi)部使用 的數(shù)據(jù)分析系統(tǒng)，我做后端，另外一個(gè)哥們做前端。
我們要實(shí)現(xiàn)的功能是：用戶可以在界面上任意輸入 SQL 數(shù)據(jù)查詢語句，并將它保存下來，生成一個(gè)數(shù)據(jù)看板。以后用戶可以隨時(shí)打開這個(gè)看板來瀏覽和分析 SQL 查詢出的最新數(shù)據(jù)，而無需反復(fù)輸入 SQL 語句。
舉個(gè)例子！
假如我們有一個(gè)很大的數(shù)據(jù)倉庫，存了海量的數(shù)據(jù)，有男有女：
產(chǎn)品同學(xué)可能只想對(duì)部分?jǐn)?shù)據(jù)進(jìn)行分析，于是寫了下列 SQL 語句來查詢所有男性：
select * from table
where 性別 = '男';
將該 SQL 語句保存，得要一個(gè) “男性數(shù)據(jù)看板”，之后，就可以在該看板頁面查看和分析所有男性數(shù)據(jù)啦。
要實(shí)現(xiàn)這個(gè)需求，一種最簡(jiǎn)單的方式就是，直接將用戶在界面上輸入的 SQL 字符串發(fā)給后端保存，需要看數(shù)據(jù)時(shí)，后端再用這個(gè)字符串從數(shù)據(jù)庫中查詢數(shù)據(jù)即可。
寫 SQL 配置流程：
打開看板瀏覽數(shù)據(jù)流程：
既然是允許用戶任意輸入的，那么問題就來了。
假如小粗心不小心打錯(cuò)了 SQL 語句：
# 錯(cuò)誤 ?
sleetc * from table
# 正確 ?
select * from table;
又或者小迷糊記錯(cuò)了 SQL 的語法：
# 錯(cuò)誤 ?
select table from a;
# 正確 ?
select a from table;
甚至是小搗蛋不按規(guī)矩出牌，輸入一些亂七八糟的字符：
# 錯(cuò)誤 ?
select q^q from table;
# 正確 ?
select q from table;
如果把這些錯(cuò)誤的 SQL 語句發(fā)給后端，后端直接用它來查數(shù)據(jù)庫，必然會(huì)導(dǎo)致查詢錯(cuò)誤，查了個(gè)寂寞。
對(duì)于實(shí)時(shí)查詢來說，這沒啥問題，查詢失敗了大不了再修改語句查詢一次唄。
但我要做的需求是允許用戶將查詢語句作為看板配置永久保存下來，便于后續(xù)自動(dòng)查詢數(shù)據(jù)。而且寫 SQL 配置的用戶可能和看數(shù)據(jù)的用戶不是同一個(gè)人，如果小 A 在配置時(shí)就沒有發(fā)現(xiàn) SQL 語句是錯(cuò)誤的，那到時(shí)候來查看數(shù)據(jù)看板的小 B 就會(huì)一臉懵逼，咋特么看不到數(shù)據(jù)呢？是數(shù)據(jù)還沒準(zhǔn)備好，還是查詢出來的數(shù)據(jù)就是 0 行呢，還是說我沒有瀏覽權(quán)限呢？
他根本不會(huì)想到，已經(jīng)配置成功的 SQL 語句，竟然是錯(cuò)誤的！
因此，需要在配置時(shí)就對(duì)用戶輸入的 SQL 進(jìn)行校驗(yàn)，看看它是否合法。
做個(gè)比喻，前端是一名底層員工（無知的小開發(fā)），后端是小組長(zhǎng)，數(shù)據(jù)庫是大老板。小開發(fā)做了個(gè)需求之后，應(yīng)該先交給小組長(zhǎng)檢查，小組長(zhǎng)說沒問題之后，再給大老板驗(yàn)收。
那如何校驗(yàn) SQL 語句呢？
因?yàn)橛脩舻妮斎胧峭耆淮_定的，他們寫的 SQL 語句可能又臭又長(zhǎng)。所以我剛想到這個(gè)需求，就覺得腦闊疼，感覺賊麻煩，不保守地給自己計(jì)劃 7 天完成。
大家可以先想想如果讓你實(shí)現(xiàn) SQL 語句校驗(yàn)，你會(huì)怎么做？
下面是我的思考過程。
絞盡腦汁
首先，我們要明確：是在前端，還是在后端校驗(yàn)？
其實(shí)，無論在前端還是后端，校驗(yàn)都至關(guān)重要，可以有效防止很多錯(cuò)誤的輸入。但由于最終是后端程序來直接操作數(shù)據(jù)庫，可以說是數(shù)據(jù)庫的最后一道防線，因此建議 將校驗(yàn)邏輯寫在后端。數(shù)據(jù)庫很嫩，他自己把握不住，需要后端程序來幫他把握把握。
那如何在后端去校驗(yàn) SQL 呢？
找現(xiàn)成的
首先，遇事不決問百度，不行再去搜倉庫?，F(xiàn)在網(wǎng)上的開源項(xiàng)目很多，那不妨搜搜看，有沒有現(xiàn)成的 SQL 校驗(yàn)類庫。最理想的情況是，有一個(gè)工具類函數(shù)，我傳給他 SQL 字符串作為參數(shù)，他直接返回給我 true 或 false。
然而，我發(fā)現(xiàn)自己在想 peach，各種開源項(xiàng)目都搜遍了，沒有找到能開箱即用的 PostgreSQL 校驗(yàn)庫。
看來，只能自己動(dòng)手，豐衣足食了。
模擬查詢
要自己實(shí)現(xiàn)校驗(yàn)，我第一時(shí)間想到的方法是模擬一次查詢。用戶剛剛寫好 SQL 語句后，即便他現(xiàn)在并不需要瀏覽數(shù)據(jù)查詢結(jié)果，我也可以在他保存配置時(shí)，用他寫的 SQL 去查詢一次數(shù)據(jù)庫。假如查詢沒報(bào)錯(cuò)，就說明 SQL 語句合法，允許保存。
這種方式最直接，也最方便，基本沒有任何的開發(fā)成本，賊香！就好比一名小開發(fā)寫完?duì)€代碼后，交給小組長(zhǎng)，但小組長(zhǎng)不講武德，自己看不懂代碼（也可能是不想看），索性就把代碼直接丟給大老板，大老板說沒問題了，小開發(fā)再上線。小組長(zhǎng)狂喜！
但是，有個(gè)致命的問題：用戶在配置 SQL 語句時(shí)，數(shù)據(jù)表可能還沒準(zhǔn)備好，無論語句是否正確，都無法查出數(shù)據(jù)。
所以，在將 SQL 語句直接發(fā)向數(shù)據(jù)庫前，要先確認(rèn)數(shù)據(jù)表是否存在。若存在，可以通過模擬查詢的方式校驗(yàn)；若不存在，只能在后端通過其他方式校驗(yàn)。
就好比小組長(zhǎng)想把爛代碼直接丟給大老板時(shí)，大老板不在，這時(shí)，只能靠自己來檢查了。
正則表達(dá)式
要在程序中校驗(yàn)字符串，我最先想到的是 正則表達(dá)式，即用特定語法來匹配同一類具有相似規(guī)則的字符串，常見的有校驗(yàn)手機(jī)號(hào)、校驗(yàn)郵箱、校驗(yàn)身份證等。
在使用正則表達(dá)式進(jìn)行校驗(yàn)前，我們要先對(duì)字符串進(jìn)行分析，看它們是否具有相似的結(jié)構(gòu)、哪些部分相似。比如 QQ 郵箱，結(jié)構(gòu)很規(guī)整，基本都是 [email protected]，因此，可以用正則表達(dá)式 /^\[email protected]$/ 來校驗(yàn)。
回過頭來看我們的需求，要校驗(yàn)的是 SQL 語句，似乎也比較規(guī)整，無非就是查詢哪個(gè)表、選哪些行、選哪些列、怎么排序等等，大概的結(jié)構(gòu)是這樣：
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
根據(jù)這個(gè)結(jié)構(gòu)，很容易編寫出粗略的正則表達(dá)式。但是，數(shù)據(jù)業(yè)務(wù)中的 SQL 語句可比這復(fù)雜得多，包含各種四則運(yùn)算、IF ... ELSE 條件判斷、CASE ... WHEN ... 分支，字符串、日期類型處理函數(shù)，還有各種聚合函數(shù)等，比如下面這個(gè) SQL：
select a as b,
sum(case when (false) then d / a else 2 end) as c
from table
where a = 1
group by b, c;
如果以上這些零碎的語法都用正則表達(dá)式來匹配，可就太麻煩了！想想腦闊又疼了。
解析表達(dá)式
既然編寫一套正則表達(dá)式比較麻煩，那我能想到的就只有把 SQL 打的稀吧碎了?？梢杂妙愃凭幾g原理語法分析的方式，搞一個(gè) SQL 解析器，將完整的 SQL 語句轉(zhuǎn)換為一顆抽象語法樹（AST），每個(gè)節(jié)點(diǎn)都是一個(gè)小表達(dá)式，從而能夠更精細(xì)地校驗(yàn) SQL 語句的合法性。
如果自己從零開始實(shí)現(xiàn)這樣一套 SQL 解析器，實(shí)在是太麻煩了，而且不具備一定的專業(yè)知識(shí)也寫不出來。因此，我先到網(wǎng)上去搜索一番，看看有沒有現(xiàn)成的解析器引擎。
這次的搜索結(jié)果還算滿意，找到了一些知名解析引擎，但是看了一圈，讀了半天，發(fā)現(xiàn)很難直接去使用他們的源碼。那委曲求全的方式就是照著他們的源碼自己寫一個(gè)解析器了。
想到這里，頭頂不僅感受到了一絲寒涼，感覺給自己估時(shí) 7 天都少了。
移花接木
第二天，我又思考了一下，網(wǎng)上有那么多現(xiàn)成的類庫，難道就沒有一個(gè)能滿足我的需求？即使沒有完全現(xiàn)成的，能不能找個(gè)相對(duì)好用的呢？
畢竟自己來寫這復(fù)雜的校驗(yàn)邏輯實(shí)在太麻煩了，所以我必須再掙扎一下！
于是，我掏出了御用小黃鴨，開始對(duì)著它念叨：SQL 校驗(yàn)、SQL 校驗(yàn)、SQL 校驗(yàn)。。。
我：什么時(shí)候會(huì)用到 SQL 校驗(yàn)?zāi)兀?/p>
小黃鴨：需要查數(shù)據(jù)庫的時(shí)候。
我：什么東西會(huì)去查數(shù)據(jù)庫呢？
小黃鴨：框架、數(shù)據(jù)庫連接池、或者代理。
我：那這些玩意在查數(shù)據(jù)庫的時(shí)候，會(huì)幫我們做校驗(yàn)么？
小黃鴨：校驗(yàn)校驗(yàn)，你就知道校驗(yàn)，你需要的功能一定是校驗(yàn)么？
等等，我好像恍然大悟了！
既然沒辦法直接搜到現(xiàn)成的 SQL 校驗(yàn)類庫，那不妨來個(gè) 移花接木，想一想其他的類庫中是否包含 SQL 解析功能，如果解析失敗，不就表示 SQL 非法，校驗(yàn)不通過么！
我開始回想自己以前用過的和訪問數(shù)據(jù)庫有關(guān)的技術(shù)，突然想到，阿里的 Druid 數(shù)據(jù)庫連接池類庫好像有一個(gè) SQL 語句格式化的功能，能把雜亂的 SQL 重新排版。既然能對(duì) SQL 格式化，是不是意味著，這個(gè)類庫有能力對(duì) SQL 語句進(jìn)行解析呢？
仔細(xì)一查 Druid 的文檔，發(fā)現(xiàn)還真有一個(gè)類叫 SQLUtils，這個(gè)類有一個(gè)方法叫 parseStatements，可以對(duì)多種不同的 SQL 方言進(jìn)行解析，比如 MySQL、PostgreSQL 等。
// 解析，接受 sql 語句和數(shù)據(jù)庫方言為參數(shù)
SQLUtils.parseStatements(sql, POSTGRESQL);
解析失敗時(shí)，會(huì)拋出異常，表示 SQL 語句非法，正好能夠滿足我的需求！
最終，我寫出的代碼如下：
try {
String sql = "select * from a";
SQLUtils.parseStatements(sql, POSTGRESQL);
return true;
} catch (ParserException e) {
LOGGER.error("解析失敗", e);
return false;
}
幾分鐘就寫完了代碼，然后又花了一些時(shí)間輸入各種 SQL 語句來測(cè)試，雖然只能實(shí)現(xiàn)基本的語法校驗(yàn)，但綜合衡量效果和成本上，我覺得已經(jīng)不錯(cuò)了，省下的大量時(shí)間可以繼續(xù)完善和優(yōu)化項(xiàng)目的其他代碼。
關(guān)鍵是，心不累了，頭發(fā)又支棱起來了！
通過這件事，帶給我三點(diǎn)思考：
在我們找項(xiàng)目代碼、找類庫的時(shí)候，如果沒辦法找到直接滿足需求的，那么可以把思維從整體轉(zhuǎn)向局部，想想在其他的項(xiàng)目中是否包含了你要找的功能。就像查詞典一樣，你要查單詞
apple，但是翻目錄只有首字母a，這個(gè)時(shí)候，就不能只盯著a看，而是要看到詞典里面的內(nèi)容，其實(shí)apple就藏在a之中。前人栽樹，后人乘涼，現(xiàn)在網(wǎng)上現(xiàn)成的項(xiàng)目代碼太多了，如果不是為了學(xué)習(xí)，很多東西沒必要自己再去實(shí)現(xiàn)一遍。
寫代碼時(shí)要注重積累，多學(xué)習(xí)和了解技術(shù)，并歸納總結(jié)到你的武器庫中，否則前人栽的樹你找不到，就可惜了。
當(dāng)然，有條件的話，前端也是可以加校驗(yàn)的，但目前沒啥必要，這里我們先用 CodeMirror 做一個(gè) SQL 代碼高亮來替代。
如果真的讓你實(shí)現(xiàn)前端 SQL 校驗(yàn)，你會(huì)怎么做呢？
我是魚皮，原創(chuàng)不易，如果覺得文章還不錯(cuò)的話，希望朋友們 點(diǎn)贊 + 在看 支持下，給俺點(diǎn)創(chuàng)作動(dòng)力。
往期推薦