Как "вставить, если не существует" в MySQL?
Я начал с поиска в Google и нашел эту статью, в которой говорится о таблицах мьютексов.
У меня есть таблица с ~14 миллионами записей. Если я хочу добавить больше данных в том же формате, есть ли способ убедиться, что запись, которую я хочу вставить, еще не существует без использования пары запросов (т. Е. Один запрос для проверки и один для вставки - это набор результатов пусто)?
Делает unique ограничение на поле гарантирует insert потерпит неудачу, если он уже там?
Кажется, что с простым ограничением, когда я запускаю вставку через php, скрипт скрипит.
15 ответов
Использование 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
при обновлении дубликата ключа или вставке игнорировать могут быть жизнеспособные решения с 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] ... ]
Любое простое ограничение должно выполнять работу, если исключение допустимо. Примеры:
- первичный ключ, если не суррогат
- уникальное ограничение на столбец
- многостолбцовое уникальное ограничение
Извините, это кажется обманчиво простым. Я знаю, что это выглядит плохо, если смотреть по ссылке, которую вы нам предоставляете.;-(
Но я все же даю этот ответ, потому что он, кажется, удовлетворяет твои потребности. (Если нет, это может привести к обновлению ваших требований, что также будет "хорошим тоном").
Отредактировано: если вставка нарушит ограничение уникальности базы данных, исключение выдается на уровне базы данных, передаваемом драйвером. Это, безусловно, остановит ваш сценарий с ошибкой. В PHP должно быть возможно решить этот случай...
Попробуйте следующее:
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 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'
)
);
?>
Есть несколько ответов, которые охватывают, как решить эту проблему, если у вас есть 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 по-прежнему будет увеличивать первичный ключ независимо от того, был ли оператор успешным или нет, как и при обычном INSERT.
Это вызовет пробелы в ваших первичных ключах, что может сделать программиста психически нестабильным. Или, если ваше приложение плохо спроектировано и зависит от идеальных дополнительных первичных ключей, это может стать головной болью.
Смотреть в innodb_autoinc_lock_mode = 0 (настройка сервера и имеет небольшое снижение производительности) или сначала используйте SELECT, чтобы убедиться, что ваш запрос не завершится ошибкой (что также приводит к снижению производительности и дополнительному коду).
Обновить или вставить без известного первичного ключа
Если у вас уже есть уникальный или первичный ключ, другие ответы либо INSERT INTO ... ON DUPLICATE KEY UPDATE ... или REPLACE INTO ... должен работать нормально (обратите внимание, что заменить на удаляет, если существует, а затем вставляет - таким образом, не обновляет частично существующие значения).
Но если у вас есть значения для some_column_id а также some_type, комбинация которых, как известно, уникальна. И вы хотите обновитьsome_valueесли существует, или вставить, если не существует. И вы хотите сделать это всего за один запрос (чтобы избежать транзакции). Это могло бы быть решением:
INSERT INTO my_table (id, some_column_id, some_type, some_value)
SELECT t.id, t.some_column_id, t.some_type, t.some_value
FROM (
SELECT id, some_column_id, some_type, some_value
FROM my_table
WHERE some_column_id = ? AND some_type = ?
UNION ALL
SELECT s.id, s.some_column_id, s.some_type, s.some_value
FROM (SELECT NULL AS id, ? AS some_column_id, ? AS some_type, ? AS some_value) AS s
) AS t
LIMIT 1
ON DUPLICATE KEY UPDATE
some_value = ?
По сути, запрос выполняется таким образом (менее сложным, чем может показаться):
- Выберите существующую строку с помощью
WHEREпункт совпадение. - Объединение полученного результата с потенциальной новой строкой (таблица
s), где значения столбца указаны явно (s.id равен NULL, поэтому он будет генерировать новый идентификатор автоинкремента). - Если существующая строка найдена, то потенциальная новая строка из таблицы
sотбрасывается (из-за LIMIT 1 в таблицеt), и он всегда будет вызыватьON DUPLICATE KEYкоторый будетUPDATEвsome_valueстолбец. - Если существующая строка не найдена, то вставляется потенциальная новая строка (как указано в таблице
s).
Примечание. Каждая таблица в реляционной базе данных должна иметь как минимум первичный автоинкремент. idстолбец. Если у вас его нет, добавьте его, даже если он вам не нужен с первого взгляда. Это обязательно нужно для этой "хитрости".
Пытаться:
// 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' (в моем примере).
Это служит цели, если вы не хотите бороться с операторами sql.
Но для этого убедитесь, что столбец установлен как уникальный в базе данных MYSQL.
Пример реализован на PHP. Реализуйте блок try catch.
try {
for($i=0; $<10; $i++){
create($i);
if($i == 3){
create(2);
}
}
}catch (Exception $e) {
echo "Error: ".$e->getMessage();
}
функция для создания находится там, где находится оператор sql. т.е.
function create($id){
$query = 'INSERT INTO '
. $this->table .
' SET
id= '.$id.',
message = test,
';
}
Уверяю вас, если у вас есть уникальный идентификатор в mysql, он не будет вставлять (создавать) и вместо этого выдает ошибку.
И даже лучше, если вы не хотите, чтобы ошибка выдавалась, просто используйте «INSERT IGNORE INTO» вместо «INSERT INTO».
Все варианты, которые предлагались здесь (и на других сайтах), у меня не работали. Каждый из них создает дополнительные проблемы.
Например, с
insert ignoreРаньше я получал сломанные внешние ключи. Потому что он меняет идентификатор обновленного (существующего) элемента.
Итак, что я закончил, - это создание пользовательской ФУНКЦИИ в MySQL.
Вот функция
CREATE function get_or_add(
in_name text
)
returns int
BEGIN
set @id = (select id from room where name = in_name);
if (isnull(@id)) then
insert into room(name) values(in_name);
set @id = last_insert_id();
end if;
return @id;
END
поэтому вы можете вставить родительский элемент следующим образом
insert into house(room_id) values(get_or_add('some room name'))
И это структура таблиц, на которых я тестировал.
CREATE TABLE `room` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `room_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
CREATE TABLE `house` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`room_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `house_FK` (`room_id`),
CONSTRAINT `house_FK` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
INSERT INTO table_name (columns) VALUES (values) ON CONFLICT (id) DO NOTHING;