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

используйте 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 или INSERT … ON DUPLICATE KEY UPDATE.

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

 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 синтаксис, а в части UPDATE ничего не делают, делая какую-то бессмысленную (пустую) операцию, например вычисление 0 + 0 (Geoffray предлагает сделать id = id для оптимизации MySQL чтобы игнорировать эту операцию). Преимущество этого метода заключается в том, что он игнорирует только повторяющиеся ключевые события и все еще прерывает другие ошибки.

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

 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 

при дублировании ключевого обновления или вставке ignore могут быть жизнеспособными решениями с 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] ... ] 

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

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

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

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

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

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

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

Вот 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' ) ); ?> 

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

 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 

Replace может сработать для вас.

Пытаться:

 // Check if exist cod = 56789 include "database.php"; $querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';"); $countrows = mysql_num_rows($querycheck); if($countrows == '1') { // Exist } else { // .... Not exist } 

Или вы можете сделать:

 // Check if exist cod = 56789 include "database.php"; $querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';"); $countrows = mysql_num_rows($querycheck); while($result = mysql_fetch_array($querycheck)) { $xxx = $result['xxx']; if($xxx == '56789') { // Exist } else { // Not exist } } 

Этот метод является быстрым и легким. Для улучшения скорости запроса в вашей большой таблице столбцы INDEX «xxx» (в моем примере).

Есть несколько ответов, которые описывают, как решить эту проблему, если у вас есть индекс 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 выполняется только тогда, когда набор данных не существует.

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

или просто

INSERT IGNORE INTO стол