蜜果私塾：常用數據庫的DML語句的比較和總結

文：阿蜜果

日期：2011-8-6

版權所有，轉載請注明出處

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

最近為了做新舊系統異構數據庫的同步，另外為了確保新舊系統實現的業務功能更加趨于一致，所以有點小懶的我也啃了一段時間的舊系統的存儲過程（舊系統的業務功能全部依賴SQL Server中的存儲過程，而新系統采用Oracle數據庫， 而且數據庫語句都通過上層的業務編寫），對一些DML語句有一些總結和體會，分享給大家。

本文進行對比的地方基于常用的幾種數據庫：Oracle、MySQL和SQL Server。

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

有一些語句寫法只在某一種數據庫中使用，而其余數據庫會導致執行失敗，若需要在編程時寫SQL語句，因為可能引起的數據庫遷移問題，何不盡量避免這種遷移陷阱呢？

（1）INSERT后不加INTO

       在INSERT后不加INTO，在SQL Server和MySQL數據庫都會執行成功，但Oracle會提示“ORA-00925: missing INTO keyword”的錯誤信息，例如：

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

（2）DELETE后不加FROM

       在DELETE后不加FROM的情況，在SQL Server和Oracle數據庫中可以執行成功，但在MySQL數據庫中會提示“SQL 執行錯誤 # 1064. 從數據庫的響應: 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）獲取當前時間

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

例如在MySQL中顯示當前時間可使用語句：

select now()

在Oracle中顯示當前時間使用語句：

select sysdate from dual

為了解決三者使用不一樣的問題，一般的做法是在Java使用JDBC操作時，可以將當前時間通過Java的方法得到，再給某個字段設置這個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）自增主鍵的處理

       在這三種數據庫中都可以定義自增ID作為主鍵，優點：節省時間，根本不用考慮怎么來標識唯一記錄，寫程序也簡單了，數據庫維護著這一批ID號，在INSERT語句時，如果所操作的表采用了自增主鍵，一般不需要指定這個字段。

　　缺點：在做分布式數據庫時，要求數據同步時，這種自增ID就會出現嚴重的問題，因為你無法用該ID來唯一標識記錄。同時在數據庫做移植時，也會出現各種問題，總之，對此自增ID有依賴的情況，都有可能出現問題。我就是深受其害的人之一。

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

2、 稍微復雜點的DML語句

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

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

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

因為上面的語句太依賴于字段的排序，為了導致遷移的一些不必要的問題，一般提倡指定所要插入數據的列，參考語法如下：

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

INSERT語句新行數據的全部字段的值或某些字段的值可以來自于對另一個表的查詢語句。例如如下的語句將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變量是存儲過程定義的，在運行時它是確定的值。

待續。

3、 參考文檔

《uniqueidentifier數據類型》：http://baike.baidu.com/view/1440863.htm