Блог

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

Пример:

 Incomming    My System    Inserted
555          583          583
583
599
  

Моей первоначальной мыслью было использовать INSERT IGNORE , но список идентификаторов для проверки находится в таблице users, а строки вставляются в таблицу events. Я также попробовал ограничение внешнего ключа, но INSERT IGNORE оно не завершается без сбоев с FK — оно выдает ошибку, которая уничтожает все последующие вставки.

Мой текущий угол атаки — считывать все идентификаторы из таблицы users в PHP и проверять идентификаторы в PHP перед попыткой вставки, но это кажется неоптимальным. Есть ли способ MySQL сделать это?

 INSERT INTO your_id_table (ID) 
SELECT ID FROM my_system_table WHERE ID IN (list_of_ids)
  

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

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

 if exists (select * from user where id = @id)
begin
     /*insert*/
end
else
begin
    /*return a status message or insert in error table to track failed records*/
end
  

Это довольно уродливый способ сделать это, но он работает:

 $ids = array( /* ids */ );
$ids = implode(',', $ids);
$sql = 'SELECT `id` FROM `table` WHERE `id` IN ('.$ids.')';

$result = $db -> query($sql);
$exists = $result -> fetch_all();
$doesntExist = array_diff($ids, $exists);
  

Вы могли бы попробовать:

 INSERT INTO Inserted(id) VALUES SELECT id FROM Incoming WHERE id IN (SELECT id FROM My_System)
  

Это выбирает идентификаторы в Incomming, которые также находятся в My_System, и вставляет их в Inserted .

Вы можете сделать что-то вроде этого:

 INSERT INTO events (iduser)
SELECT iduser
FROM users WHERE iduser IN (1,2,3,4,5)
  

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

Если у вас есть больше столбцов для вставки, это будет выглядеть примерно так:

 $idsToInsert = "1,2,3,4,5,6";
$query = "INSERT INTO events (iduser, title)
SELECT iduser, ".$title."
FROM users WHERE iduser IN (".$idsToInsert.")";
  

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

Эту последнюю часть запроса вы должны генерировать динамически через PHP, но это не проблема

Ссылка Mysql для вставки в таблицу, выбранную из, находится здесь: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Вы можете сделать это в коде PHP

 $result = mysql_query("SELECT id FROM another_table WHERE id = '100'");
$number_of_rows = mysql_num_rows($result);
if ($number_of_rows > 0) {
// ID exist in other table
}
  

FK выдает ошибку, которая уничтожает все последующие вставки

Почему это убивает следующие вставки? Делать вставки по одному, try — catch ошибки mysql.

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

 INSERT INTO events (id)
SELECT id FROM my_system ms
LEFT JOIN incoming i 
ON i.id = ms.id
WHERE i.id IS NOT NULL
  

Идея здесь в том, что вы исключаете те записи во входящей таблице, которые не имеют соответствующей записи в таблице my_system, потому что результирующий набор будет равен null для входящих идентификаторов.

Похоже, что некоторые другие люди уже придумали подобное решение! Надеюсь, это поможет, в любом случае.

Внутреннее соединение — самый простой способ его выполнить

 INSERT INTO events (id) 
SELECT incoming.id
FROM my_system ms 
INNER JOIN incoming i  
USING (id);
  

Просто загрузите входящие идентификаторы во входящую таблицу. Кроме того, не забудьте использовать идентификатор в качестве ПЕРВИЧНОГО КЛЮЧА обеих таблиц.

Просто сделайте:

 insert into events (id, value1, value2, ...)
select id, @value1, @value2, ...
from users
where id = @id
  

Таким образом, он будет вставляться только в том случае, если идентификатор уже существует в вашей users таблице. Предполагается id , что значение уникально для каждой строки в users таблице, но если бы оно не было просто добавлено distinct после выбора, чтобы убедиться, что вставлена только одна строка.