Я начал с гугления и нашел эту статью, в которой говорится о таблицах мьютексов.
У меня есть таблица с ~14 миллионами записей. Если я хочу добавить больше данных в том же формате, есть ли способ убедиться, что запись, которую я хочу вставить, еще не существует, не используя пару запросов (т.е. один запрос для проверки и один для вставки, если набор результатов пуст)?
Гарантирует ли ограничение unique
на поле, что insert
будет неудачным, если запись уже существует?
Похоже, что с простым ограничением, когда я делаю вставку через php, скрипт вылетает.
используйте 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's webcache:.
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
SETensembl_transcript_id
= 'ENSORGT00000000001',transcript_chrom_start
= 12345,transcript_chrom_end
= 12678;Если запись существует, она будет перезаписана; если она еще не существует, она будет создана. Однако использование этого метода неэффективно для нашего случая: нам не нужно перезаписывать существующие записи, достаточно > просто пропустить их. просто пропустить их.
Метод 2: использование INSERT IGNORE Также очень прост:
INSERT IGNORE INTO
transcripts
SETensembl_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 (Джеффри предлагает сделать так. присвоение id=id, чтобы механизм оптимизации MySQL игнорировал эту операцию. операцию). Преимущество этого метода в том, что он игнорирует только дубликаты > ключевых событий. ключевые события, и по-прежнему прерывается при других ошибках.В качестве последнего замечания: этот пост был вдохновлен Xaprb. Я бы также посоветовал ознакомиться с другой его статьей о написании гибких SQL-запросов.
Решение:
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
используется как где не существует
-состояние обнаруживает, если уже существует ряд, с данными, которые будут вставлены. После одной строки такого рода будет найден, то запрос может перестать, следовательно, не более 1
(Микро-оптимизации, могут быть опущены).
Промежуточные запроса
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
представляет значения, которые будут вставлены. Двойной
относится к специальному одна строка, один столбец таблицы присутствует по умолчанию во всех базах данных Oracle (см. https://en.wikipedia.org/wiki/DUAL_table). На MySQL-сервера версия 5.7.26 я получил правильный запрос, когда исключение из ДУАЛЬ, но старые версии (как 5.5.60), кажется, требуют с
информации. С помощью где не существует
промежуточный запрос возвращает пустой результирующий набор, если внутренний запрос найден соответствующие сведения.
Внешний запрос
INSERT INTO `table` (`value1`, `value2`)
вставляет данные, если таковые возвращается промежуточного запроса.
on duplicate key update, или insert 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 функция, которая будет вставлять строку, только если все указанные значения столбцов Дон'т уже существуют в таблице.
Если один из столбцов совпадают, то строка будет добавлена.
Если таблица пуста, то строка будет добавлена.
Если существует ряд, в котором все указанные столбцы имеют указанные значения, строки выиграл'т быть добавлены.
insert_unique функция($таблице $Варс) { если (счет($Варс)) { $таблица = использования mysql_real_escape_string($таблица); $Варс = использование array_map('использования mysql_real_escape_string', $Варс);
Треб $ = на "вставить в долл
(в". присоединяйтесь('
, ', array_keys($Варс)) ."
В) и";
$Треб .= "и выберите ' что". присоединяюсь (про;', '" и, $Варс)&.и"' двойной " - а;
$Треб .= ", где не существует (выберите 1 из $таблица
куда ";
по каждому элементу ($Варс как $Коль =и GT; $Валь)
$Треб .= "в$коль
='$Валь' и ";
$Треб = функцию substr($Треб, 0, -5) . " в) 1 и quot лимита;;
$рез = mysql_query($Треб) или умереть(); возвращение mysql_insert_id(); }
возвращает 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
Есть несколько ответов, которые охватывают как решить эту проблему, если у вас есть "уникальный" индекс, который можно проверить по На дубликат ключа " или " вставить игнорировать
. Это не всегда так, и как "уникальный" имеет ограничение длины (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 является регулярное "обновление" запроса без эффекта, когда набор данных не существует. Запрос 2 является вставка
которая зависит от не существует
, то есть вставить
выполняется только тогда, когда набор данных не'т существуют.
Попробуйте:
// 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
}
}
Этот метод является быстрым и легким. Для повышения скорости выполнения запроса в таблице столбцов индекса 'ХХХ' ( В моем примере ).
То стоит отметить, что вставка игнорировать еще инкремент первичного ключа, является ли заявление удался или не как обычный вставить бы.
Это вызовет пробелы в вашем первичных ключей, что может сделать программист психически неуравновешен. Или если ваше приложение плохо разработаны и зависит от правильного добавочных первичных ключей, то это может стать головной болью.
Посмотри в innodb_autoinc_lock_mode = 0` (сервер настройки, и поставляется с небольшой производительности), или использовать первый, чтобы убедиться, что ваш запрос не завершится ошибкой (с нажмите производительность и дополнительный код).