Я начал с Google, и нашел это статья который говорит о таблицах мьютекса.
У меня есть таблица с ~ 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:
18 октября 2007 г.
Для начала: с последней версии MySQL синтаксис, представленный в заголовке, не
возможный. Но есть несколько очень простых способов сделать то, что
ожидается использование существующего функционала.Есть 3 возможных решения: с помощью INSERT IGNORE, REPLACE или
ВСТАВИТЬ… НА ДУБЛИКАТЕ КЛЮЧЕВОЕ ОБНОВЛЕНИЕ.Представьте, что у нас есть стол:
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
синтаксис, а в части ОБНОВЛЕНИЕ просто ничего не делать, делать некоторые бессмысленные
(пустая) операция, такая как вычисление 0 + 0 (Джеффри предлагает сделать
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 должно быть возможно решить этот случай …
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
Есть несколько ответов, которые охватывают, как решить эту проблему, если у вас есть 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
выполняется только тогда, когда набор данных не существует.