Получать все вставленные идентификаторы при вставке нескольких строк с использованием одного запроса

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

У меня есть таблица с именем settings (которая хранит пользовательские настройки), и мне нужно вставить несколько настроек для каждого пользователя. Первоначально я выполнил отдельный оператор insert для каждого параметра, но, почувствовав, что это не очень хороший способ сделать это, я подумал о вставке нескольких строк одним и тем же вложением. Моя единственная проблема заключается в том, что мне нужны идентификаторы auto_incremented каждой из вновь вставленных строк.

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

То, что я сделал, просто. После ввода нескольких строк я вызываю last_insert_id(), чтобы получить идентификатор первой строки одновременно вставленных строк. У меня уже есть счетчик числа вставленных строк, поэтому я просто создаю новый массив и заполняю его идентификаторами, начинающимися с last_insert_id() и заканчивая last_insert_id() + n-1 (где n - количество вставленных строк).

Я чувствую, что это будет работать по следующим причинам:

1.) Документация MYSQL гласит, что last_insert_id() зависит от соединения, и если другой клиент/соединение вставляет новые записи, то это не повлияет на другой клиент last_insert_id().

2.) Я чувствую, что, когда вставка выполняется с помощью одного оператора SQL, вся вставка должна рассматриваться как одна транзакция. Если это так, то должны применяться правила ACID, а значения auto_incremented должны быть последовательными. Я не уверен в этом.

Это мои причины, по которым я чувствую, что логика должна работать. Итак, мой вопрос заключается в том, будет ли приведенная выше логика работать для ВСЕХ условий? Могу ли я полагаться на это, чтобы работать правильно во всех ситуациях? Я знаю, что он работает для меня в настоящее время.

Ответ 1

Если вам нравится играть, то сделайте следующее:)

Чтобы быть уверенным на 99%, вам придется заблокировать таблицу для записи. Вы не уверены, что (в будущем) две транзакции не смогут переплетаться.

Чтобы быть уверенным, что 100% вы прочитали эти значения. (Или проанализировать исходный MySQL) Лучшим решением было бы добавить дату в настройки редактирования таблицы и прочитать последнюю. Если вы не хотите изменять структуру, вы можете использовать триггеры http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html.

Хорошее решение будет обновлять все ваши настройки или только пары: key - setting name

Ответ 2

На это поведение нельзя полагаться; помимо очевидных проблем с блокировкой, скажем, вы хотите настроить master ↔ master replication; внезапно, приращение id на 2 каждый раз.

Кроме того, вместо того, чтобы писать несколько операторов вставки, может быть полезно использовать подготовленные инструкции:

$db = new PDO(...);
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO `mytable` (a, b) VALUES (?, ?)');

foreach ($entries as $entry) {
    $stmt->execute(array($entry['a'], $entry['b']));
    $id = $db->lastInsertId();
}

$db->commit();

Ответ 3

Я согласен с @anigel. Вы не можете быть уверены, что некоторые транзакции не будут запутаны. Вы можете разделить вставки на отдельные запросы, вызвать last_insert_id() для каждого отдельного запроса и заполнить массив результатами.

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

Ответ 4

Как насчет:

$id = array();

$sql = "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";
$sql .= "INSERT INTO `table` VALUES ('', 'foo', 'bar');";

if ($db=new mysqli('host', 'user', 'pass', 'dbase'))
{
    $db->multi_query($sql);

    if ( isset($db->insert_id) ) $id[] = $db->insert_id;

    while ($db->more_results())
    {
        if ($db->next_result()) $id[] = $db->insert_id;
        else trigger_error($db->error);
    }

    $db->close();
}
else trigger_error($db->error);

if (count($id) == 0) trigger_error('Uh oh! No inserts succeeded!');
else print_r($id);

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

Ответ 5

Я уже делаю это мое приложение. Я вставляю записи в цикл, и когда каждая запись вставлена, я буду хранить идентификатор Auto increment в массиве по вызову last_insert_id(). Поэтому я могу использовать массив вставленного идентификатора, когда мне это нужно.

Ответ 6

Я сделал это немного, но не нашел полезные данные в конце, поэтому остановился.

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

Важно установить переменную со временем, а не полагаться на NOW():

  INSERT INTO table (username,privelege,whenadded,whoadded) VALUES ('1','2','$thetime','$theinserter'),('1','5','$thetime','$theinserter'),etc...;

будет хорошо вставлять несколько строк.

чтобы получить искомый массив:

SELECT idrow FROM table WHERE username='1' AND whenadded='$thetime' AND whoadded='$theinserter';

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

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

Ответ 7

Что-то меня беспокоило об этом вопросе... зачем вам нужно вставить insert_id из каждой строки? Мне кажется, что если вы вставляете в таблицу настроек, чтобы скорректировать настройки для пользователя, было бы лучше добавить какой-то пользовательский ключ в таблицу. Я, вероятно, просто не вижу всей картины, но это идея, которую я получаю:

+---------------------------------------+
|  `settings`                           |
+------+--------+-----------+-----------+
|  id  |  user  |  setting  |  value    |
+------+--------+-----------+-----------+
|  `id` INT(11) PRIMARY AUTO_INCREMENT  |
+---------------------------------------+
|  `user` INT(11)                       |
+---------------------------------------+
|  `setting` VARCHAR(15)                |
+---------------------------------------+
|  `value` VARCHAR(25)                  |
+---------------------------------------+

+---------------------------------------+
|  `users`                              |
+------+--------+--------+--------------+
|  id  |  name  |  email |  etc         |
+------+--------+--------+--------------+
|  `id` INT(11) PRIMARY AUTO_INCREMENT  |
+---------------------------------------+
|  `name` VARCHAR(32)                   |
+---------------------------------------+
|  `email` VARCHAR(64)                  |
+---------------------------------------+
|  `etc` VARCHAR(64)                    |
+---------------------------------------+

Мое основное мышление здесь в том, что то, что вы делаете с insert_id (s), - это то, как пытаться создать ссылку между пользователями и настройками, чтобы вы знали, кто задал то, что вы дадите, и верните их настройки позже.

Если я полностью потерял смысл, пожалуйста, верните меня по теме, и я попытаюсь придумать другое решение, но если я удалюсь где угодно, где вы пытаетесь пойти:

Если вы действительно пытаетесь сопоставить две таблицы с помощью insert_id (s), то не будет похоже на код ниже (если у вас есть структура таблицы, аналогичная приведенной выше):

Я предполагаю, что $user является ссылкой на конкретного пользователя (users. id).

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

$mysqli = new mysqli('host', 'username', 'password', 'database') or trigger_error('[MYSQLI]: Could not connect.');

if ($mysqli)
{
    foreach ($settings AS $setting_name=>$setting_value)
    {
        // I'll do individual queries here so error checking between is easy, but you could join them into a single query string and use a $mysqli->multi_query();
        // I'll give two examples of how to make the multi_query string below.
        $sql = "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')";
        $mysqli->query($sql) or trigger_error('[MYSQLI]: ' . $mysqli->error . '['.$sql.']';
    }
    $mysqli->close() // Since we know in this scope that $mysqli was created we need to close it when we are finished with it.
}

Теперь, когда вам понадобятся пользовательские настройки, вы можете сделать SELECT с JOIN, чтобы поместить все настройки и информацию о пользователе вместе и простить меня, если я пошлю этот бит, потому что я, безусловно, не эксперт mysql (i) поэтому я не уверен, что вам нужно будет сделать что-нибудь особенное: в таблице настроек есть несколько записей и одна запись в таблице пользователей, но я уверен, что кто-то может установить нас обоих прямо, если я это испортил:

$mysqli = new mysqli('host', 'username', 'password', 'database') or trigger_error('[MYSQLI]: Unable to connect.');

if ($mysqli)
{

    $sql = "SELECT `users`.`name`, `users`.`email`, `users`.`id`, `users`.`etc`, `settings`.`setting`, `settings`.`value` FROM `settings` JOIN (`users`) ON (`users`.`id`=`settings`.`user`) WHERE `settings`.`user`=$user GROUP BY `settings`.`user` ORDER BY `settings`.`user` ASC";

    if ($result=$mysqli->query($sql))
    {
        if ($result->num_rows == 0)
            echo "Uh oh! $user has no settings or doesn't exist!";
        else
        {
            // Not sure if my sql would get multiple results or if it would get it all in one row like I want so I'll assume multiple which will work either way.
            while ($row=$result->fetch_array())
                print_r($row);  // Just print the array of settings to see that it worked.
        }
        $result->free(); // We are done with our results so we release it back into the wild.
    } else trigger_error('[MYSQLI]: '.$mysqli->error . '['.$sql.']');
    $mysqli->close(); // Our if ($mysqli) tells us that in this scope $mysqli exists, so we need to close it since we are finished.
}

Как я уже говорил ранее, я, безусловно, не эксперт mysql (i), и, вероятно, есть способы упорядочить вещи, и я, возможно, допустил некоторые ошибки с синтаксисом в моей инструкции JOIN или просто использовал лишние claus (ы), такие как GROUP BY. Я сделал SELECT pull из settings и присоединил к нему users, потому что я не был уверен, что присоединение настроек к пользователям приведет к единому результату, содержащему пользователей и все возможные значения, из параметров, которые соответствуют нашему предложению WHERE. Я только присоединился к A с B, где в каждом из них было 1 результат, но я уверен, что JOIN находится в правильной общей области.

В качестве альтернативы нескольким запросам я сказал, что приведу примеры построения одной строки запроса для multi_query, поэтому:

$arr = array();
foreach($settings AS $setting_name=>$setting_value)
{
    $arr[] = "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')";
}
$sql = join('; ', $arr);

или

foreach($settings AS $setting_name=>$setting_value)
{
    $sql = ( isset($sql) ) ? $sql.'; '."INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')" : "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, '$setting_name', '$setting_value')";
}

Последнее, что я хочу отметить: если вам не требуется несколько записей одного и того же параметра для пользователя, тогда вы можете сделать ОБНОВЛЕНИЕ перед запросом INSERT и только сделать INSERT, если результирующий $mysqli- > insert_id = = 0. Если вы пытаетесь сохранить историю изменений настроек для пользователей, и поэтому вам нужно несколько записей, я бы предложил создать отдельную таблицу с журналом, содержащим структуру таблицы, например:

+--------------------------------------------------+
|  `logs`                                          |
+------+--------+--------+-----------+-------------+
|  id  |  time  |  user  |  setting  |  new_value  |
+------+--------+--------+-----------+-------------+
|  `id` INT(11) PRIMARY AUTO_INCREMENT             |
+--------------------------------------------------+
|  `time` TIMESTAMP                                |
+--------------------------------------------------+
|  `user` INT(11)                                  |
+--------------------------------------------------+
|  `setting` INT(11)                               |
+--------------------------------------------------+
|  `new_value` VARCHAR(25)                         |
+--------------------------------------------------+

Если вы делаете по умолчанию time CURRENT_TIMESTAMP или просто вставляете дату ( "Ymd G: i: s" ) в это поле, вы можете отслеживать изменения настроек, вставляя их в журнал при создании новых настроек или изменено.

Чтобы сделать INSERTS, если UPDATE ничего не изменил, вы можете использовать два отдельных оператора. Это также можно сделать с помощью "INSERT VALUES() ON DUPLICATE KEY UPDATE...", но этот метод, по-видимому, не рекомендуется использовать для таблиц с несколькими полями UNIQUE. Использовать последний метод будет означать один запрос, но вам нужно будет сделать комбинацию user и setting UNIQUE (или, возможно, DISTINCT?). Если вы сделали setting UNIQUE, вы могли бы иметь только один setting= 'foo' в таблице, если я не ошибаюсь. Чтобы сделать это с двумя утверждениями, вы сделали бы что-то вроде:

$sql = "UPDATE `settings` SET `value`='bar' WHERE `user`=$user AND `setting`='foo' LIMIT 1";
$mysqli->query() or trigger_error('[MYSQLI]: ' . $mysqli->error . '['.$sql.']');
if ( $mysqli->affected_rows == 0 )
{
    $sql = "INSERT INTO `settings` (`id`, `user`, `setting`, `value`) VALUES ('', $user, 'foo', 'bar')";
    $mysqli->query($sql) or trigger_error('[MYSQLI]: ' . $mysqli->error . '['.$sql.']');
}

В приведенном выше коде вы можете подставить $mysqli- > insert_id для $mysqli- > affected_rows, если хотите, но конечный результат тот же. Оператор INSERT вызывается только в том случае, если UPDATE ничего не изменил в таблице (что указывает на отсутствие записей для этого параметра и этого пользователя).

Я прошу прощения, что это очень длинный ответ, и он отклонился от вашего первоначального вопроса, но я надеюсь, что он согласен с вашей истинной целью/целью. Я с нетерпением жду вашего ответа и комментариев о том, как улучшить мои SQL-запросы от истинных мастеров SQL.