有可移植 SQL 之类的东西吗？
根据我的经验，即使有 SQL 标准，编写不经修改就能在大量 RDBMS 上运行的 SQL 也是相当困难的。
因此，我想知道是否有一个 SQL 子集（包括 DDL、模式等）可以在所有主要 RDBMS 上运行，包括 PostgreSQL、MySQL、SQL Server 以及最后但并非最不重要的 Oracle。编写可移植 SQL 时应该避免哪些陷阱？
顺便问一下，是否有一个项目的目标是将 SQL 的有效子集转换为所有这些供应商使用的特定方言？我知道Hibernate和其他ORM系统必须这样做，但我不想要ORM，我想编写直接到数据库的SQL。
谢谢！
In my experience, even though there is a SQL standard, it is quite difficult to write SQL that works, unmodified, over a large number of RDBMS.
Thus, I'd like to know if there is a subset of SQL (including DDL, schemas, etc) that is known to work on all major RDBMS, including PostgreSQL, MySQL, SQL Server and, last but not least, Oracle. What kind of pitfalls should be avoided when writing portable SQL?
By the way, is there a project whose goal is to translate a valid subset of SQL into the specific dialects used by all these vendors? I know that Hibernate and other ORM systems have to do this, but I don't want ORM, I want to write straight-to-database SQL.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
问题在于，一些 DBMS 甚至忽略了最简单的标准（例如，引用字符或字符串连接）。
因此，以下内容（100% ANSI SQL）并不在每个 DBMS 上运行：
而且我什至没有考虑更高级的 SQL 标准，例如递归公用表表达式（即使那些支持它的表达式并不总是符合）或窗口函数（某些仅实现非常窄的子集，有些不支持所有选项）。
关于DDL，存在数据类型的问题。
DATE在任何地方都不相同，就像TIMESTAMP一样。并非每个 DBMS 都有BOOLEAN类型或TIME类型。当涉及到约束或领域时，您会发现更多差异。
简而言之：除非您真的、确实需要独立于 DBMS，否则不要为此烦恼。
话虽如此：如果您确实可以在专有语法和标准语法之间进行选择，请选择标准语法（
OUTER JOIN与(+)或*=< /code>、decode与CASE、nvl与coalesce等）。The problem is that some DBMS even ignore the most simple standards (e.g. like quoting characters or string concatenation).
So the following (100% ANSI SQL) does not run on every DBMS:
And I'M not even thinking about more advanced SQL standards like recursive common table expressions (even those that support it don't always comply) or windowing functions (some only implement a very narrow subset, some do not support all options).
Regarding DDL, there is the problem with data types.
DATEis not the same everywhere, just asTIMESTAMP. Not every DBMS has aBOOLEANtype orTIMEtype.When it comes to constraints or domains you get even more differences.
So in a nutshell: unless you really, really need to be DBMS independent, don't bother with it.
Having said all that: if you do have the choice between a proprietary and standard syntax do choose the standard syntax (
OUTER JOINvs(+)or*=,decodevsCASE,nvlvs.coalesceand so on).在每个 RDBMS 中，列出的符合 ANSI 的内容在所有 RDBMS 中都应该相同，因为这是真正的标准。然而，如果只坚持使用 ANSI（即可移植）的东西，那么您就会失去优化的、特定于供应商的功能。另外，仅仅因为 PostgreSQL 实现了 ANSI 函数并不意味着它在任何其他 RDBMS 中都可用（但如果它可用，那么它应该以相同的方式工作）。
就我个人而言，我认为真正可移植的 SQL 代码或标准化为最低公分母集的项目没有任何价值，因为每个特定的 RDBMS 的优化方式都不同。没有通用的应用语言。如果您使用 C#，那么您不会想使用只能在 PHP 或 JAVA 中找到的东西。因此，只要拥抱您所在的平台就可以了:)。
编辑：如果您正在编写一个可以连接到多个不同 RDBMS 的应用程序，那么您可能需要为每个特定平台找到合适的 SQL，就像每个 ORM 的作者所做的那样。
Within each RDBMS, whatever is listed as ANSI-compliant should be the same across all of them as that is the true standard. However, by sticking with only ANSI (i.e. portable) stuff, then you lose out on the optimized, vendor-specific functionality. Also, just because PostgreSQL implements an ANSI function doesn't mean that it is available in any other RDBMS (but if it is available, then it should work the same).
Personally, I see no value in truly portable SQL code or a project to normalize down to a lowest-common-denominator set as each particular RDBMS is optimized differently. There is no common application language. IF you are using C#, then you wouldn't be wanting to use stuff that can only be found in PHP or JAVA. So just embrace the platform you are on :).
Edit: If you are writing an application that can connect to several different RDBMS's, then you will likely need to find the appropriate SQL for each particular platform, just like the authors of each of the ORM's had to do.
简单的查询几乎总是可移植的。不幸的是，您提供的 SQL 供应商列表在标准合规性方面差异很大。在遵守 ANSI SQL 标准方面，MS SQL Server 名列前茅，而 MySQL 和 Oracle 在遵守标准方面都非常糟糕。当然，这并不是说它们是糟糕的 RDBMS 引擎，或者不能用它们编写强大的查询，但它们对标准的遵守并不完全是它们所闻名的。
请注意，您在该列表中省略了一些大型 RDBMS 厂商，即 Sybase 和 IBM 的 DB2。就其价值而言，这两个通常比其他更符合标准。
Simple queries are almost always portable. Unfortunately, the list of SQL vendors that you provided vary greatly in their standards compliance. MS SQL Server is at the top of the ones that you listed in terms of complying with ANSI SQL standards, and both MySQL and Oracle are notoriously bad when it comes to standards compliance. That, of course, is not to say that they're bad RDBMS engines or that you can't write powerful queries with them, but their adherence to standards is not exactly what they're known for.
Note that you've omitted some big RDBMS players in that list, namely Sybase and IBM's DB2. Those two are generally more standards-compliant than the others, for what that's worth.
我可以在一个产品上编写 SQL 代码并期望它无需修改即可在另一个产品上运行，这种理想是一个不可能实现的梦想。
我宁愿将“可移植性”视为衡量将代码移动到另一个 SQL 产品，或者更重要的是，移动到同一 SQL 产品的更高版本的难易程度的指标，并指出已建立的 SQL 产品往往转向 SQL 标准（例如 SQL-92 的
UPDATE需要标量子查询，因此很冗长，SQL Server 早期提供了专有的JOIN..FROM语法，然后为 SQL Server 2008提供了支持并扩展标准 SQL 的MERGE的MERGE语法。根据经验，请在 SQL 产品支持的情况下使用标准 SQL 代码（例如
CURRENT_TIMESTAMP而不是 SQL Server 的getdate()），否则更喜欢使用易于映射到的专有代码标准 SQL 代码（例如，使用宏，SQL Server 的SUBSTRING()可以轻松映射到标准 SQL 的SUBSTRING()）。请注意，SQL 标准之外的某些函数对于 SQL 产品来说是通用的（例如，大多数/全部将具有MOD()函数或运算符）。The ideal that I can write SQL code on one product and expect it to work on another without modification is an impossible dream.
I rather think of "portability" as being a meaure of how easy it is move code to another SQL product or, crucially, to a later version of the same SQL product, noting that established SQL products tend to move toward the SQL Standards (e.g. SQL-92's
UPDATErequires scalar subqueries hence is long-winded, SQL Server early on provided proprietaryJOIN..FROMsyntax then for SQL Server 2008 provided aMERGEsyntax which supports and extends Standard SQL'sMERGE).As a rule of thumb, use Standard SQL code where your SQL product supports it (e.g.
CURRENT_TIMESTAMPrather than SQL Server'sgetdate()), otherwise prefer proprietary code that easily maps into Standard SQL code (e.g. SQL Server'sSUBSTRING()easily maps to Standard SQL'sSUBSTRING()using a macro). Note that some functions outside of the SQL Standard will be common to SQL products (e.g. most/all will have aMOD()function or operator).我使用 Alpha Five 构建 Web 应用程序，它有一个名为“可移植 SQL”的功能。大约有200个“便携功能”。如果我使用可移植性功能，它将自动转换为本机 SQL 以适合我碰巧使用的任何引擎。
因此，如果我写“SELECT now() FROM client”，now() 是一个可移植函数，该语法将自动转换为本地语言，具体取决于我碰巧使用的语言。它们支持 22 种不同的 SQL 引擎。
I build web applications using Alpha Five which has a feature called "Portable SQL". There are about 200 "Portability Functions". If I use a portability function, it will automatically convert to native SQL to fit whichever engine I happen to use.
So if I write "SELECT now() FROM clients", now() being a portability function, that syntax will automatically be converted to the native language depending on which I happen to use. They support 22 different SQL engines.