SQL Вставить в таблицу, только если запись не существует

Я хочу выполнить набор запросов для вставки некоторых данных в таблицу SQL, но только в том случае, если запись удовлетворяет определенным критериям. В таблице 4 поля: id (основное), fund_id, date и price.

У меня в запросе 3 поля: fund_id, date и price.

Итак, мой запрос будет примерно таким:

INSERT INTO funds (fund_id, date, price)
    VALUES (23, '2013-02-12', 22.43)
    WHERE NOT EXISTS (
       SELECT * 
       FROM funds 
       WHERE fund_id = 23
         AND date = '2013-02-12'
    );

Поэтому я хочу вставить данные только в том случае, если запись, соответствующая fund_id и date, еще не существует. Если приведенное выше верно, это кажется мне довольно неэффективным способом достижения этого, поскольку каждый раз необходимо запускать дополнительный оператор select.

Есть ли лучший способ достичь вышеуказанного?

Изменить: для пояснения ни fund_id, ни date не являются уникальными полями; записи с одним и тем же идентификатором фонда или датой будут существовать, но ни одна запись не должна иметь тот же идентификатор фонда и дату, что и другая.


mysql sql sql-insert
person harryg    schedule 09.05.2013    source источник
comment
Какую СУБД вы используете? SQL Server, MySQ, Oracle, MSAccess?   -  person Adriaan Stander    schedule 09.05.2013
comment
MySQL, хотя я бы хотел, чтобы он потенциально работал и с MSAccess, если возможно   -  person harryg    schedule 09.05.2013
comment
Ознакомьтесь с stackoverflow.com/questions/913841/mysql-conditional-insert   -  person Akash KC    schedule 09.05.2013
comment
кажется, ID и дата - ваши логически уникальные ключи. Почему бы вам не объявить их уникальными и не обработать исключение базы данных при нарушении ограничения уникального ключа?   -  person Bilal Mirza    schedule 09.05.2013
comment
Подход в порядке. Однако ваш sql недействителен, как указано в ответе Тринимона. У вас не может быть предложения where, если вы используете ключевое слово values.   -  person Dan Bracuk    schedule 09.05.2013
comment
@BilalMirza ID и дата уникальны только в сочетании. Т.е. другие записи могут указывать дату или идентификатор, но не то и другое вместе. Если это то, что вы говорите, не могли бы вы подробнее рассказать, как я бы это сделал?   -  person harryg    schedule 09.05.2013

Ответы (3)


arrow_upward
59
arrow_downward

Это может быть простое решение для достижения этой цели:

INSERT INTO funds (ID, date, price)
SELECT 23, DATE('2013-02-12'), 22.5
  FROM dual
 WHERE NOT EXISTS (SELECT 1 
                     FROM funds 
                    WHERE ID = 23
                      AND date = DATE('2013-02-12'));

p.s. в качестве альтернативы (если ID первичный ключ):

 INSERT INTO funds (ID, date, price)
    VALUES (23, DATE('2013-02-12'), 22.5)
        ON DUPLICATE KEY UPDATE ID = 23; -- or whatever you need

см. эту скрипку.

person Trinimon    schedule 09.05.2013
comment
Чем он отличается / лучше моего исходного метода? - person harryg; 09.05.2013
comment
Это не иначе. Но это валидный SQL. @harryg вы пробовали свое утверждение? - person ypercubeᵀᴹ; 09.05.2013
comment
Хорошо, теперь этот должен работать (FROM dual отсутствовал). Скорректировал мой пример, чтобы он соответствовал вашему коду выше - person Trinimon; 09.05.2013
comment
@ypercube Нет, я не пробовал свое утверждение, поскольку на данный момент оно все еще концептуально. Итак, какова цель dual? Я погуглил, но неясно, как работает это утверждение ... - person harryg; 09.05.2013
comment
Это именно то, что вы пытались сделать. Но синтаксис MySQL не позволяет использовать WHERE в операторе INSERT ... VALUES. Приходится переписывать с INSERT ... SELECT. dual - это специальная таблица с одной строкой (и одно из ее применений такое, чтобы создать таблицу с одной строкой, чтобы ее можно было вставить в funds.) - person ypercubeᵀᴹ; 09.05.2013
comment
Да, FROM dual означает, что вы выбираете эти значения в качестве результата запроса (так сказать, из ниоткуда); а выбор значений дает вам возможность добавить WHERE условие - чего VALUES не предлагает. В качестве альтернативы вы можете использовать предложение ON DUPLICATE KEY, но для этого требуется, чтобы идентификатор был первичным ключом; см. обновленную скрипку - person Trinimon; 09.05.2013
comment
Ок, отлично. Я попробовал ваш метод, и, похоже, он работает. Пробовал примерно на 5000 строках, и на выполнение потребовалось около 20 секунд, поэтому не самый эффективный, но адекватный моим потребностям. Спасибо за помощь. - person harryg; 09.05.2013
comment
это много переменных, которые нужно отслеживать / поддерживать. @harryg, ты пробовал ответить? - person gillyspy; 09.05.2013
comment
@gillyspy не уверен, что вы имеете в виду ... Я попробовал ответ выше, и он сработал. Изначально я искал решение, которое делает то же самое, что и выше, но намного более эффективно. - person harryg; 09.05.2013
comment
@harryg, gillyspy: если у вас есть индекс по идентификатору, оба оператора, вероятно, являются самыми быстрыми возможностями. Возможно, вы можете получить дополнительную производительность, выполняя несколько операторов в одной транзакции (т.е. избегайте коммитов после каждого оператора): проверьте, например, stackoverflow.com/questions/5896162/; также интересна блокировка таблиц, см. dev.mysql.com/ doc / refman / 5.1 / en / insert-speed.html - person Trinimon; 09.05.2013
comment
С sqlite можно обойтись без двойной таблицы. Экспресс-тесты дают ожидаемые результаты: ВСТАВИТЬ В фонды ВЫБРАТЬ 23 в качестве идентификатора, ДАТА ('2013-02-12') в качестве даты, 22,5 в качестве цены, ГДЕ НЕ СУЩЕСТВУЕТ (ВЫБРАТЬ * ИЗ фондов ГДЕ ИД = 23 И дата = ДАТА ('2013-02-12')) - person widged; 28.08.2013

arrow_upward
44
arrow_downward

Хотя ответ, который я изначально пометил как выбранный, правильный и соответствует тому, что я просил, есть лучший способ сделать это (что другие признали, но не вдавались в подробности). Составной уникальный индекс должен быть создан для таблицы, состоящей из fund_id и date.

ALTER TABLE funds ADD UNIQUE KEY `fund_date` (`fund_id`, `date`);

Затем при вставке записи добавьте условие при возникновении конфликта:

INSERT INTO funds (`fund_id`, `date`, `price`)
    VALUES (23, DATE('2013-02-12'), 22.5)
        ON DUPLICATE KEY UPDATE `price` = `price`; --this keeps the price what it was (no change to the table) or:

INSERT INTO funds (`fund_id`, `date`, `price`)
    VALUES (23, DATE('2013-02-12'), 22.5)
        ON DUPLICATE KEY UPDATE `price` = 22.5; --this updates the price to the new value

Это обеспечит гораздо лучшую производительность для подзапроса и улучшит структуру таблицы. Он поставляется с предупреждением о том, что вы не можете иметь значения NULL в ваших уникальных ключевых столбцах, поскольку они по-прежнему обрабатываются MySQL как значения.

person harryg    schedule 13.11.2013
comment
Ваше решение работает хорошо, намного аккуратнее и элегантнее. Но есть ли разница в производительности? В любом случае ON DUPLICATE KEY не выполняет подзапрос? - person Dimitris Sfounis; 13.11.2014
comment
Производительность UNIQUE зависит от механизма хранения. В TokuDB это не рекомендуется ... - person gdm; 09.01.2015
comment
Это не работает - person Mahdi; 01.05.2017

arrow_upward
6
arrow_downward

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

FIDDLE

insert into funds (ID, date, price) 
select 
    T.* 
from 
    (select 23 ID,  '2013-02-12' date,  22.43 price) T  
        left join 
    funds on funds.ID = T.ID and funds.date = T.date
where 
    funds.ID is null
person gillyspy    schedule 09.05.2013