蜜果私塾：常用数据库的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