我可以编写可移植的SQL语句吗？

#1 楼

不，无论如何都不需要大量实用的代码。您可以尝试遵循标准（例如，使用COALESCE而不是ISNULL），但是大小之间存在太多差异。烦恼的是：


SQL Server支持标识符的双引号和方括号。 MySQL使用反引号。
SQL Server支持TOP，大多数其他数据库使用LIMIT。
PostgreSQL仅是相对较新实现的存储过程，尽管实际上您可以使用函数。
MySQL区分大小写在UNIX上运行时，表名（但不是字段名）在Windows上运行时不是。 SQL Server都不区分大小写（除非有区别）。
CTE和窗口函数并非在所有系统上都可用，并且并非始终实现相同。
SQL Server不需要命令定界符（除非这样做），但是MySQL和Oracle可以。 MySQL在定义存储过程时还需要使用备用定界符。 MS SQL不支持任何此类内容。
不同供应商的安全性几乎总是不同。
错误处理总是不同的。使用这些系统。

许多人已经编写了软件，目的是让一个人编写与RDBMS无关的查询。这些实验大多数都失败了，并且有一些实验脱离了实验室，在整个环境中徘徊，并在破坏中蔓延开来。但即使是最好的，也不会考虑到目标系统编写的代码的性能。

#2 楼

有ANSI SQL标准，例如，参见Wikipedia文章中有关互操作性和标准化的部分。问题是，实际上，很少有人遵循这些标准，这些标准通常是事后编写和创建的，因为多年的历史已经束缚了各种数据库产品的作用，以不同的方式做事。

并不是所有的东西都丢失了。对于中等目标，例如几乎不需要复杂查询和报告的Web应用程序，实现可支持的数据库后端列表是可以实现的目标。例如，上面的列表。仅向其中添加最低版本号，这样您才能真正知道要支持和测试的目标。恐怕您必须进行测试。

在您的应用程序代码中，希望限制自己使用非常基本的SELECT，UPDATE和INSERT。


请查找数据库抽象层，允许您进行参数化的预准备查询。即使根据一种给定的数据库产品中当前启用的设置，转义字符串也可以有很大的不同。如果必须包含固定的字符串文字，请确保其为“单引号”并且不能包含控制字符，空值，反斜杠，引号等。
确保所有标识符（表，列名，别名）都不可能保留关键字（从，选择，左，计数等），从本质上避免所有简单的英语单词。否则，您需要引用它们，这就是一罐蠕虫。最好将所有这些都保留为小写，但不要期望它们会用大写字母返回。
除了GROUP BY查询中的通用聚合函数外，不要指望任何SQL函数。基本上，COUNT（），MIN（），MAX（），SUM（）
加，减，乘数通常是安全的，可以打折数据类型范围的限制。不要期望使用除法或模数，最重要的是不要尝试在SQL Server端串联字符串。当然，他们所有人都可以做到这一点，但是方式略有不同。
请勿尝试使用LIKE运算符。
仅对ORDER BY期望纯数字，并在应用程序端保持按字符串排序。对归类的支持差异很大。如果您要排序的列可以包含NULL，则期望它们可以从上至下进行排序。
如果必须在数据库中存储二进制数据（BLOB，VARBINARY等），则必须进行艰苦的测试和

一一处理所有受支持的数据库后端之间的差异，以进行检索和存储。

如果坚持这些，那么大部分工作将在DDL方面，创建数据库，定义您的表，针对您选择支持的每个数据库进行手工定制。
如今，通常所有内容都支持VIEW，因此您甚至可以抽象出函数，运算符之间的差异，并为您的应用程序提供一致的视图。尽管您必须为每个数据库定义它的方式有所不同，但是您的代码就像是同一张“表”一样。

要注意的痛点：

< br坚持使用带符号的32位或64位整数和bigints。如果必须使用十进制数字，则需要多加注意。不管怎样，只要有定义的后端支持列表，它都是可以实现的。
文本值字符集和长度。如今，您希望能够正确存储和处理包括表情符号在内的所有内容。用这些进行测试，并找出需要什么。例如。从历史上看，MySQL和MariaDB仍然调用所需的utf8mb4，而基础utf8则不行。在Microsoft SQL Server中，您需要_SC归类，并且仅使用NCHAR / NVARCHAR字段，或者从版本15（2019）_SC_UTF8开始。确保它们足够大以容纳所需的字符数。 utf-8 char（4）只能容纳一个表情符号（不带修饰符），不能容纳四个。但是，如果在文本列上必须有任何类型的INDEX，请警惕大尺寸，因为最大限制可能非常低。
文本值，排序规则。即使您如我之前所说的那样认真，并且不依赖于数据库服务器的排序，在确定相等性时排序规则仍然起作用。这对于按值等效选择和唯一键都非常重要！始终保持警惕并测试您正在获得所需的东西。是否区分大小写，重音敏感度等。在不同的DB之间，要获得所需的结果会有很大的不同，但是通常需要注意一些事项。一定要花很多时间。
显然会忘记更多深奥的类型。集，枚举，XML，数组等。
在NULLable列上具有UNIQUE键可能允许任意数量的NULL值，或者恰好允许一个NULL值，具体取决于数据库系统。但是您可以在数据库定义部分中进行改进和处理，以实现所需的功能。

这些天，您也不要轻易将MariaDB和MySQL结合在一起。到目前为止，它们已经发生了重大分歧。像对待它们一样，对它们进行处理和测试。
dbfiddle之类的工具非常有用。并没有真正利用任何特定数据库后端的优势，这是您必须自己解决的问题。
许多博客，CMS和类似系统发现它对例如支持MySQL和PostgreSQL都是有用的至少。

#3 楼

对于足够琐碎的语句-可以，是的。

SELECT field FROM table

应该适用于所有情况，如果您的情况正确的话，因为其中一些DB区分大小写。

对于实际应用中可能需要的任何内容，其他答案随处可见。

#4 楼

语法是一回事（其他答案已经涵盖了这一点），但是看似相同的语句的行为是另一回事。

我认为这也是要提防的事情-甚至更重要，因为出现问题的时间可能很晚才出现。

以下是一些可能令您感到惊奇的示例：

整数除法

如果以下情况，Postgres和SQL Server将返回整数：除以两个整数。在这种情况下，Oracle和MySQL将返回一个十进制值。

获取此示例表：

create table t (nr integer);
insert into t values (1), (1), (2), (2), (2), (3);

并执行查询以计算每个数字出现的百分比：

select nr, count(*) / (select count(*) from t) as pct
from t
group by nr;

Postgres和SQL Server将为每一行返回0（零），而MySQL和Oracle将返回预期的百分比。

LIKE的行为

SQL Server使用某种“穷人的正则表达式”作为LIKE通配符，如果您正在寻找例如，可能会咬住您。方括号。采取以下示例数据：

create table foo (bar varchar(100));
insert into foo values ('2'), ('[42]');

以及以下语句（100％纯ANSI SQL）：

select *
from foo
where bar like '%[42]%';

没有DBMS会抱怨语法。但是，SQL Server将返回两行，而所有其他行将仅返回带有[42]的行。

（我故意在其中输入数字，以免出现区分大小写/不区分大小写的问题）

唯一索引和NULL

请查看此表：

CREATE TABLE foo (col1 integer, col2 integer);
CREATE UNIQUE INDEX idx_foo ON foo (col1, col2);

以上内容几乎可以在每个DBMS上运行而无需更改。

然后考虑两个INSERT语句：

INSERT INTO foo (col1, col2) VALUES (1, null);
INSERT INTO foo (col1, col2) VALUES (1, null);

Postgres和MySQL将愉快地插入这两行，因为NULL从不等于任何东西，因此它们不会违反唯一索引（约束）。

Oracle和SQL Server将拒绝插入第二行。

外键评估

使用此自引用表：

CREATE TABLE fk_test
(
  id          integer PRIMARY KEY,
  name        varchar(20),
  parent_id   integer,
  FOREIGN KEY (parent_id) REFERENCES fk_test (id)
);

以下插入是单个语句（100％ANSI SQL-但Oracle不支持，但这不是重点）。

INSERT INTO fk_test 
  (id, name, parent_id) 
VALUES 
  (4, 'Four', 1),
  (3, 'Three', 2),
  (2, 'Two', 1),
  (1, 'OnNe', null);

因为它是单个原子语句，外键引用均有效。上面的代码在SQL Server和Postgres中运行没有问题，因为将语句视为单个原子INSERT并检查了语句级别的约束。 MySQL失败是因为它逐行检查约束，而不是在语句结束时检查约束。

删除多行时也是如此。假设我们以正确的顺序插入了所有这四行，并且想要删除除根目录以外的所有内容：

DELETE FROM fk_test
WHERE id IN (2,3,4);

再次在MySQL中失败，但在Postgres，Oracle和SQL中有效服务器。

类似的事情可能会因独特的约束而发生。

锁定

（默认）锁定行为也有很大的不同。尽管在Postgres和Oracle中，读取器永远不会阻塞写入器，而写入器也永远不会阻塞读取器（使用FOR UPDATE或LOCK TABLE进行显式锁定），但在SQL Server或MySQL中可能并非如此。 Oracle和Postgres也没有锁升级，因此锁行为通常不受锁数量的影响。


这也是为什么我认为使用DBMS进行测试（而不是用于生产的测试）会使测试变得毫无意义的原因（请考虑：嵌入式/内存引擎，例如H2或HSQLDB）与“真实”的东西）

#5 楼

SQL代码越复杂，可移植性就越差。对于特定的应用程序，我需要支持Oracle和MSSQL。除了其他更复杂的区别外，字符串连接（|| vs +）使我发疯。

然后是驱动程序。您可能会幸运地通过其精心设计的JDBC API在JAVA中访问这些SQL服务器。您可能会对C实现感到有些生气，或者根本找不到其他驱动程序。 YMMV。