Вернуться




Как выполнить запрос «INSERT IF NOT EXIST» в MySQL?




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

Гарантирует ли ограничение unique поля при выполнении insert отказ выполнения, если оно уже существует?

 

Ответ 1

В последней версии 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.

 

Метод 3: использование INSERT… ON DUPLICATE KEY UPDATE:

Третий вариант - использовать INSERT … ON DUPLICATE KEY UPDATE синтаксис, где в части UPDATE просто ничего не делается, выполняется какая-то бессмысленная (пустая) операция, например, вычисляется 0 + 0 (или просто выполняется присвоение id = id для механизма оптимизации MySQL, чтобы игнорировать эту операцию). Преимущество этого метода в том, что он игнорирует только повторяющиеся ключевые события и по-прежнему прерывает выполнение других ошибок.

 

Ответ 2

Решение:

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) 

 

 

Объяснение:

SELECT * FROM `table` 

      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

 

Используемое в качестве WHERE NOT EXISTS-условие определяет, существует ли уже строка с данными для вставки. После того как будет найдена одна такая строка, запрос может остановиться, поэтому LIMIT 1 (микрооптимизация может быть опущена).

Промежуточный запрос:

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

 

представляет значения, которые нужно вставить. DUAL относится к специальной таблице с одной строкой и одним столбцом, которая по умолчанию присутствует во всех базах данных Oracle. На MySQL-сервере версии 5.7.26 я получил действительный запрос при пропуске FROM DUAL, но более старые версии (например, 5.5.60), похоже, требуют секции FROM. При использовании WHERE NOT EXISTS промежуточного запроса возвращается пустой набор результатов, если самый внутренний запрос нашел совпадающие данные.

Внешний запрос:

INSERT INTO `table` (`value1`, `value2`) 

 

вставляет данные, если они были возвращены промежуточным запросом.

 

Ответ 3

Вот функция 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'

  )

);

?>

 

Ответ 4

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

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



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




Cхожие статьи





Какой хостинг выбрать для сайта

Какой хостинг выбрать для сайта

Для беспроблемного функционирования сайта на просторах интернета необходимо ...

15 Февраля 2021    Web


Как и на чём лучше писать сайты?

Как и на чём лучше писать сайты?

Каждый начинающий сайтостроитель мечтает создать свой уникальный сайт на ко ...

21 Февраля 2021    Web


Ищем качественный и недорогой хостинг? Тогда вам сюда

Ищем качественный и недорогой хостинг? Тогда вам сюда

Рано или поздно всем вебмастерам приходится искать хостинг для своего проек ...

21 Февраля 2021    Web




Напишем