Как получить точный первичный ключ auto_increment вставленной строки?

У меня есть 2 таблицы ниже

create table Order(
   OrderId int primary key auto_increment,
   UserId int not null,
   OrderDate date not null,
   IsPaid boolean default false,
   (...Reference statement for UserId...)
);

create table OrderProduct(
   OrderId int not null,
   ProductName varcahr(1024) not null,
   constraint `fk_orderid_from_orderProduct`
   foreign key (OrderId) references Order(OrderId)
);

Таблица Order представляет каждый заказ, сделанный пользователем, Table OrderProduct - это список продуктов, которые содержатся в каждом продукте. Как видите, первичный ключ OrderId для таблицы Order равен auto_increment.

То, что я хочу сделать, это как ниже,

  1. Создать пустой заказ для пользователя по INSERT INTO Order SET UserId=1234, OrderDate="2019-10-11";
  2. И я могу узнать, какой номер используется в качестве первичного ключа для вставленного Order (присваивается auto_increment) и предположить, что это 2368
  3. Добавить продукт в заказ, как, INSERT INTO OrderProduct (OrderId, ProductName) VALUES (2368, "iPhone"), (2368, "Toy"), (2368, "Computer);

Проблема в том, как я могу получить точный OrderId из таблицы Order которая автоматически вставляется auto_increment. Это не проблема, если есть только один сеанс, и все запросы обрабатываются последовательно. Но в отношении многих пользователей, создающих свой заказ, это становится проблемой. (В этом случае обрезка вставленного идентификатора ордера по MAX (OrderId) или LastIndex () (верно?) Не всегда точна, потому что другой пользователь может вставить другой ордер в таблицу (и это увеличит OrderId )

Я SELECT OrderId WHERE UserId="alex0123" and OrderDate="2019-10-11" использовать запрос как ( SELECT OrderId WHERE UserId="alex0123" and OrderDate="2019-10-11" ) Но это не ответ, как вы знаете. Это может обрезать то, что я хочу, но нельзя гарантировать точность, и я думаю, что это неправильно.

Кроме того, я рассматриваю блокировку таблицы при транзакции конкретного пользователя. Но это вызовет проблемы с производительностью. (Пока кто-то обрабатывает свой заказ, другой не может этого сделать)

Есть ли способ справиться с этим? Или есть какая-либо схема или метод БД, чтобы обойти эту проблему?

Всего 2 ответа


Вы можете использовать LAST_INSERT_ID() как он поддерживается для каждого клиента (сеанса). Из руководства:

Сгенерированный идентификатор поддерживается на сервере для каждого подключения . Это означает, что значение, возвращаемое функцией данному клиенту, является первым значением AUTO_INCREMENT, сгенерированным для самого последнего оператора, влияющего на столбец AUTO_INCREMENT этим клиентом .

Таким образом, ваш запрос OrderProduct станет:

INSERT INTO OrderProduct (OrderId, ProductName) VALUES 
(LAST_INSERT_ID(), "iPhone"),
(LAST_INSERT_ID(), "Toy"), 
(LAST_INSERT_ID(), "Computer)

Если оператор SQL вставляет значение для столбца со свойством auto_increment , вставленное значение будет передано клиенту в пакете OK сервера .

В зависимости от языка программирования это значение может быть получено с помощью функции API, например

  • C: mysql_insert_id()
  • Python: cursor->lastrowid
  • PHP: mysqli_insert_id()

Но вы можете получить это значение также с помощью одного оператора SQL:

MariaDB [test]> create table mytable (orderid int unsigned not null auto_increment primary key);
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> insert into mytable values (NULL);
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

Есть идеи?

10000