小編精選 - 技術文章翻譯 · 06月28日

不要只憑習慣寫 WHERE 條件，常用查詢先跑一遍

WHERE 條件別憑習慣寫，常用查詢先跑一遍

剛換一套資料庫，先別急著看複雜語法。日常開發裡最先撞上的，往往還是那些普通條件：`=`、`<>`、`like`、`in`、`between`、`is null`、`order by`、`limit`、`group by`。這些東西看起來簡單，真到遷移 SQL 或排查介面資料時，反而最容易因為「應該差不多」而少看一眼。

這次用一張很小的測試表，把常見查詢條件集中跑一遍。環境是已經初始化好的 app_db，表放在 app_schema 下。當前實例初始化時啟用了 --enable-ci，所以大小寫相關的結果只按當前環境記錄，不能直接拿去推所有安裝模式。

先準備一批夠用的資料

測試表叫 app_schema.t_filter_demo。欄位沒有故意設計得複雜，name 放名稱，category 放分類，status 放狀態，score 和 price 用來做範圍、排序和聚合，remark 專門留給 NULL 判斷。

資料一共 12 行，裡面混了幾類東西：mysql、Kingbase、sql、oracle、pgsql；狀態有 online、offline、draft；name 裡故意放了 MySQL-basic、mysql-limit、MYSQL-LIKE 這種大小寫不同的值。score 有一行是 NULL，remark 有 4 行是 NULL。後面的每個查詢都靠這批資料說話。

準備條件查詢測試表

這張表不是業務模型，只是為了把查詢條件餵飽。比如 category 適合看 in 和 group by，status 適合看不等值和分組統計，score 適合看 between、排序和聚合函數，remark 適合看 NULL 的判斷方式。

終端提示符是 app_db=>，說明目前連線庫是 app_db，目前使用者不是管理員提示符下常見的 #。這類小資訊平時容易忽略，但寫查詢實驗時很有用，至少能先確認沒有連到安裝時的 test 庫，也沒有繼續用 system 做所有操作。

等值和不等值先確認

最普通的等值查詢沒有懸念：

sql 代碼解讀複製代碼select id, name, category, status, score
from app_schema.t_filter_demo
where category = 'mysql'
order by id;

返回的是 1、2、3 三行，正好都是 mysql 分類。這裡先把最基礎的 where category = 'mysql' 跑通，後面再疊更多條件。

不等值用了兩種寫法：

sql 代碼解讀複製代碼where status <> 'online'

以及：

sql 代碼解讀複製代碼where status != 'online'

兩次返回結果一致，都是 3、6、8、9、11 這 5 行，也就是 offline 和 draft 狀態的資料。當前環境下，<> 和 != 都能用。寫遷移腳本時，如果原來的 MySQL SQL 裡用了 !=，至少這個場景下沒有直接卡住。

驗證等值和不等值

不過長期寫 SQL，<> 仍然更接近標準寫法；!= 更像從 MySQL 使用習慣帶過來的寫法。能不能用是一回事，團隊裡怎麼統一又是另一回事。

這裡還順手確認了一點：status <> 'online' 返回的是所有非 online 的明確值，不會把 NULL 混進來。當前測試表的 status 欄位是 not null，所以結果很好讀。如果業務表裡狀態欄位允許 NULL，不等值條件就要重新檢查，不能想當然把 NULL 當成「不是 online」。

AND、OR 最怕括號省掉

組合條件先看 and：

sql 代碼解讀複製代碼select id, name, category, status, score
from app_schema.t_filter_demo
where category = 'Kingbase'
  and status = 'online'
order by id;

返回 4、5 兩行。表裡 category 存的是 kingbase，查詢裡寫的是 Kingbase，當前環境仍然匹配到了。這個現象和前面提到的 --enable-ci 有關，大小寫不敏感不是隨口假設出來的，是這次查詢結果裡能看到的。

再把 or 加進來：

sql 代碼解讀複製代碼where (category = 'mysql' or category = 'Kingbase')
  and status = 'online'

返回 1、2、4、5 四行。這裡括號不能省。沒有括號時，讀 SQL 的人要在腦子裡重新算 and、or 的優先級；有括號時，條件分組直接寫在語句裡。

驗證 and 和 or

從 MySQL 遷過來的 SQL，大量篩選條件都長這樣：一個主條件，再疊幾個狀態、分類、時間範圍。KingbaseES 能跑這些基本條件不稀奇，真正要養成的是把複雜條件寫得不含糊。尤其是 or 混在一串 and 裡時，括號比解釋更可靠。

LIKE 的大小寫表現要實測

LIKE 這裡特意用了大小寫不一樣的名稱。先排除名字裡包含 sql 的資料：

sql 代碼解讀複製代碼select id, name, category
from app_schema.t_filter_demo
where name not like '%sql%'
order by id;

返回 4、6、10、11 四行。Kingbase-start、KINGBASE-schema、Oracle-mode、oracle-sequence 都沒有被 %sql% 匹配到。

接著分別查：

sql 代碼解讀複製代碼where name like 'MYSQL%'

和：

sql 代碼解讀複製代碼where name like 'mysql%'

兩次結果都是 1、2、3 三行：MySQL-basic、mysql-limit、MYSQL-LIKE。這說明當前實例裡，LIKE 對這批字元比較是大小寫不敏感的。

LIKE 和 NOT LIKE 的大小寫表現

這裡不要把結論寫大。只能說當前環境、當前初始化方式下，MYSQL% 和 mysql% 查出來一致。如果換成沒有啟用大小寫不敏感的實例，或者換字元集、排序規則，應該重新跑一遍。遷移 MySQL 的時候，LIKE 往往藏在搜尋介面裡，大小寫表現最好提前確認。

還有一個細節：not like '%sql%' 把 MySQL-basic、mysql-limit、MYSQL-LIKE、SQL-where、sql-order、SQL-null 都排除了。這個結果比只看 like 'mysql%' 更能暴露大小寫影響，因為 %sql% 在字串中間匹配，MySQL 裡的 SQL 也被算進去了。

IN、NOT IN、BETWEEN 都比較順

IN 適合替代一串 or。這次查 mysql 和 Kingbase 兩類：

sql 代碼解讀複製代碼select id, name, category, status
from app_schema.t_filter_demo
where category in ('mysql', 'Kingbase')
order by category, id;

返回 6 行，包括 3 行 kingbase 和 3 行 mysql。同樣，因為當前環境大小寫不敏感，Kingbase 能匹配到表裡的 kingbase。

反過來用 not in，結果就是剩下的 oracle、pgsql、sql 三類，共 6 行。

範圍條件用了 between 85 and 95：

sql 代碼解讀複製代碼select id, name, score, price
from app_schema.t_filter_demo
where score between 85 and 95
order by score desc, id;

返回 7 行，包含 95，也包含 85。between 的邊界是包含兩端的，這一點和 MySQL 裡常見理解一致。結果按 score desc, id 排序後，最高分是 Kingbase-start 的 95，最低一行是 Oracle-mode 的 85。

IN、NOT IN 和 BETWEEN

這些條件本身不難，容易出問題的是資料裡有 NULL 的時候。not in 遇到 NULL 子查詢時會變得麻煩，不過這次先只用固定列表，不把子查詢混進來。NULL 單獨看。

order by category, id 也幫忙看清了返回順序。in 的結果不是按列表裡 'mysql', 'Kingbase' 的順序回來，而是按 SQL 裡寫的排序欄位回來。介面如果對返回順序有要求，就把 order by 寫清楚，不要靠插入順序或者 in 列表順序。

NULL 不要用等號猜

remark 欄位有 4 行是 NULL。用正確寫法查：

sql 代碼解讀複製代碼where remark is null

返回 3、6、9、12 四行。反過來：

sql 代碼解讀複製代碼where remark is not null

返回 8 行。

再故意寫一次：

sql 代碼解讀複製代碼where remark = null

結果是 0 行。這個結果很適合拿來提醒自己：NULL 不是一個普通值，不能用 = 去匹配。要查空值，就寫 is null；要排除空值，就寫 is not null。

score is null 也單獨跑了一次，只返回 9 號 SQL-null。後面的聚合統計會用到它，因為 count(*) 和 count(score) 的結果不一樣。

NULL 判斷

從 MySQL 切到 KingbaseES，NULL 這塊反而不用背新東西，按 SQL 裡的正常寫法來就行。真正要改的是一些偷懶習慣：介面拼條件時不要把 = null 拼進去，動態 SQL 裡也不要把 NULL 當成普通字串處理。

remark 和 score 兩個欄位都放了 NULL，效果不一樣：remark 是文字備註，適合驗證空值篩選；score 是數字，後面會參與 avg。同樣是 NULL，到了統計函數裡就會影響計數和平均值，這比單純查出空行更容易在報表裡踩坑。

排序和 LIMIT 可以直接組合

取分數最高的 5 行：

sql 代碼解讀複製代碼select id, name, category, score
from app_schema.t_filter_demo
where score is not null
order by score desc, id
limit 5;

返回結果是 4、11、6、1、5，對應分數 95、92、91、90、88。這裡先用 score is not null 把 NULL 排除掉，再按分數倒序排，最後加 id 兜底。

多欄位排序配合 LIMIT

limit 語法前面已經單獨驗證過，這裡放在條件查詢裡看更接近日常用法。實際介面裡很少只寫 limit 5，更多是先篩選，再排序，再限制條數。排序欄位只有一個時，如果出現同分，分頁結果可能不穩定；多加一個唯一欄位做兜底，結果更好復現。

這次資料裡前 5 名沒有同分，id 兜底看起來不顯眼，但它仍然應該寫上。等資料量上來以後，同一個分數出現多行很正常。分頁介面少一個穩定排序欄位，問題通常不會立刻暴露，而是在翻頁時出現重複行或漏行。

聚合函數會跳過 NULL

先對全表做一次基礎統計：

sql 代碼解讀複製代碼select
  count(*) as total_count,
  count(score) as score_count,
  min(score) as min_score,
  max(score) as max_score,
  avg(score) as avg_score
from app_schema.t_filter_demo;

結果裡 total_count 是 12，score_count 是 11。差的那一行，就是 score 為 NULL 的 9 號資料。min(score) 是 70，max(score) 是 95，avg(score) 是 84.45454545454545。平均值按 11 個非 NULL 分數算，不是按 12 行硬除。

再按狀態分組：

sql 代碼解讀複製代碼select status, count(*) as row_count
from app_schema.t_filter_demo
group by status
order by status;

返回 draft=2、offline=3、online=7。

基礎聚合統計

這裡和 MySQL 裡的常見用法差別不大。真正需要盯住的是 count(*) 和 count(欄位)。前者數行，後者只數這個欄位非 NULL 的行。介面裡如果把這兩個混用，統計結果會差一截。

狀態統計也順便校驗了前面的插入資料：2 行草稿、3 行下線、7 行上線，加起來正好 12 行。做這種小實驗時，分組結果能當作一次反查，避免後面拿一批本來就不對的資料繼續驗證。

HAVING 是分組後的過濾

最後按分類統計：

sql 代碼解讀複製代碼select category, count(*) as row_count, avg(score) as avg_score
from app_schema.t_filter_demo
group by category
order by category;

結果有 5 類：kingbase 3 行，平均分約 91.33；mysql 3 行，平均分約 82.67；oracle 2 行，平均分 88.50；pgsql 1 行，平均分 87；sql 3 行，平均分 71.50。sql 分類裡有一行 score 是 NULL，所以平均分只按 70 和 73 算。

再加上：

sql 代碼解讀複製代碼having count(*) >= 2

pgsql 被過濾掉，只剩下行數大於等於 2 的分類。where 是分組前過濾明細行，having 是分組後過濾聚合結果，這個差別在報表 SQL 裡很常見。

GROUP BY 和 HAVING

這組查詢跑完以後，日常 WHERE 條件基本有了底。等值、不等值、and/or、like、in、between、NULL 判斷、排序、限制條數、聚合和 having，在當前 V009R001C010 環境裡都能按預期工作。真正需要單獨標記的有三點：當前實例大小寫不敏感，所以 LIKE 和 category = 'Kingbase' 的表現不能脫離環境複述；NULL 只能用 is null / is not null；聚合函數處理 NULL 時要看清楚 count(*) 和 count(欄位) 的差別。