Как я могу вставить много строк в таблицу MySQL и вернуть новые идентификаторы?

Старый поток, но только что изучил это, так что вот оно: если вы используете InnoDB в последней версии MySQL, вы можете получить список идентификаторов, используя LAST_INSERT_ID() и ROW_COUNT().

InnoDB гарантирует последовательные номера для АВТОМАТИЧЕСКОГО УВЕЛИЧЕНИЯ при выполнении массовых вставок, при условии, что для innodb_autoinc_lock_mode установлено значение 0 (традиционный) или 1 (последовательный). Следовательно, вы можете получить первый идентификатор из LAST_INSERT_ID() и последний, добавив ROW_COUNT()-1.

Единственный способ, которым я могу это сделать, - это если вы сохраните уникальный идентификатор для каждого набора вставленных строк (guid), а затем выберите идентификаторы строк. например:

INSERT INTO t1
(SELECT col1,col2,col3,'3aee88e2-a981-1027-a396-84f02afe7c70' FROM a_very_large_table);
COMMIT;

SELECT id FROM t1 
WHERE guid='3aee88e2-a981-1027-a396-84f02afe7c70';

Вы также можете сгенерировать guid в базе данных, используя uuid()

Предположим, у нас есть таблица с именем temptable с двумя столбцами uid, col1, где uid - поле с автоматическим приращением. Выполнение чего-то подобного ниже вернет все вставленные идентификаторы в наборе результатов. Вы можете просмотреть набор результатов и получить свой идентификатор. Я понимаю, что это старый пост, и это решение может работать не для всех случаев. Но для других это возможно, и поэтому я отвечаю на это.

# lock the table
lock tables temptable write;

#bulk insert the rows;
insert into temptable(col1) values(1),(2),(3),(4);

#get the value of first inserted row. when bulk inserting last_insert_id() #should give the value of first inserted row from bulk op.
set @first_id = last_insert_id();

#now select the auto increment field whose value is greater than equal to #the first row. Remember since you have write lock on that table other #sessions can't write to it. This resultset should have all the inserted #id's
select uid from temptable where uid >=@first_id;

#now that you are done don't forget to unlock the table.
unlock tables;

Стоит отметить, что ответ @Dag Sondre Hansen также можно реализовать, если у вас innodb_autoinc_lock_mode установлено значение 2, просто заблокировав таблицу перед вставкой.

LOCK TABLE my_table WRITE;
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_count = ROW_COUNT();
SET @last_insert_id = LAST_INSERT_ID();
UNLOCK TABLES;
SELECT id FROM my_table WHERE id >= @last_insert_id AND id <= @last_insert_id + (@row_count - 1);

Вот скрипка, демонстрирующая: https://www.db-fiddle.com/f/ahXAhosYkkRmwqR9Y4mAsr/0

Я думаю, вам придется либо обрабатывать идентификатор транзакции в вашем приложении, либо идентификатор элемента в вашем приложении, чтобы сделать это безупречно.

Один из способов сделать это, который может сработать, если все ваши вставки выполнены успешно (!), Заключается в следующем:

Затем вы можете получить вставленные идентификаторы с помощью цикла для количества затронутых строк, начиная с lastid (который является первым вставленным идентификатором массовой вставки). И, таким образом, я проверил, что он работает идеально ... только будьте осторожны, например, HeidiSQL не вернет правильное значение для ROW_COUNT (), вероятно, потому, что это дерьмовый графический интерфейс, делающий случайное дерьмо, о котором мы его не просим - однако это совершенно правильно из любого командная строка или PHP mysqli -

START TRANSACTION;
BEGIN;
INSERT into test (b) VALUES ('1'),('2'),('3');
SELECT LAST_INSERT_ID() AS lastid,ROW_COUNT() AS rowcount;
COMMIT;

В PHP это выглядит так (local_sqle - это прямой вызов mysqli_query, local_sqlec - это вызов mysqli_query + преобразование набора результатов в массив PHP):

local_sqle("START TRANSACTION;
BEGIN;
INSERT into test (b) VALUES ('1'),('2'),('3');");
$r=local_sqlec("SELECT LAST_INSERT_ID() AS lastid,ROW_COUNT() AS rowcount;");
local_sqle("
COMMIT;");
$i=0;
echo "last id =".($r[0]['lastid'])."<br>";
echo "Row count =".($r[0]['rowcount'])."<br>";

while($i<$r[0]['rowcount']){
    echo "inserted id =".($r[0]['lastid']+$i)."<br>";
    $i++;
}

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

Я бы не был уверен, что значение автоинкремента увеличит элемент на 1. И возникнут огромные проблемы, если ваша БД будет иметь репликацию Master // Master и разрешить дублирование исключения auto_increment. AI будет +2 вместо +1, также, если будет еще один мастер, он станет +3. поэтому ретранслируйте такие вещи, как AUTO_INCREMENT, потому что 1 убивает ваш проект.

Я вижу только несколько хороших вариантов для этого.

этот фрагмент SQL не будет иметь проблем с несколькими мастерами и даст хорошие результаты, пока вам не понадобятся только вставленные записи. при нескольких запросах без транзакций можно поймать другие вставки записей.

START TRANSACTION;
SELECT max(id) into @maxLastId FROM `main_table`;
INSERT INTO `main_table` (`value`) VALUES ('first'), ('second') ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
SELECT `id` FROM `main_table` WHERE id > @maxLastId OR @maxLastId IS NULL;
COMMIT;

(если вам также понадобятся обновленные записи с помощью DUPLICATE KEY UPDATE), вам нужно будет немного реорганизовать базу данных, и SQL будет выглядеть следующим образом (безопасно для транзакций и никаких транзакций внутри одного соединения).

#START TRANSACTION    
INSERT INTO bulk_inserts VALUES (null);
SET @blukTransactionId = LAST_INSERT_ID();
SELECT  @blukTransactionId, LAST_INSERT_ID();
INSERT INTO `main_table` (`value`, `transaction_id`) VALUES ('first', @blukTransactionId), ('second', @blukTransactionId) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`), `transaction_id` = VALUES(`transaction_id`);
SELECT  @blukTransactionId, LAST_INSERT_ID();
SELECT id FROM `main_table` WHERE `transaction_id` = @blukTransactionId;
#COMMIT

оба случая безопасны для транснациональных. первый покажет вам только вставленные записи, а второй даст вам все записи, даже обновленные.

также эти параметры будут работать даже с INSERT IGNORE ...

Эта ветка старая, но все эти решения мне не помогли, поэтому я придумал свое.

Сначала посчитайте, сколько строк вы хотите вставить.

скажем, нам нужно добавить 5 строк:

LOCK TABLE tbl WRITE;

SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME   = 'tbl'

затем используйте только что выбранный auto_increment для выполнения следующего запроса:

ALTER TABLE tbl AUTO_INCREMENT = {AUTO_INCREMENT}+5;
UNLOCK TABLES;

Наконец-то вставьте

Используйте зарезервированный диапазон автоинкремента для вставки с идентификатором.

Предупреждение: для этого решения требуется повышенный уровень доступа к таблицам. Но обычно массовые вставки выполняются скриптами crons и импортерами, а что-то еще может использовать специальный доступ в любом случае. Вы не стали бы использовать это только для нескольких вставок.

Это может оставить неиспользуемые идентификаторы, если вы используете ON DUPLICATE KEY UPDATE.

Это можно решить другим способом, если вы знаете количество вставленных строк. Так, например, вы вставили 4 строки, последний идентификатор в вашей таблице был 600.

Итак, сразу после выполнения запроса на вставку вы можете использовать

select last_insert_id()

чтобы получить первый идентификатор вставки, теперь вы можете выполнить этот простой расчет

last_insert_id() значение, которое здесь будет 601 Итак, это будет 601 + 4 - 1 = 604

Итак, все идентификаторы между 601 и 604

Требуется InnoDB. Обязательно включите все запросы в транзакцию.

Изменить-
Решение 2 Работает только при наличии varchar или какого-либо текстового поля. Добавьте случайную строку в varchar или текст поле. Например, случайная строка - это 123sdfsklls113, у вас есть поле name, значение которого равно Sam, поэтому поле в этом случае станет

sam123sdfsklls113

Добавьте случайную строку в конец значения столбца.

После того, как вы вставили строки, теперь вы можете легко получить последние вставленные строки, используя этот

select id from table_name where column_name like '%randomstring_here'

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

update table_name set column_name = replace(column_name, 'randomstring_ here', '') where id in (last inserted ids here)

он удалит случайную строку из значения столбца.