Как «вставить, если не существует» в MySQL?

Я начал с Google, и нашел это статья который говорит о таблицах мьютекса.

У меня есть таблица с ~ 14 миллионами записей. Если я хочу добавить больше данных в том же формате, есть ли способ убедиться, что запись, которую я хочу вставить, еще не существует без использования пары запросов (т. Е. Один запрос для проверки и один для вставки — это набор результатов пусто)?

Делает unique ограничение на поле гарантирует insert потерпит неудачу, если он уже там?

Кажется, что с просто ограничение, когда я выпускаю вставку через php, скрипт скрипит.

+719

Решение

использование INSERT IGNORE INTO table

увидеть http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

есть также INSERT … ON DUPLICATE KEY UPDATE Синтаксис, вы можете найти объяснения на dev.mysql.com


Пост от bogdan.org.ua согласно Веб-кеш Google:

18 октября 2007 г.

Для начала: с последней версии MySQL синтаксис, представленный в заголовке, не
возможный. Но есть несколько очень простых способов сделать то, что
ожидается использование существующего функционала.

Есть 3 возможных решения: с помощью INSERT IGNORE, REPLACE или
ВСТАВИТЬ… НА ДУБЛИКАТЕ КЛЮЧЕВОЕ ОБНОВЛЕНИЕ.

Представьте, что у нас есть стол:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Теперь представьте, что у нас есть автоматический конвейер, импортирующий стенограммы
метаданные от Ensembl, и что по разным причинам конвейер
может быть сломан на любом этапе исполнения. Таким образом, нам нужно обеспечить два
вещи: 1) повторные казни трубопровода не разрушат наши
базы данных, и 2) повторные казни не умрут из-за ‘дубликата
ошибки первичного ключа.

Способ 1: использование REPLACE

Это очень просто:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

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

Способ 2: использование INSERT IGNORE Также очень просто:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = ‘ENSORGT00000000001′,
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Здесь, если «ensembl_transcript_id» уже присутствует в
база данных, она будет молча пропущена (игнорируется). (Чтобы быть более точным,
Вот цитата из справочника MySQL: «Если вы используете IGNORE
ключевое слово, ошибки, возникающие при выполнении оператора INSERT
вместо этого рассматривается как предупреждение. Например, без IGNORE, строка, которая
дублирует существующий индекс UNIQUE или значение PRIMARY KEY в таблице
вызывает ошибку повторяющегося ключа, и оператор отменяется. «.) Если
запись еще не существует, она будет создана.

Этот второй метод имеет несколько потенциальных недостатков, в том числе
не прерывание запроса в случае возникновения любой другой проблемы (см.
руководство). Таким образом, его следует использовать, если он был предварительно протестирован без
Ключевое слово IGNORE.

Есть еще один вариант: использовать INSERT… ON DUPLICATE KEY UPDATE
синтаксис, а в части ОБНОВЛЕНИЕ просто ничего не делать, делать некоторые бессмысленные
(пустая) операция, такая как вычисление 0 + 0 (Джеффри предлагает сделать
id = идентификатор для механизма оптимизации MySQL, чтобы игнорировать это
операция). Преимущество этого метода в том, что он игнорирует только дубликаты
ключевые события, и все еще прерывает на других ошибках.

В качестве последнего уведомления: этот пост был вдохновлен Xaprb. Я бы также посоветовал
обратитесь к его другому посту по написанию гибких SQL-запросов.

716

Другие решения

INSERT INTO `table` (value1, value2)
SELECT 'stuff for value1', 'stuff for value2' FROM `table`
WHERE NOT EXISTS (SELECT * FROM `table`
WHERE value1='stuff for value1' AND value2='stuff for value2')
LIMIT 1

В качестве альтернативы, внешний SELECT заявление может относиться к DUAL чтобы обработать случай, когда таблица изначально пуста:

INSERT INTO `table` (value1, value2)
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table`
WHERE value1='stuff for value1' AND value2='stuff for value2')
LIMIT 1
167

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


Пример при обновлении дубликата ключа обновление на основе mysql.com

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Пример вставить игнорировать основанный на mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

Или же:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

Или же:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
49

Любое простое ограничение должно выполнять работу, если исключение допустимо. Примеры :

  • первичный ключ, если не суррогат
  • уникальное ограничение на столбец
  • многостолбцовое уникальное ограничение

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

Но я все равно даю этот ответ, потому что он, кажется, удовлетворяет твои потребности. (Если нет, это может привести к тому, что вы обновите свои требования, что также будет «хорошим тоном»).

отредактированный: Если вставка нарушит ограничение уникальности базы данных, исключение — выброс на уровне базы данных, ретранслируемый драйвером. Это, безусловно, остановит ваш сценарий с ошибкой. В PHP должно быть возможно решить этот случай …

24
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Если запись существует, она будет перезаписана; если он еще не существует, он будет создан.

18

Вот функция PHP, которая вставит строку, только если все указанные значения столбцов еще не существуют в таблице.

  • Если один из столбцов отличается, строка будет добавлена.

  • Если таблица пуста, строка будет добавлена.

  • Если существует строка, в которой все указанные столбцы имеют указанные значения, строка не будет добавлена.

    function insert_unique($table, $vars)
    {
    if (count($vars)) {
    $table = mysql_real_escape_string($table);
    $vars = array_map('mysql_real_escape_string', $vars);
    
    $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
    $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
    $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
    
    foreach ($vars AS $col => $val)
    $req .= "`$col`='$val' AND ";
    
    $req = substr($req, 0, -5) . ") LIMIT 1";
    
    $res = mysql_query($req) OR die();
    return mysql_insert_id();
    }
    
    return False;
    }
    

Пример использования:

<?php
insert_unique('mytable', array(
'mycolumn1' => 'myvalue1',
'mycolumn2' => 'myvalue2',
'mycolumn3' => 'myvalue3'
)
);
?>
18

Попробуйте следующее:

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
INSERT INTO beta (name) VALUES ('John')
INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END
16

Есть несколько ответов, которые охватывают, как решить эту проблему, если у вас есть UNIQUE Индекс, который вы можете проверить с помощью ON DUPLICATE KEY или же INSERT IGNORE, Это не всегда так, и как UNIQUE имеет ограничение длины (1000 байт), вы не сможете изменить это. Например, мне пришлось работать с метаданными в WordPress (wp_postmeta).

Я наконец решил это двумя запросами:

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

Запрос 1 является регулярным UPDATE запрос без эффекта, когда рассматриваемый набор данных отсутствует. Запрос 2 является INSERT который зависит от NOT EXISTSто есть INSERT выполняется только тогда, когда набор данных не существует.

5
По вопросам рекламы [email protected]