最佳方法制作双倍插入。

Question

什么是在表A中插入信息并使用表A的索引与表B关联的最佳方法？

我尝试的“解决方案”是将信息插入表A（具有自动生成的ID），然后选择最后一个索引并将其插入表B。这可能不是非常有用，因为在插入之间，最后一个索引可能会更改，因为另一个用户可以在表A中生成新索引。

我在各种DBMS PostgreSQL、Informix、MySQL和MSSQL中都遇到过这个问题（感谢lomaxx的答案）。

- isc_fausto

---

我认为你的问题需要更加清晰明了。从表A中获取的“索引”并不自动意味着“自动生成的值”，这也是我认为你所指的内容。当有许多并发用户时，“最后一个索引”是不可靠的。 - Jonathan Leffler

Answer 1

如果您正在使用MSSQL，您可以使用SCOPE_IDENTITY返回当前会话中插入的最后一个id。然后，您可以使用它插入到表B中。 此MSDN文章给出了一个不错的示例。

- lomaxx

Answer 2

这是针对Postgres的序列解决方案，当然你需要在存储过程或应用程序代码中进行操作。

postgres=# create table foo(id serial primary key, text varchar);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE

postgres=# create table bar(id int references foo, text varchar);
CREATE TABLE
postgres=# select nextval('foo_id_seq');
 nextval
---------
       1
(1 row)

postgres=# insert into foo values (1,'a'); insert into bar values(1,'b');
INSERT 0 1
INSERT 0 1

对于MySQL，如果您在同一连接中使用多个插入操作，事务很重要，以免自己绊倒。

对于LAST_INSERT_ID()，最近生成的ID是在服务器上按每个连接维护的。它不会被另一个客户端更改，即使您使用非魔术值（即非NULL且非0的值）更新另一个AUTO_INCREMENT列也不会更改它。同时从多个客户端使用LAST_INSERT_ID()和AUTO_INCREMENT列是完全有效的。每个客户端将接收该客户端执行的最后一条语句的最后插入的ID。

mysql> create table foo(id int primary key auto_increment, text varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> create table bar(id int references foo, text varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo(text) values ('x');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bar values (last_insert_id(),'y');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

- Vinko Vrsalovic

Answer 3

另一种选择是创建一个序列，在插入表之前将序列值存储在一个变量中，并使用该变量插入两个表。

- Dheer

---

这不会引起同样的问题吗？ - seFausto

也许无法完全理解您的问题。如果我们将值分配给本地变量，我们将知道该值。不确定两个插入是否在同一位置还是分开。如果您正在对表A进行批量插入，然后再插入表B，则处理起来会更加棘手。 - Dheer

Answer 4

在ORACLE中，使用序列来保持主键值，并使用RETURNING子句。

INSERT INTO table1 ( pk_table1, value1 ) 
   VALUES ( table1_seq.NEXTVAL, p_value1 ) RETURNING pk_table1 INTO l_table1_id;

INSERT INTO table2 ( pk_table2, pk_table1, value2 ) 
  VALUES ( table2_seq.NEXTVAL, l_table1_id, p_value2 );

在Oracle中，最佳实践是使用PACKAGES来存储应用程序的所有SQL /数据操作层。

- Guy

Answer 5

使用IBM Informix Dynamic Server (IDS)实现双重插入取决于您使用的语言。如果是服务器(SPL-存储过程语言)，并且您正在使用SERIAL列，则使用DBINFO('sqlca.sqlerrd2')来表示插入到表B时添加到表A的序列值。如果您在客户端(ESQL/C、I4GL、JDBC、ODBC)中工作，则通过批准的接口(sqlca.sqlerrd[1]在ESQL/C中，sqlca.sqlerrd[2]在I4GL中)收集序列，然后再次传输它。

IDS还支持序列，因此您可以使用该技术代替。

IDS 11.50还支持SERIAL8和BIGSERIAL以及SERIAL(4字节整数);每个接口的详细信息略有不同，但基本原理相同。

- Jonathan Leffler

---

我正在使用JDBC，如何收集序列号？ - seFausto

Answer 6

如果您的表是基于UUID的键，生成UUID并在插入时使用它。

- Constantin

Answer 7

Access 2000+（Jet 4.0）的解决方案在Microsoft Knowledge Base中有描述。基本上，您可以使用SELECT @@Identity来检索生成在您的连接上的自动递增字段的值。

- rhenium

Answer 8

如果你使用的是SQL Server 2005+，你也可以使用OUTPUT子句来输出已更新、插入或删除的数据。它非常酷，正好适用于你需要的类型。

http://msdn.microsoft.com/en-us/library/ms177564.aspx

- Mladen

Answer 9

使用事务来避免这个问题：“这可能并不是非常有用，因为最后一个索引可能会在插入之间更改，因为另一个用户可能会在表A中生成一个新的索引。”

而且，在PostgreSQL中，您可以使用“nextval”和“currval”来实现您想要做的事情：

BEGIN;

INSERT INTO products (prod_id, prod_name, description) VALUES (
    nextval('products_prod_id_seq')
    , 'a product'
    , 'a product description'
);

INSERT INTO prices (price_id, prod_id, price) VALUES (
    nextval('prices_price_id_seq')
    , currval('products_prod_id_seq')
    , 0.99
);

COMMIT;

如果您需要DDL片段，请告诉我。

- Adam Bernier

Answer 10

另一个 Access 2000+ (Jet 4.0) 的解决方案是创建一个 Jet 4.0 的 VIEW（在 Access 中称为保存为查询对象的 SELECT 查询），并在 IDENTITY（自动编号）列上使用 INNER JOIN；连接列必须在 SELECT 子句中公开，并引用表。然后，INSERT INTO 这个 VIEW，为所有没有 DEFAULT 的 NOT NULL 列提供值。

IDENTITY 列的值可以省略，在这种情况下，引擎将像往常一样自动生成该值，或者提供并遵守显式值；如果在其他表中（没有 IDENTITY 列的表）的连接列的值也被提供，则它必须与 IDENTITY 值相同，否则将出现错误；如果省略了 IDENTITY 值，则任何提供给连接列的值都将被忽略。请注意，这些表之间通常会有一个 FOREIGN KEY，但这不是此过程工作的先决条件。

快速示例（ANSI-92查询模式Jet 4.0语法）：

CREATE TABLE Table1 
(
   key_col INTEGER IDENTITY NOT NULL PRIMARY KEY, 
   data_col_1 INTEGER NOT NULL
)
;
CREATE TABLE Table2
(
   key_col INTEGER NOT NULL, 
   data_col_2 INTEGER NOT NULL, 
   PRIMARY KEY (key_col, data_col_2)
)
;
CREATE VIEW View1
AS 
SELECT T1.key_col AS key_col_1, T2.key_col AS key_col_2, 
       T1.data_col_1, T2.data_col_2
  FROM Table2 AS T2
       INNER JOIN Table1 AS T1
          ON T1.key_col = T2.key_col
;
INSERT INTO View1 (data_col_1, data_col_2) 
VALUES (1, 2)
;

- onedaywhen