可移植SQL：唯一主键

Question

试图开发一些可以在较大的RDBMS之间移植的东西。

问题在于 生成 和 使用 自动递增数字作为表的主键。

这里有两个主题

1. 用于生成自动递增数字的机制。
2. 如何指定您要将其用作表的主键。

我正在寻找我 认为 当前状态的验证：

不幸的是，标准化在这方面来得很晚，并且在某些方面仍未实施（作为强制性标准）。这意味着在2013年仍然无法以可移植的方式编写CREATE
TABLE语句…如果您希望使用自动生成的主键进行编写。

真的可以吗？

重新（1）。这是标准化的，因为它来自SQL：2003。据我了解，要走的路是序列。我相信这些是SQL：2003的必需部分，对吗？另一种可能性是IDENTITY关键字，该关键字也在SQL：2003中定义，但据我所知，它是标准的可选部分……这意味着像Oracle这样的关键角色并未实现它。并且仍然可以要求合规。好的，因此SEQUENCEs是为此指定的可移植方法，对吗？

重新（2）。数据库供应商以不同的方式实现此目的。在PostgreSQL中，您可以将CREATE
TABLE语句直接与序列链接，在Oracle中，您必须创建触发器以确保SEQUENCE与表一起使用。

因此，我的结论是，如果没有（2）的标准化解决方案，那么所有主要参与者现在都支持SEQUENCE确实没有多大帮助。我仍然需要为CREATE
TABLE语句之类的简单代码编写特定于db的代码。

这是正确的吗？

除了标准及其实施之外，如果有人对这个问题有一个可移植的解决方案，无论是从RDBMS最佳实践的角度来看，我也将很感兴趣。为了使这种解决方案起作用，它必须独立于任何应用程序，即它必须是解决问题的数据库，而不是应用程序层。也许如果TRIGGER和SEQUENCE的概念都可以说是标准化的，那么将两者结合起来的解决方案便是可移植的了？

Answer 1

至于“便携式创建表语句”：它以数据类型开头：布尔，int或long数据类型是否属于任何SQL标准，我真的很喜欢这些类型。PostgreSql支持这些数据类型，Oracle不支持。具有讽刺意味的是，Oracle在PL
/ SQL中支持布尔值，但不作为表中的数据类型。在Oracle中，甚至表/列名等的长度也限制为30个字符。因此，即使是最简单的“创建表”也并非总是可移植的。

至于自动生成的主键：我不知道可移植的语法，因此我没有在“创建表”中定义它。当然，这只会延迟问题，并将问题留给insert语句。本主题还有另一个问题：以最有效的方式使用JDBC在插入后获取生成的密钥。这在Oracle和PostgreSql之间有很大的不同，如果您曾经敢于在Oracle中使用区分大小写的表/列名称，那将不会很有趣。

至于约束，我更喜欢在“创建表”之后在单独的语句中添加它们。如果您在Oracle中使用char（1）和检查约束来实现布尔数据类型，而PostgreSql直接支持此数据类型，则约束的集合可能会有所不同。

至于“标准”：一个例子

SQL99 standard: for SELECT DISTINCT, ORDER BY expressions must appear in select list

此消息来自PostgreSql，Oracle 11g没有抱怨。14年后，他们会改变吗？

一般来说，您仍然必须编写数据库特定的代码。

关于您的结论：在我们的场景中，我们使用模型驱动的方法实现了一个可移植的数据库应用程序。该逻辑元数据由应用程序使用，并且对于不同的数据库类型，有不同的后端。我们不使用任何ORM，而仅使用“直接SQL”，因为这可以简化SQL语句的调整，并且可以完全访问所有SQL功能。我们编写了自己的库，后来发现关键思想与“规范”相匹配。

好消息是，尽管有很多小麻烦，但即使有复杂的查询，它也能很好地工作。例如，窗口聚合函数非常可移植（row_number（），划分依据）。您必须在Oracle上使用listagg，而在PostgreSql上则需要string_agg。递归命令表表达式在PostgreSql中需要“
with
recursive”，Oracle不喜欢它。PostgreSql在查询中支持“限制”和“偏移”，您需要将其包装在Oracle中。如果您同时在Oracle和PostgreSql中使用SQL数组（表中的列），则会使您发疯。有关于Oracle的物化视图，但在PostgreSql中不存在。出乎意料的是，不仅可以用Java，而且可以用Scala编写数据库存储过程，这在Oracle和PostgreSql中都非常有效。此列表不完整。但是到目前为止，我们设法找到了一个可以解决任何“便携性问题”的可接受的（快速的）解决方案。

它还清吗？在我们的方案中，有一个中央Oracle安装（RAC，读/写），但是在每个应用程序服务器上都有作为本地数据库的分布式PostgreSql安装（只读）。这样可以大大提高性能和可伸缩性，而不会降低成本。

如果您真的只想 在数据库中 解决它，则有一种可能性：将任何东西放在存储过程中，用Java /
Scala编写它们，并限制自己在应用程序中调用这些过程，并读取结果集。当然，这只是将复杂性从应用程序层转移到数据库中，但是您接受了hacks :-)

如果您使用Java存储过程，则触发器是相当标准化的。并且如果您的数据库，您的管理层，您的数据中心人员和您的同事支持它。还应考虑非技术/社会方面。我什至听说过数据库调整人员不接受通用的“左外部联接”语法。他们坚持使用Oracle使用“（+）”的方式。

因此，即使触发器（PL / SQL）和序列已标准化，也需要考虑很多其他事项。

更新

至于返回生成的主键，我只能从JDBC的角度判断情况。

如果您使用 Statement.getGeneratedKeys （我认为这是正常方式），则PostgreSql会返回它。

Oracle要求您指定在创建准备好的语句时要显式获取其值的（主键）列。这是可行的，但前提是您不使用区分大小写的表名。在那种情况下，您收到的只是一个令人误解的
ORA-00942： Oracle的JDBC驱动程序 中没有 抛出 表或视图：
Oracle的JDBC驱动程序中存在/有错误，并且我还没有找到使用可移植的方法获取值的方法。 JDBC方法。因此，在插入后立即在同一事务中以额外的专有“
select sequence.currVal from
double”为代价，您可以取回主键。在我们的例子中，额外的时间是可以接受的，我们比较了插入100000行的时间：PostgreSql更快直到第10000行，在此之后Oracle性能更好。

查看有关从2008年开始获取具有区分大小写的表名的主键和
错误报告的方法的stackoverflow问题

这个例子很好地说明了这些问题。通常，PostgreSQL会按照您期望的方式工作，但是您可能必须为Oracle找到一种特殊的方式。