編程經(jīng)驗(yàn)點(diǎn)滴 動(dòng)態(tài)SQL的拼接技巧
更新時(shí)間:2012年09月19日 11:32:27 作者:
眾多人在回復(fù),其中至少有三位老兄,誤解了動(dòng)態(tài) SQL 的拼接。特寫此文,闡述一下其中的技巧。希望能糾正初學(xué)者的錯(cuò)誤
常見的誤解有:
1. 只用 ado.net ,無法進(jìn)行動(dòng)態(tài) SQL 拼接。
2. 有幾個(gè)動(dòng)態(tài)參數(shù),代碼的重復(fù)量就成了這些參數(shù)的不同數(shù)量的組合數(shù),動(dòng)態(tài)參數(shù)越多,重復(fù)量越大。
對(duì)于第二個(gè)問題,以下的錯(cuò)誤代碼為其證據(jù):
if(id>0 && string.IsNullOrEmpty(name))
{
command.CommandText = "select * from t1 where id=?";
command.Parameters.Add(id);
}
if(id<=0 && !string.IsNullOrEmpty(name))
{
command.CommandText = "select * from t1 where name=?";
command.Parameters.Add(name);
}
if(id<=0 && !string.IsNullOrEmpty(name))
{
command.CommandText = "select * from t1 where id=? and name=?";
command.Parameters.Add(id);
command.Parameters.Add(name);
}
這兩個(gè)問題都很好解決,給一個(gè)正確的代碼例子大家看看即可:
string sql ="select * from t1 where 1=1";
if(id != null)
{
sql += " and id=?";
addParameterValue(cmd,id);
}
if(!string.IsNullOrEmpty(name))
{
sql += " and name=?";
addParameterValue(cmd,name);
}
command.CommandText = sql;
這里的技巧在于,使用了一個(gè) "where 1=1", 巧妙解決了后續(xù) sql 拼接中,每行開頭是否要有 "and" 的問題。而這個(gè) "where 1=1",并不會(huì)對(duì)數(shù)據(jù)庫的索引執(zhí)行,造成性能上的影響。
對(duì)參數(shù)進(jìn)行排列組合,然后寫各種組合的 SQL,這個(gè)思路很奇怪。問題是,很多初學(xué)者,都有這個(gè)思維習(xí)慣。本人不是計(jì)算機(jī)科班出身,不知道是否哪本教科書,就是如此教導(dǎo)的。但很不幸的是,這個(gè)思維習(xí)慣是錯(cuò)誤的。
"where 1=1" 雖是教科書中沒有的小技巧,卻很管用。
另外,在程序中,一般會(huì)在用戶界面上讓使用用戶錄入數(shù)字,這個(gè)數(shù)字的數(shù)值,在代碼中會(huì)自動(dòng)變成 string,然后嘗試 string 轉(zhuǎn)換成 int/long,最后送到 sql 函數(shù)里。這里需要特別注意的是,很多人把某個(gè)特殊的數(shù)值,作為“用戶無錄入的默認(rèn)值”,正如本文開頭所寫的錯(cuò)誤代碼那樣:
if(id>0 && string.IsNullOrEmpty(name))
問題在于,0 是否是不正常的業(yè)務(wù)數(shù)值,代碼中看不出來。不排除程序員隨意指定一個(gè)數(shù)值,作為“用戶無錄入的默認(rèn)值”,如果不巧這個(gè)默認(rèn)值,實(shí)際上是有其他意義的,那就造成問題。
在數(shù)據(jù)庫的理論中,沒有指定的數(shù)據(jù),是用 null 來表示的,不論是 string 還是 int/long。
這是一個(gè)很好的思路,同樣可以用在這里的 sql 拼接中。因此,我們?cè)诤竺娴拇a中,使用了這個(gè):
if(id != null)
上述代碼中,
addParameterValue(cmd,name); 是一個(gè)簡(jiǎn)單封裝的函數(shù),用來封裝如下一小段代碼,目的是讓最后的代碼,較為簡(jiǎn)捷直觀:
DbParameter p = cmd.CreateParameter();
p.ParameterName = "@XXX";
p.Value = TTT;
cmd.Parameters.Add(p);
當(dāng)然,這個(gè) addParameterValue() 封裝函數(shù),是可有可無的。多寫幾個(gè) DbParameter p = cmd.CreateParameter() 并沒有什么大問題。
1. 只用 ado.net ,無法進(jìn)行動(dòng)態(tài) SQL 拼接。
2. 有幾個(gè)動(dòng)態(tài)參數(shù),代碼的重復(fù)量就成了這些參數(shù)的不同數(shù)量的組合數(shù),動(dòng)態(tài)參數(shù)越多,重復(fù)量越大。
對(duì)于第二個(gè)問題,以下的錯(cuò)誤代碼為其證據(jù):
復(fù)制代碼 代碼如下:
if(id>0 && string.IsNullOrEmpty(name))
{
command.CommandText = "select * from t1 where id=?";
command.Parameters.Add(id);
}
if(id<=0 && !string.IsNullOrEmpty(name))
{
command.CommandText = "select * from t1 where name=?";
command.Parameters.Add(name);
}
if(id<=0 && !string.IsNullOrEmpty(name))
{
command.CommandText = "select * from t1 where id=? and name=?";
command.Parameters.Add(id);
command.Parameters.Add(name);
}
這兩個(gè)問題都很好解決,給一個(gè)正確的代碼例子大家看看即可:
復(fù)制代碼 代碼如下:
string sql ="select * from t1 where 1=1";
if(id != null)
{
sql += " and id=?";
addParameterValue(cmd,id);
}
if(!string.IsNullOrEmpty(name))
{
sql += " and name=?";
addParameterValue(cmd,name);
}
command.CommandText = sql;
這里的技巧在于,使用了一個(gè) "where 1=1", 巧妙解決了后續(xù) sql 拼接中,每行開頭是否要有 "and" 的問題。而這個(gè) "where 1=1",并不會(huì)對(duì)數(shù)據(jù)庫的索引執(zhí)行,造成性能上的影響。
對(duì)參數(shù)進(jìn)行排列組合,然后寫各種組合的 SQL,這個(gè)思路很奇怪。問題是,很多初學(xué)者,都有這個(gè)思維習(xí)慣。本人不是計(jì)算機(jī)科班出身,不知道是否哪本教科書,就是如此教導(dǎo)的。但很不幸的是,這個(gè)思維習(xí)慣是錯(cuò)誤的。
"where 1=1" 雖是教科書中沒有的小技巧,卻很管用。
另外,在程序中,一般會(huì)在用戶界面上讓使用用戶錄入數(shù)字,這個(gè)數(shù)字的數(shù)值,在代碼中會(huì)自動(dòng)變成 string,然后嘗試 string 轉(zhuǎn)換成 int/long,最后送到 sql 函數(shù)里。這里需要特別注意的是,很多人把某個(gè)特殊的數(shù)值,作為“用戶無錄入的默認(rèn)值”,正如本文開頭所寫的錯(cuò)誤代碼那樣:
復(fù)制代碼 代碼如下:
if(id>0 && string.IsNullOrEmpty(name))
問題在于,0 是否是不正常的業(yè)務(wù)數(shù)值,代碼中看不出來。不排除程序員隨意指定一個(gè)數(shù)值,作為“用戶無錄入的默認(rèn)值”,如果不巧這個(gè)默認(rèn)值,實(shí)際上是有其他意義的,那就造成問題。
在數(shù)據(jù)庫的理論中,沒有指定的數(shù)據(jù),是用 null 來表示的,不論是 string 還是 int/long。
這是一個(gè)很好的思路,同樣可以用在這里的 sql 拼接中。因此,我們?cè)诤竺娴拇a中,使用了這個(gè):
復(fù)制代碼 代碼如下:
if(id != null)
上述代碼中,
addParameterValue(cmd,name); 是一個(gè)簡(jiǎn)單封裝的函數(shù),用來封裝如下一小段代碼,目的是讓最后的代碼,較為簡(jiǎn)捷直觀:
復(fù)制代碼 代碼如下:
DbParameter p = cmd.CreateParameter();
p.ParameterName = "@XXX";
p.Value = TTT;
cmd.Parameters.Add(p);
當(dāng)然,這個(gè) addParameterValue() 封裝函數(shù),是可有可無的。多寫幾個(gè) DbParameter p = cmd.CreateParameter() 并沒有什么大問題。
您可能感興趣的文章:
相關(guān)文章
在sqlserver中如何使用CTE解決復(fù)雜查詢問題
本文給大家介紹使用cte解決復(fù)雜查詢問題,在此代碼中需要注意count函數(shù),它統(tǒng)計(jì)了一個(gè)列,如果該列在某行的值為null,將不會(huì)統(tǒng)計(jì)該行,本文代碼詳解并附有注釋,感興趣的朋友一起看看吧2015-11-11數(shù)據(jù)轉(zhuǎn)換沖突及轉(zhuǎn)換過程中大對(duì)象的處理
數(shù)據(jù)轉(zhuǎn)換沖突及轉(zhuǎn)換過程中大對(duì)象的處理方法,大家可以參考下。2009-07-07sql server中錯(cuò)誤日志errorlog的深入講解
很多時(shí)候診斷SQLSERVER問題都需要看SQLSERVER的錯(cuò)誤日志,所以下面這篇文章主要給大家介紹了關(guān)于sql server中錯(cuò)誤日志errorlog的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2018-10-10SQL為什么不建議執(zhí)行超過3表以上的多表關(guān)聯(lián)查詢
mysql是往簡(jiǎn)單化方向去設(shè)計(jì)的,如果多個(gè)表關(guān)聯(lián)查詢(超過3張表)效率上是比不上PG的,本文就詳細(xì)的介紹一下原因,感興趣的可以了解一下2022-03-03SQLserver 實(shí)現(xiàn)分組統(tǒng)計(jì)查詢(按月、小時(shí)分組)
首先創(chuàng)建數(shù)據(jù)表IP地址,訪問時(shí)間和訪問次數(shù)。如果每訪問一次就插入一條記錄,那么AccessCount可以不要,查詢時(shí)使用count就可以了,這樣當(dāng)訪問量很大的時(shí)候會(huì)對(duì)數(shù)據(jù)庫造成很大壓力。2009-06-06