У меня есть таблица с 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: использование 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
Web
