考虑到序列/自动增量的不同实现，如何创建可跨不同 RDBMS 工作的 SQL 插入语句？
我正在尝试为一个类创建一种方法，该方法会自动将记录插入表中。问题是我需要使代码适用于 mysql、mssql、postgresql、oracle 和 sqlite。
我能想到的唯一解决方案是：
创建我自己的主键值，但这似乎非常危险，因为可能很难想出一个自动防故障的解决方案来生成主键值。
创建代码，根据正在使用的rdbms驱动程序切换使用的方法，但这看起来很草率，如果可能的话，我想使用符合SQL标准的方式。
创建代码
如果所有这些 RDBMS 使用不同的方法自动递增主键字段，我如何创建此 INSERT 语句以在所有这些 RDBMS 上工作？
I am trying to make a method for a class that will automatically insert a record into a table. The problem is that I need to make the code work for mysql, mssql, postgresql, oracle, and sqlite.
The only solutions I can think of are:
Create my own primary key values, but this seems very risky as it may be hard to come up with a fail-safe solution to generate the primary key values.
Create code that will switch the method used based on the rdbms driver being used, but this seems sloppy and I would like to use an SQL standards compliant way if possible.
How can I create this INSERT statement to work accross all of these rdbms if they all use different methods for auto incrementing a primary key field?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
针对每个 RDBMS 使用专门的代码。
标准合规性很好，但有太多东西并不适用于所有数据库。从简单的事情开始，例如引用字段和值，例如将结果集限制为给定的行数，以及可能的数百种其他情况。
Use specialized code per-rdbms.
Standard compliance is nice, but there are too many things that don't work on all of the databases. Starting with simple things like quoting of fields and values, things like limiting resultset to given number of rows, and possibly hundreds of other cases.
纯通用
唯一有保证的可移植方式，可以跨所有可以想象的数据库执行此操作，并且只有一个实现；是让应用程序将密钥生成为 UUID 并将它们作为常规字段插入。好处是您不需要依赖某些语言或库特定的实现来检索密钥，您已经拥有它。
如果您正在进行数据仓库并且在行
后不更新行，则可以替代 UUID INSERTed 是行内容的加密哈希。让您的应用程序将所有字段内容连接到一个字符串，并提取该字符串的 SHA1 哈希值。该密钥用于所有实际目的，保证对于这些内容是唯一的。复制和查找重复记录也变得轻而易举。序号并不是主键的全部结束。
UUID 键更容易移植，在 2011 年，它们在索引和空间应该一样快这不是问题，除非您有 Facebook 或 Google 大小数据，然后您有钱购买所需的磁盘空间，他们也使用除
长数字之外的其他内容。另一个好处是您还可以更轻松地进行复制，因为您不必担心尝试同步的每个数据库都有重复的键。
SQL Generic
您只需在
INSERT语句中省略该字段，数据库就会为您创建值。PHP 将使检索自动生成的密钥成为以通用方式执行的问题。我能找到的关于该主题的所有内容都非常专门针对正在使用的任何数据库，似乎没有与 PHP 的 JDBC 等效的通用 API。
如果您使用的是 Java
如果您使用 布尔执行(String sql,
int autoGenerateKeys) 上的语句 类。您可以告诉它自动生成的密钥位于哪个字段索引，它将使用 结果集 getGenerateKeys()。
Oracle 特定
如果您在
DDL中的所需自动增量字段上设置DEFAULT值，以调用从DDL中选择下一个值的函数code>sequence 您也可以在 Oracle 中实现所需的自动增量行为。注意
UUID 有时称为GUID，在技术上不同，但在概念上是相同的。
Pure Generic
The only guaranteed portable way to do this across every conceivable database, and only have one single implementation; is to have the application generate the keys as UUIDs and insert them as regular fields. The upside is you don't need to rely on some language or library specific implementation to retrieve the key, you already have it.
An alternative to UUID if you are doing data warehousing and not updating the rows after they are
INSERTed is a cryptographic hash of the contents of the row. Have your application concatenate all the fields contents to one single string and pull a SHA1 hash of that string. That key is for all practical purposes guaranteed to be unique for those contents. Makes replication and finding duplicate records a snap as well.Sequential numbers are not the end all be all of primary keys.
UUID keys are easier to port, and in 2011, they are just as fast when indexing and space should not be an issue unless you have Facebook or Google size data and then you have the money to buy the disk space you need, they use something other than a
longnumber as well.Another benefit is you can also do replication much easier because you don't have to worry about every database you try to sync with having a duplicate key.
SQL Generic
You can just leave off the field in the
INSERTstatement and the database will take care of creating the value for you.PHP is going to make retrieving the auto-generated keys a problematic to do it in a generic way. Everything I can find about the topic is very specialized to whatever database is being used, there doesn't seem to be a generic API equivalent to JDBC for PHP.
If you were using Java
If you use the boolean execute(String sql,
int autoGeneratedKeys) on the Statement class. You can tell it which field index the auto generated key is in and it will return it using ResultSet getGeneratedKeys().
Oracle Specific
If you set the
DEFAULTvalue on the desited auto increment field in yourDDLto call a function that selects the next value from asequenceyou can implement the desired auto increment behavior in Oracle as well.NOTE
UUID is sometimes called GUID, and are technically different but conceptually the same thing.
仅插入带有自动增量字段的记录没有任何问题：通常您只需在插入中省略该字段，数据库将其生成为计算的默认值（这就是为什么它被称为“自动增量”）。
仅当您需要获取该生成字段的值（通常是代理主键）时，才会出现可移植性问题。没有标准的 SQL 方法可以实现这一点。
编辑：哦。根据评论，关于我的第一段（第二段不变）：
确实，并非每个数据库都支持直接开箱即用的“默认”自动增量字段（我的错误）。但是，如果您规定了支持这种插入的模式设计（在 Postgresql、Mysql、MSSQL 和 -我认为 - SQLite 中是直接的；在 Oracle 中不是直接的，但可以通过触发器），那么您可以在以下代码中编写 SQL 插入一种简单且便携的方式。在其他地方，您不能：在这种情况下，您必须编写特定于 RDBMS 的插入。
There is nothing problematic in just inserting records with an auto-increment field: typically you just omit that field in the insert, the database generate it as a computed default value (that's why it's called "auto-increment").
The portability problem arises only when you need to get the value of that generated field (which frequently is a surrogate primary key). No standard SQL way for getting that.
EDITED: oook. As per comments, regarding my first paragraph (the second paragraph stands) :
It's true that not every database supports straighforward out-of-box "DEFAULT" autoincrement fields (mea culpa). However, IF you have stipulated a schema design that supports this kind of insertion (straigtforward in Postgresql, Mysql, MSSQL and -I think- SQLite; not direct but possible -with triggers- in Oracle), THEN you can code your SQL INSERTS in a simple and portable way. ELSEWHERE, you cannot: in that case you must write rdbms-specific INSERTS.
为什么不使用 ORM？ PHP 有很多这样的东西：
Doctrine、Redbean 和许多其他
ORM 的要点是抽象数据库层。这听起来像是您所需要的。您的方法将处理代码中的对象，并且 ORM 具有将这些对象保存到数据库中所需的自定义代码
Why don't you use an ORM? PHP has a ton of them:
Doctrine, Redbean, and many others
The point of an ORM is to abstract the database layer. This sounds like what you need. You method would deal with objects in your code and the ORM has the custom code needed to persist those objects into your database