蜜果私塾：常用數(shù)據(jù)庫(kù)的DML語句的比較和總結(jié)

文：阿蜜果

日期：2011-8-6

版權(quán)所有，轉(zhuǎn)載請(qǐng)注明出處

DML = Data Manipulation Language，數(shù)據(jù)操縱語言，使用戶能夠查詢數(shù)據(jù)庫(kù)以及操作已有數(shù)據(jù)庫(kù)中的數(shù)據(jù)的計(jì)算機(jī)語言。具體是指是UPDATE更新、INSERT插入、DELETE刪除。

最近為了做新舊系統(tǒng)異構(gòu)數(shù)據(jù)庫(kù)的同步，另外為了確保新舊系統(tǒng)實(shí)現(xiàn)的業(yè)務(wù)功能更加趨于一致，所以有點(diǎn)小懶的我也啃了一段時(shí)間的舊系統(tǒng)的存儲(chǔ)過程（舊系統(tǒng)的業(yè)務(wù)功能全部依賴SQL Server中的存儲(chǔ)過程，而新系統(tǒng)采用Oracle數(shù)據(jù)庫(kù)， 而且數(shù)據(jù)庫(kù)語句都通過上層的業(yè)務(wù)編寫），對(duì)一些DML語句有一些總結(jié)和體會(huì)，分享給大家。

本文進(jìn)行對(duì)比的地方基于常用的幾種數(shù)據(jù)庫(kù)：Oracle、MySQL和SQL Server。

1、 盡量避免不夠通用的SQL語句

有一些語句寫法只在某一種數(shù)據(jù)庫(kù)中使用，而其余數(shù)據(jù)庫(kù)會(huì)導(dǎo)致執(zhí)行失敗，若需要在編程時(shí)寫SQL語句，因?yàn)榭赡芤鸬臄?shù)據(jù)庫(kù)遷移問題，何不盡量避免這種遷移陷阱呢？

（1）INSERT后不加INTO

       在INSERT后不加INTO，在SQL Server和MySQL數(shù)據(jù)庫(kù)都會(huì)執(zhí)行成功，但Oracle會(huì)提示“ORA-00925: missing INTO keyword”的錯(cuò)誤信息，例如：

INSERT userbarring(UserName, PhoneNumber, Areanum, LimitType) VALUES ('amigo', '13233334444', '010', 1);

（2）DELETE后不加FROM

       在DELETE后不加FROM的情況，在SQL Server和Oracle數(shù)據(jù)庫(kù)中可以執(zhí)行成功，但在MySQL數(shù)據(jù)庫(kù)中會(huì)提示“SQL 執(zhí)行錯(cuò)誤 # 1064. 從數(shù)據(jù)庫(kù)的響應(yīng): You have an error in your SQL syntax; check the manual that corresponds to you’re my SQL server version from the right syntax to use near ‘…….’”，例如：

DELETE cld_callbarring WHERE UserName='amigo'

（3）獲取當(dāng)前時(shí)間

        這三種數(shù)據(jù)庫(kù)的都能定義DATETIME（日期時(shí)間類型），但在INSERT和UPDATE等語句為這種類型的數(shù)據(jù)設(shè)值時(shí)所用的函數(shù)都各不相同。MySQL使用now()函數(shù)，SQL Server使用GETDATE()函數(shù)，Oracle使用sysdate。

例如在MySQL中顯示當(dāng)前時(shí)間可使用語句：

select now()

在Oracle中顯示當(dāng)前時(shí)間使用語句：

select sysdate from dual

為了解決三者使用不一樣的問題，一般的做法是在Java使用JDBC操作時(shí)，可以將當(dāng)前時(shí)間通過Java的方法得到，再給某個(gè)字段設(shè)置這個(gè)java.sql.Date類型的值，參考代碼如下：

Date createTime = new Date();
String sql = "insert into testdate(createTime) values (?) ";
ps = conn.prepareStatement(sql);
ps.setDate(1, new java.sql.Date(createTime.getTime()));
int re = ps.executeUpdate();

（4）自增主鍵的處理

       在這三種數(shù)據(jù)庫(kù)中都可以定義自增ID作為主鍵，優(yōu)點(diǎn)：節(jié)省時(shí)間，根本不用考慮怎么來標(biāo)識(shí)唯一記錄，寫程序也簡(jiǎn)單了，數(shù)據(jù)庫(kù)維護(hù)著這一批ID號(hào)，在INSERT語句時(shí)，如果所操作的表采用了自增主鍵，一般不需要指定這個(gè)字段。

　　缺點(diǎn)：在做分布式數(shù)據(jù)庫(kù)時(shí)，要求數(shù)據(jù)同步時(shí)，這種自增ID就會(huì)出現(xiàn)嚴(yán)重的問題，因?yàn)槟銦o法用該ID來唯一標(biāo)識(shí)記錄。同時(shí)在數(shù)據(jù)庫(kù)做移植時(shí)，也會(huì)出現(xiàn)各種問題，總之，對(duì)此自增ID有依賴的情況，都有可能出現(xiàn)問題。我就是深受其害的人之一。

      解決的方法有很多，例如將主鍵定義成一個(gè)字符串類型，該字段交給上層業(yè)務(wù)來指定和保證唯一性，例如定義成流水號(hào)（里面帶有日期時(shí)間和其它保證唯一性的值）。或者將主鍵使用uniqueidentifier數(shù)據(jù)類型等。

2、 稍微復(fù)雜點(diǎn)的DML語句

（1）INSERT語句中各字段的值來自SELECT語句

     INSERT語句用于向表格中插入新的行，若按照數(shù)據(jù)庫(kù)字段的排列順序進(jìn)行字段設(shè)值，可以在INSERT語句中不指定字段名稱，參考語法如下：

INSERT INTO 表名稱 VALUES (值1, 值2,....)

因?yàn)樯厦娴恼Z句太依賴于字段的排序，為了導(dǎo)致遷移的一些不必要的問題，一般提倡指定所要插入數(shù)據(jù)的列，參考語法如下：

INSERT INTO表名稱 (列1, 列2,...) VALUES (值1, 值2,....)

INSERT語句新行數(shù)據(jù)的全部字段的值或某些字段的值可以來自于對(duì)另一個(gè)表的查詢語句。例如如下的語句將userbarring中的LimitType值為0的記錄插入到userbarring_bak表中：

INSERT INTO userbarring_bak(UserName, PhoneNumber, Areanum, LimitType) SELECT Usernumber, BarNumber, Areanum, LimitType FROM userbarring where LimitType=0

（2）CASE……WHEN……語句

有些INSERT或UPDATE語句中帶有CASE……WHEN語句，舉例如下：

INSERT INTO PLAT_UIDLine(UID,CallIn,CallOut,LastUpdateTime) VALUES (‘12345678’,(CASE @CallType WHEN 1 THEN 1 ELSE 0 END),(CASE @CallType WHEN 2 THEN 1 ELSE 0 END),GETDATE())

其中CallType變量是存儲(chǔ)過程定義的，在運(yùn)行時(shí)它是確定的值。

待續(xù)。

3、 參考文檔

《uniqueidentifier數(shù)據(jù)類型》：http://baike.baidu.com/view/1440863.htm