稱不上DB咖的我,反常地連寫兩篇SQL筆記,其實都是研究「動態產生SQL查詢條件」議題的副產品,這篇才算步入正題,鴨架子湯先來兩碗,烤鴨才上桌,哈!但這樣安排是對的,以下探討有一部分需要先前筆記的基礎才好聊下去。

兩週前,參加一場Code Review討論,會中大家剛好聊到「動態產生SQL查詢條件」這檔事兒。它的情境是:使用者在操作介面上有多項條件選擇,例如:日期、類別、關鍵字,每個條件使用者可選擇輸入或不輸入(不輸入代表不限定)。從程式的角度,使用者依輸入條件不同,可能形成以下幾種SQL查詢條件:

  1. 全部都不填
    不需WHERE條件,查詢全部資料
  2. 填日期
    WHERE Date = @date
  3. 填類別
    WHERE Catg = @catg
  4. 填關鍵字
    WHERE Subject LIKE '%' + @keywd + '%'
  5. 填日期+填類別
    WHERE Date = @date AND Catg = @catg
  6. 填日期+填關鍵字
    WHERE Date = @date AND Subject LIKE '%' + @keywd + '%'
  7. 填類別+填關鍵字
    WHERE Catg = @catg AND Subject LIKE '%' + @keywd + '%'
  8. 填日期+填類別+填關鍵字
    WHERE Date = @date AND Catg = @catg AND Subject LIKE '%' + @keywd + '%'

為了應付這種情境,老鳥們應該都看過一種寫法:

        static void DynaWhereSample(DateTime? date, string catg, string keywd)
        {
            SqlCommand cmd = new SqlCommand();
            string sql = "SELECT * FROM MyTable WHERE 1=1 ";
            if (date != null)
            {
                sql += "AND Date = @date";
                cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date.Value;
            }
            if (!string.IsNullOrEmpty(catg))
            {
//嚴正提醒:catg等參數來自使用者輸入,請使用SqlParameter,禁止直接串接
//錯誤示範 => sql += "AND Catg='" + catg + "'"
//以上寫法隱藏SQL Injection風險,招致難以想像的災難(嚴重者可至家破人亡)
//開發者犯此錯誤,依江湖聯合幫規第127條,處唯一死刑-阿魯巴到死!
                sql += "AND Catg = @catg";
                cmd.Parameters.Add("@catg", SqlDbType.VarChar).Value = catg;
            }
            if (!string.IsNullOrEmpty(keywd))
            {
                sql += "AND Subject LIKE '%' + @keywd + '%'";
                cmd.Parameters.Add("@keywd", SqlDbType.NVarChar).Value = keywd;
            }
            cmd.CommandText = sql;
            //執行SQL,以下省略
        }

程式依date, catg, keywd是否有值動態組裝出不同的WHERE條件組合(再次提醒,請使用SqlParameter,切忌直接內容直接串成SQL指令,否則…),若使用者什麼都沒填,SQL查詢指令會變成SELECT * FROM MyTable WHERE 1=1,效果與SELEC * FROM MyTable相同。

「WHERE 1=1」差不多已經是這種寫法的代名詞吧?我發現只要說出「WHERE 1=1再動態接條件」,許多老鳥都會點頭會心一笑,足見此招在江湖流傳之廣。(不信大家可以用Google查「WHERE 1=1」 XD)

提到WHERE 1=1,不意外地就有人起疑「多加這種條件會不會影響效能?會不會該查詢變成Table Scan(Index Scan)?」

依我的認知,市面上的主流資料庫早已歷經千鎚百鍊,不致呆頭呆腦被1=1騙,編譯SQL最佳化時自有能力排除這類永遠成立或永遠不成立的無意義條件。過去,對於這點主張只能想當然爾,在搞懂Index Scan與Index Seek的意義及發生時機後,現在我們能靠檢查執行計劃驗證:

如上圖所示,加上WHRE 1=1,仍然走Index Seek,證明SQL未受1=1混淆,仍選擇適合的執行計劃。

等等,難道動態組條件一定非得WHERE 1=1?

寫WHERE 1=1的好處是邏輯簡單,只要無腦地有給值就串字串AND Col…,即使完全不限條件也能靠WHERE 1=1全選。若不這麼做,得判斷第一個條件加WHERE Col=…,第二個以後的條件加AND Col=…,全無條件時連WHERE都不要加,邏輯當真複雜不少,這也難怪WHERE 1=1這招能風行一時。不過,都到了LINQ時代,出現好多新武器,用短短幾行程式碼就能寫出沒有贅肉的動態WHERE條件組裝邏輯:

        static void DynaWhereSample(DateTime? date, string catg, string keywd)
        {
            SqlCommand cmd = new SqlCommand();
            List<string> cond = new List<string>();
            if (date != null)
            {
                cond.Add("Date = @date");
                cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date.Value;
            }
            if (!string.IsNullOrEmpty(catg))
            {
                cond.Add("Catg = @catg");
                cmd.Parameters.Add("@catg", SqlDbType.VarChar).Value = catg;
            }
            if (!string.IsNullOrEmpty(keywd))
            {
                cond.Add("Subject LIKE '%' + @keywd + '%'");
                cmd.Parameters.Add("@keywd", SqlDbType.NVarChar).Value = keywd;
            }
            cmd.CommandText = string.Format(
                "SELECT * FROM MyTable{0}{1}",
                cond.Count > 0 ? " WHERE " : "",
                string.Join(" AND ", cond.ToArray()));
            //執行SQL,以下省略
            Console.WriteLine(cmd.CommandText);
        }

幾行簡潔程式碼就能組出有馬甲線的精實SQL指令,何苦再用怪招?大家就忘了WHERE 1=1吧!:P


Comments

Be the first to post a comment

Post a comment