Лучший способ сделать двойную вставку
Каков наилучший способ вставки информации в таблицу A и использования индекса из таблицы A для связи с таблицей B.
"Решение", которое я попытался, - вставить информацию в таблицу A (которая имеет автоматически сгенерированный идентификатор), затем выбрать последний индекс и вставить его в таблицу B. Это может быть не очень полезно, так как последний индекс может меняться между вставки, потому что другой пользователь может сгенерировать новый индекс в таблице A
У меня была эта проблема с различными СУБД postgreSQL, Informix, MySQL и MSSQL (спасибо lomaxx за ответ)
12 ответов
Если вы используете MSSQL, вы можете использовать SCOPE_IDENTITY, чтобы вернуть последний идентификатор, вставленный в ваш текущий сеанс. Затем вы можете использовать это, чтобы вставить в таблицу B.
Эта статья из MSDN дает достойный пример того, как это сделать.
Это последовательное решение (для 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() последний сгенерированный идентификатор поддерживается на сервере для каждого соединения. Это не изменено другим клиентом. Он даже не изменится, если вы обновите другой столбец AUTO_INCREMENT с немагическим значением (то есть значением, которое не равно NULL и не равно 0). Использование столбцов LAST_INSERT_ID() и AUTO_INCREMENT одновременно от нескольких клиентов вполне допустимо. Каждый клиент получит последний вставленный идентификатор для последнего оператора, выполненного клиентом.
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)
В ORACLE используйте последовательности для хранения значений PK и используйте предложение 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 для хранения всего уровня манипулирования SQL / данными приложения.
Другой вариант - создать последовательность и перед вставкой в таблицу получить значение последовательности в переменной и использовать его для вставки в обе таблицы.
В случае IBM Informix Dynamic Server (IDS) это зависит от языка, который вы используете для реализации двойной вставки. Если это сервер (SPL - язык хранимых процедур), и если вы используете столбец SERIAL, то вы используете DBINFO('sqlca.sqlerrd2') для представления последовательного значения, добавляемого в таблицу A при вставке в таблицу B. Если вы работают в клиенте (ESQL/C, I4GL, JDBC, ODBC), вы собираете серийный номер через утвержденный интерфейс (sqlca.sqlerrd[1] в ESQL/C, sqlca.sqlerrd[2] в I4GL) и затем переносите его вернуться снова.
IDS также поддерживает последовательности, поэтому вы можете использовать эту технику.
IDS 11.50 поддерживает SERIAL8 и BIGSERIAL, а также SERIAL (4-байтовое целое число); подробные интерфейсы немного отличаются для каждого из них, но основной принцип тот же.
Используйте транзакцию, чтобы избежать этой проблемы: "Это может быть не очень полезно, так как последний индекс может меняться между вставками, потому что другой пользователь может сгенерировать новый индекс в таблице 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.
Если ваши таблицы имеют UUID-ключ, сгенерируйте UUID и используйте его в обеих вставках.
Если вы используете sql server 2005+, вы также можете использовать предложение OUTPUT, которое выводит данные, которые были обновлены, вставлены или удалены. Это довольно круто и именно для того типа вещей, которые вам нужны. http://msdn.microsoft.com/en-us/library/ms177564.aspx
Ответ Access 2000+ (Jet 4.0) описан в Базе знаний Microsoft. В основном, вы можете использовать SELECT @@Identity чтобы получить значение поля автоинкремента, которое генерируется в вашем соединении.
В SQL Server вы используете поле @@IDENTITY, а также переносите INSERTв транзакции.
DEFINE ... etc etc
BEGIN TRANSACTION
INSERT INTO table1 ( value1 ) VALUES ( @p_value1 )
SET @pk_table1 = @@IDENTITY
INSERT INTO table2 ( pk_table1, value2 ) VALUES ( @pk_table1, @p_value2 )
COMMIT
В TSQL рекомендуется хранить @@IDENTITY значение в переменной сразу после INSERT чтобы избежать повреждения значения будущим кодом обслуживания.
Также рекомендуется использовать хранимые процедуры.
Если это в Informix и JSP, есть функция, которая возвращает поле Serial таблицы после вставки.
import com.informix.jdbc.*;
cmd = "insert into serialTable(i) values (100)";
stmt.executeUpdate(cmd);
System.out.println(cmd+"...okay");
int serialValue = ((IfmxStatement)stmt).getSerial();
System.out.println("serial value: " + serialValue);
(По какой-то причине в моем рабочем компьютере все описывается по-испански, может, потому что в Мексике)
Другой ответ Access 2000+ (Jet 4.0) - создать Jet 4.0 VIEW (в терминах доступа: SELECT запрос сохранен как объект запроса) с INNER JOIN на IDENTITY (Autonumber) столбец; объединяющие столбцы должны быть представлены в предложении SELECT и в ссылочной таблице. затем INSERT INTO VIEW поставляя значения для всех NOT NULL столбцы, которые не имеют DEFAULT,
IDENTITY значение столбца может быть либо опущено, в этом случае механизм будет автоматически генерировать значение, как обычно, либо явное значение будет предоставлено и выполнено; если значение объединяющего столбца в другой таблице (без IDENTITY колонка), то она должна быть такой же, как IDENTITY значение в противном случае произойдет ошибка; если IDENTITY значение пропускается, тогда любое значение, предоставленное для присоединяемого столбца, будет игнорироваться. Обратите внимание, что FOREIGN KEY обычно можно ожидать между такими таблицами, но это не является обязательным условием для этого процесса.
Быстрый пример (синтаксис ANSI-92 Query Mode 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)
;