同时支持 Oracle 和 MySQL：它们的 SQL 语法有多相似？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1733273/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Supporting both Oracle and MySQL: how similar is their SQL syntax?
提问by Jim Ferrans
We use Oracle on a project and would like to also support MySQL. How close are their SQL dialects?
我们在一个项目上使用 Oracle，并希望也支持 MySQL。他们的 SQL 方言有多接近？
Is it perhaps even possible to use the same SQL source for both without too many gymnastics?
甚至有可能在没有太多体操的情况下为两者使用相同的 SQL 源吗？
Details:
细节：
- We're using iBatis, a persistence manager that cleanly segregates the SQL statements into resource files. But we work at the SQL level, which has its advantages (and disadvantages).
- We'd prefer not to move to an object-relational mapper like Hibernate, which would fully shield us from dialect differences.
- We've tried hard to keep to a generic subset of Oracle SQL.
- There's no PL/SQL.
- We don't use stored procedures or triggers (yet, anyway).
- We use check constraints, unique constraints, and foreign key constraints.
- We use ON DELETE CASCADEs.
- We use transactions (done at the iBatis API level).
- We call a few Oracle timestamp functions in the queries.
- We would use the InnoDB storage engine with MySQL (it supports transactions and constraints).
- 我们正在使用 iBatis，这是一个持久性管理器，可以将 SQL 语句干净地隔离到资源文件中。但是我们在 SQL 级别工作，这有其优点（和缺点）。
- 我们不希望迁移到像 Hibernate 这样的对象关系映射器，这将完全保护我们免受方言差异的影响。
- 我们一直努力保持 Oracle SQL 的通用子集。
- 没有 PL/SQL。
- 我们不使用存储过程或触发器（无论如何）。
- 我们使用检查约束、唯一约束和外键约束。
- 我们使用 ON DELETE CASCADE。
- 我们使用事务（在 iBatis API 级别完成）。
- 我们在查询中调用了一些 Oracle 时间戳函数。
- 我们将 InnoDB 存储引擎与 MySQL 一起使用（它支持事务和约束）。
So what are your thoughts? Would we need to maintain two different sets of iBatis SQL resource files, one for each dialect, or is it possible to have a single set of SQL supporting both MySQL and Oracle?
所以你的想法是什么？我们是否需要维护两组不同的 iBatis SQL 资源文件，每个方言一个，或者是否可以有一组同时支持 MySQL 和 Oracle 的 SQL？
Final Update:Thanks for all the answers, and especially the pointers to Troels Arvin's page on differences. It's really regrettable that the standard isn't more, well, standard. For us the issues turn out to be the MySQL auto-increment vs. the Oracle sequence, the MySQL LIMIT vs. the Oracle Rowumber(), and perhaps the odd function or two. Most everything else ought to transfer pretty easily, modulo a few edits to make sure we're using SQL-92 as @mjv points out. The larger issue is that some queries may need to be hand-optimized differently in each DBMS.
最后更新：感谢所有答案，尤其是指向 Troels Arvin 的差异页面的提示。真的很遗憾，标准不是更多，好吧，标准。对我们来说，问题是 MySQL 自动递增与 Oracle 序列、MySQL LIMIT 与 Oracle Rowumber()，也许还有一两个奇怪的函数。大多数其他东西都应该很容易转移，以一些编辑为模，以确保我们使用 SQL-92，正如@mjv 指出的那样。更大的问题是某些查询可能需要在每个 DBMS 中进行不同的手动优化。
回答by mjv
Expect a few minor bumps on the road, but on whole should be relatively easy.
预计路上会有一些小颠簸，但总的来说应该相对容易。
From the list of features you currently use, there should only be a few synctactic or semantic differences, in general easy to fix or account for. The fact that you do not use PL/SQL and/or Stored Procedures is a plus. A good rule of thumb is to try and stick to SQL-92 which most DBMSes support, in particular both Oracle and MySQL. (Note this is not the current SQL standard which is SQL-2008).
从您当前使用的功能列表中，应该只有一些语法或语义差异，通常易于修复或说明。您不使用 PL/SQL 和/或存储过程的事实是一个加分项。一个好的经验法则是尝试并坚持大多数 DBMS 支持的 SQL-92，尤其是 Oracle 和 MySQL。（请注意，这不是当前的 SQL 标准，即 SQL-2008）。
A few of the differences:
一些差异：
- "LIMIT" is a famous one: to limit the number of rows to retrieve in the results list, MySQL uses LIMIT n, at the end of the query, Oracle uses RowNumber() in the WHERE clause (which is pain, for you also need to reference it in the SELECT list...)
- Some datatypes are different. I think mostly BOOLEAN (but who uses this ;-) ) Also some I think subtle differences with the DATETIME type/format.
- Some function names are different (SUBSTRING vs. SUBSTR and such...)
- “LIMIT”是一个著名的：限制检索结果列表中的行数，MySQL使用LIMIT n，在查询结束时，Oracle在WHERE子句中使用RowNumber()（这很痛苦，对你来说也是需要在 SELECT 列表中引用它...）
- 有些数据类型是不同的。我认为主要是 BOOLEAN（但谁使用这个 ;-)）还有一些我认为与 DATETIME 类型/格式的细微差别。
- 某些函数名称不同（SUBSTRING 与 SUBSTR 等...）
Just found what seems to be a good resource about differences between SQL implementations.
刚刚发现似乎是关于 SQL 实现之间差异的好资源。
Reading the responses from others, yeah, DDL, could be a problem. I discounted that probably because many applications do not require DDL, you just need to set the data schema etc. at once, and then just use SQL for querying, adding or updating the data.
阅读其他人的回复，是的，DDL，可能是一个问题。我不这么认为，可能是因为许多应用程序不需要 DDL，您只需一次设置数据模式等，然后只需使用 SQL 查询、添加或更新数据。
回答by RRUZ
I believe that maintaining a single set of SQL resource files with MySQL and Oracle, has several disadvantages as being caught between backward compatibility and solve a particular problem. it is best to have a sql for each SQL engine and thus maximize the capabilities of each.
我相信使用 MySQL 和 Oracle 维护一组 SQL 资源文件有几个缺点，因为在向后兼容性和解决特定问题之间被夹住了。最好为每个 SQL 引擎都有一个 sql，从而最大限度地发挥每个引擎的能力。
Features that look identical in a brochure may be implemented very differently.
在小册子中看起来相同的功能可能会以非常不同的方式实现。
see these examples
看这些例子
Limiting result sets
限制结果集
MYSQL
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
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)
You can check this Comparison of different SQL implementations
您可以查看不同 SQL 实现的比较
回答by O. Jones
In addition to the stuff others have mentioned, oracle and mysql handle outer joins quite differently. Actually, Oracle offers a syntax that mySql won't cope with, but Oracle will cope with the standard syntax.
除了其他人提到的内容之外，oracle 和 mysql 处理外连接的方式完全不同。实际上，Oracle 提供了 mySql 无法处理的语法，但 Oracle 会处理标准语法。
Oracle only:
仅甲骨文：
SELECT a.foo, b.bar
FROM a, b
WHERE a.foo = b.foo(+)
mySql and Oracle:
mySql 和 Oracle：
SELECT a.foo, b.bar
FROM a
LEFT JOIN b
ON (a.foo=b.foo)
So you may have to convert some outer joins.
所以你可能需要转换一些外部连接。
回答by ChssPly76
You definitely won't be able to keep your DDL the same. As far as DML goes, there are many similarities (there's a core subset of ANSI SQL standard supported by every database) but there are some differences as well.
您肯定无法保持 DDL 不变。就 DML 而言，有许多相似之处（每个数据库都支持 ANSI SQL 标准的核心子集），但也存在一些差异。
To start, MySQL uses auto increment values and Oracle uses sequences. It's possible to work around this (sequence + trigger on Oracle side to simulate auto increment), but it's there. Built-in functions are quite different.
首先，MySQL 使用自动增量值，而 Oracle 使用序列。可以解决这个问题（Oracle 端的序列 + 触发器来模拟自动增量），但它就在那里。内置函数则大不相同。
Basically, depending on what exactly you intend to use it may or may not be possible to keep one set of statements for both. Incidentally, even with Hibernate dialects it's not always possible to have the same set of queries - HQL is great but not always enough.
基本上，取决于您究竟打算使用什么，它可能会或可能不会为两者保留一组语句。顺便说一句，即使使用 Hibernate 方言，也不总是可能有相同的查询集 - HQL 很棒但并不总是足够。
回答by Gary Myers
Oracle treats empty strings as nulls. MySQL treats empty strings as empty strings and null strings as null strings.
Oracle 将空字符串视为空值。MySQL 将空字符串视为空字符串，将空字符串视为空字符串。
