「這段查詢語句太密集了,根本讀不下去……」

正式環境系統吐出的整段擠成一行的巨大 SQL;或是巢狀 JOIN 層層堆疊、宛如迷宮的預存程序。「這個條件是在哪裡分岔的?」——盯著這樣一堵 SQL 高牆,是許多人都有過的挫折經驗。

SQL 格式化工具 會把貼上的 SQL 完全在瀏覽器內重新排版,因此即使是機密的正式環境查詢語句,也絕不會外流。

SQL 格式化工具:如何讓查詢語句變得易讀,以及方言選擇為何重要

格式化前後對照

select users.id,users.name,count(orders.id) from users left join orders on users.id=orders.user_id where users.deleted_at is null group by users.id,users.name order by users.id desc;
SELECT
  users.id,
  users.name,
  COUNT(orders.id)
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE users.deleted_at IS NULL
GROUP BY
  users.id,
  users.name
ORDER BY users.id DESC;

光是把排版整理好,遺漏的條件、JOIN 的方向、彙總的粒度就會變得清楚很多。

為什麼「方言(dialect)」的選擇是必要的,而不是可選項

SQL 格式化工具必須先進行 tokenize 並理解語法,才能決定該在哪裡換行、如何縮排。這代表各方言特有的擴充語法,會直接影響格式化能否成功。

方言差異 標準 SQL MySQL PostgreSQL BigQuery
識別字引號 "name" `name` "name" `project.dataset.table`
字串串接 || CONCAT() || ||
LIMIT 語法 LIMIT n LIMIT n LIMIT n OFFSET m LIMIT n
陣列/結構 非標準 不支援 ARRAY[...] STRUCT<...>

舉例來說,如果把 MySQL 用反引號包住的識別字當成標準 SQL 解析,token 的邊界可能會偏移,導致格式化結果整個亂掉。「格式化結果怪怪的」這類問題,大多數都是選錯方言造成的。 這個工具支援 PostgreSQL、MySQL、MariaDB、SQL Server、BigQuery 等主流方言,只要配合實際使用的資料庫選擇,切換方言時就會自動重新格式化。

格式化選項,以及對範本與預存腳本的支援

SQL 格式化工具有十一個決定輸出樣式的選項。

選項 可選值 效果
關鍵字大小寫 維持原樣 / 大寫 / 小寫 例如把 select 統一成 SELECT
縮排 2 個空格 / 4 個空格 / Tab 配合團隊的規範
AND / OR 的位置 行首 / 行尾 想讓新增或刪除條件時的差異更小,放行首比較適合
單行最大寬度 窄(30)/ 50 / 寬(80) 越小輸出越長,括號內也越容易換行
函式名稱大小寫 維持原樣 / 大寫 / 小寫 統一 COUNT(*) 與 count(*),可與關鍵字分開指定
資料型別大小寫 維持原樣 / 大寫 / 小寫 統一 VARCHAR(10) 與 varchar(10)
識別字(資料表・欄位名)大小寫 維持原樣 / 大寫 / 小寫 有加引號的識別字("Name"、`name`)不會更動
關鍵字對齊方式 標準 / 靠左 / 靠右 靠左、靠右(river 風格)會把 SELECT、FROM、WHERE 垂直對齊,讓內容從同一個位置開始
運算子空白 有 / 無 選「無」會變成 a=1、a+b 這樣緊貼
分號 行尾 / 獨立一行 選獨立一行時,語句後面會多一行只有 ; 的行
查詢之間的空行 1 / 2 / 3 格式化多個語句時,語句之間的空行數

當輸入的 SQL 看起來是其他方言的寫法(ON CONFLICT、TOP、AUTO_INCREMENT 等)時,會提示「這段 SQL 看起來是 PostgreSQL 的寫法」並建議切換。不會自動切換;若只用到各方言通用的寫法,則不會顯示任何提示。

實務中的 SQL 也會混入照原樣會造成語法錯誤的寫法。以下這些可以在不破壞內容的情況下格式化:

  • 範本變數:dbt、Jinja 的 {{ ref('orders') }}、{% if … %}、{# … #},以及 shell、JS 的 ${var},會被視為一個整體
  • SQL Server 的 GO:選擇 SQL Server(T-SQL)方言時,會以只有 GO 的那一行把腳本切成批次,並逐批次格式化。字串或註解中的 go 不會被視為分隔
  • MySQL 的 DELIMITER:含有 DELIMITER // 這類行的腳本,也會依各分隔符號切出敘述再格式化,因此 // 不會被拆開

相反方向的「壓縮」按鈕,會把空白、換行與註解壓成一行。字串常值的內容不會更動(註解會被刪除)。想把查詢以單行嵌進記錄檔或設定檔時使用。

SQL 審查時該檢查的重點

格式化之後,再多確認幾個項目,能進一步提升審查的品質。

  • WHERE 條件是否符合預期
  • JOIN 條件是否有遺漏
  • LEFT JOIN 與 INNER JOIN 的選用是否正確(完整說明請參考 SQL JOIN 完全參考)
  • GROUP BY 的彙總粒度是否符合預期
  • 需要的地方是否有加上 ORDER BY/LIMIT
  • 子查詢與 CTE 是否各自承擔明確的職責

從日誌擷取出來的 SQL,通常所有預留位置都已展開,因此特別容易變長。在閱讀前先格式化,能大幅縮短調查時間。

格式化不只是讓外觀變好看

真正的價值不在於美觀,而是體現在以下三個地方:

  1. 及早發現語法錯誤——解析失敗代表某處壞掉了,等於是執行前的免費健檢
  2. 讓 diff 更穩定——固定格式化規則後,查詢變更的 diff 只會反映真正有意義的修改
  3. 解讀機器產生的 SQL——ORM 吐出的單行查詢,格式化之後才看得出它到底 JOIN 了什麼

第一點特別容易被忽略:括號數量不對、少了逗號這類失誤,會直接讓格式化處理失敗,讓您在執行前就能發現問題。

整理、嵌入,以及改寫成其他資料庫的寫法

除了格式化之外,SQL 格式化工具還能整理貼上的 SQL、把它嵌入程式碼,並改寫成其他資料庫的寫法。

整理與嵌入

  • 移除註解:移除 -- 與 /* */。會影響行為的 MySQL /*! … */ 與最佳化器提示 /*+ … */ 會保留
  • 切換識別字的引號:在 MySQL 的 `x`、PostgreSQL / SQLite 的 "x"、SQL Server 的 [x] 之間轉換。只有名稱由小寫英文字母、數字與 _ 組成且不是保留字時,才會移除引號
  • 包成程式碼用的字串:JavaScript(樣板字串)、Python(三重引號)、Java(文字區塊)、Go、PHP、C#、JSON
  • 從程式碼字串取出 SQL:讀取 "SELECT … " + "FROM …" 這類串接與 \n 跳脫,還原成 SQL,從日誌或原始碼複製的查詢可以直接拿去格式化

改寫成其他資料庫的寫法

在 MySQL、PostgreSQL、SQLite、SQL Server 之間,改寫可以機械式修正的部分。字串、註解與加引號的識別字不會被動到。

項目 範例
識別字的引號 `id` → "id" → [id]
筆數限制 LIMIT 10, 5 → LIMIT 5 OFFSET 10;轉成 SQL Server 時用 TOP (n) / OFFSET … FETCH
NULL 取代 IFNULL(a, 0)、ISNULL(a, 0) ⇄ COALESCE(a, 0)
目前時間 NOW() ⇄ GETDATE() ⇄ CURRENT_TIMESTAMP
字串彙總 GROUP_CONCAT(x SEPARATOR ',') ⇄ STRING_AGG(x, ',')
轉型 a::int → CAST(a AS int)
型別與自動遞增(CREATE TABLE) AUTO_INCREMENT ⇄ SERIAL ⇄ IDENTITY(1,1)、TINYINT(1) → BOOLEAN、DATETIME → TIMESTAMP 等

無法機械式修正的部分不會改寫,而是列在「需手動確認」中:ILIKE(改成 LIKE,並提醒大小寫處理的差異)、||(在 MySQL 中表示 OR)、RETURNING、UPSERT(ON CONFLICT 與 ON DUPLICATE KEY UPDATE 等)、含 ORDER BY 的 GROUP_CONCAT、MySQL 的反斜線跳脫等。結果只是起點。各方言的語意差異很多,請務必在實際的資料庫上測試。

常見問題

支援 MySQL 和 PostgreSQL 的 SQL 嗎?

支援。標準 SQL、MySQL、PostgreSQL、MariaDB、SQL Server、BigQuery 等主流方言都可以格式化。若 SQL 中含有方言特有的語法,執行前仍請務必自行確認。

格式化會改變 SQL 的意義嗎?

不會。格式化只會調整空白、換行、縮排、關鍵字大小寫,查詢的意義(執行結果)不會改變。不過執行前,還是建議務必確認格式化後的版本與原意一致。

選錯方言會發生什麼事?

識別字的引號方式或關鍵字的解讀可能會跑掉,導致縮排錯亂或出現預期外的換行。如果覺得「格式化結果怪怪的」,請先檢查方言選擇是否正確。

貼上含有機密資料的 SQL 安全嗎?

安全。格式化完全在瀏覽器內完成,不會有任何網路連線,資料表名稱與欄位名稱都不會外流。

可以把 MySQL 的 SQL 轉成 PostgreSQL 或 SQL Server 用嗎?

引號、LIMIT、IFNULL / COALESCE、NOW()、GROUP_CONCAT / STRING_AGG、:: 轉型,以及 CREATE TABLE 的型別與 AUTO_INCREMENT 等可機械式修正的部分,會改寫。ILIKE、||、UPSERT、RETURNING 這類語意會改變的項目不會改寫,而是列為需手動確認的項目。不保證結果行為完全相同,執行前請先在實際的資料庫上確認。

總結

  • 格式化工具本身只是把工作委派給函式庫的一行程式碼,但方言選擇才是真正決定結果的關鍵參數
  • 光是識別字引號的差異,就可能改變查詢的解析方式,請依實際使用的資料庫選擇方言
  • 格式化不只是美觀而已:能及早發現語法錯誤、穩定 diff、解讀機器產生的 SQL

下次從日誌中挖出查詢語句時,不妨先丟進格式化工具看看。