支持Oracle和MySQL：它们的SQL语法有多相似？

Question

我们在一个项目中使用Oracle，希望也支持MySQL。它们的SQL方言有多接近呢？是否可能在不进行太多操作的情况下同时使用相同的SQL源代码呢？

详细信息如下：

- 我们使用iBatis作为持久化管理器，将SQL语句清晰地分离到资源文件中。但我们在SQL级别上工作，这有其优点（和缺点）。 - 我们不想转向像Hibernate这样的对象关系映射器，它可以完全屏蔽我们之间的方言差异。 - 我们努力保持使用通用的Oracle SQL子集。 - 没有PL/SQL。 - 我们不使用存储过程或触发器（目前还没有）。 - 我们使用检查约束、唯一约束和外键约束。 - 我们使用ON DELETE CASCADEs。 - 我们使用事务（在iBatis API级别完成）。 - 我们在查询中调用了一些Oracle时间戳函数。 - 我们将使用MySQL的InnoDB存储引擎（它支持事务和约束）。

那么你有什么想法呢？我们需要维护两个不同的iBatis SQL资源文件集合，一个针对每种方言，还是可能有一个支持MySQL和Oracle的单一SQL集合呢？

最终更新：感谢所有答案，特别是指向Troels Arvin关于差异的页面的指针。遗憾的是标准不够标准。对我们来说，问题在于MySQL自增与Oracle序列、MySQL LIMIT与Oracle Rowumber()以及一两个奇怪的函数之间的差异。大多数其他内容应该很容易转移，除了一些编辑，以确保我们使用SQL-92，正如@mjv所指出的那样。更大的问题是，某些查询可能需要在每个DBMS中手动进行不同的优化。

- Jim Ferrans

Answer 1

预计会有一些小问题，但总体来说应该相对容易。

从您目前使用的功能列表中，通常只会有一些语法或语义差异，很容易修复或解决。您不使用PL/SQL和/或存储过程是一个优点。一个好的经验法则是尽量坚持SQL-92标准，大多数DBMS都支持，特别是Oracle和MySQL。 （请注意，这不是当前的SQL标准，即SQL-2008）。

一些差异：

• “LIMIT”是一个著名的例子：为了限制要检索的结果列表中的行数，MySQL使用LIMIT n，在查询末尾，Oracle在WHERE子句中使用RowNumber（）（这很痛苦，因为您还需要在SELECT列表中引用它...）
• 一些数据类型是不同的。我认为主要是BOOLEAN（但谁使用它;-)）还有一些关于DATETIME类型/格式的微妙差异。
• 一些函数名称不同（SUBSTRING vs. SUBSTR等...）

刚刚发现似乎有一个关于SQL实现差异的好资源。

从其他人的回复中可以看出，DDL可能是一个问题。我忽略了这一点，可能是因为许多应用程序不需要DDL，您只需要一次性设置数据模式等，然后只使用SQL查询、添加或更新数据。

- mjv

---

+1：我忘记了LIMIT/RowNumber()。我们使用CHAR(1) Y/N字段，这似乎很70年代，但至少在Java级别上它们被映射到布尔值。感谢您提供的资源，我会仔细研究它。 - Jim Ferrans

Answer 2

我认为，使用单一的SQL资源文件来维护MySQL和Oracle存在几个缺点，因为它们被困在向后兼容性和解决特定问题之间。最好为每个SQL引擎都有一个SQL，从而最大化每个引擎的功能。

在宣传册中看起来相同的功能可能实现方式却大不相同。

看这些例子：

限制结果集

MYSQL

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n

ORACLE

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
)
WHERE rownumber <= n

Limit—with offset

MYSQL

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip

ORACLE

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
    columns
  FROM tablename
)
WHERE rn > skip AND rn <= (n+skip)

您可以查看这个不同SQL实现的比较。它涵盖了各种SQL数据库，包括MySQL、PostgreSQL和Oracle等。

- RRUZ

---

“+1 for helpfulness”是对有帮助的回答的鼓励。而“maximize the capabilities of each”指的是优化问题：在某些情况下，我们可能需要以不同的方式构建相同的查询以提高性能。索引等问题也是如此。 - Jim Ferrans

Answer 3

除了其他人提到的内容外，Oracle和MySQL处理外连接的方式有很大不同。实际上，Oracle提供了一种语法，而MySQL无法处理，但Oracle可以处理标准语法。

仅适用于Oracle：

SELECT a.foo, b.bar
  FROM a, b
 WHERE a.foo = b.foo(+)

MySQL和Oracle：

SELECT a.foo, b.bar
     FROM a 
LEFT JOIN b 
       ON (a.foo=b.foo)

所以你可能需要转换一些外连接。

- O. Jones

Answer 4

你肯定不能保持DDL相同。就DML而言，虽然有许多相似之处（每个数据库都支持ANSI SQL标准的核心子集），但也存在一些差异。

首先，MySQL使用自动递增值，而Oracle使用序列。虽然可以通过工作来解决这个问题（在Oracle端使用序列+触发器以模拟自动递增），但还是存在差异。内置函数也完全不同。

基本上，取决于您打算使用的内容，可能无法保持一个语句集用于两种情况。顺便说一下，即使使用Hibernate方言，也不总是可能拥有相同的查询集- HQL很棒，但并不总是足够。

- ChssPly76

Answer 5

Oracle将空字符串视为null值。MySQL将空字符串视为空字符串，将null字符串视为null值。

- Gary Myers