Объем MySQL INSERT или UPDATE

Есть ли способ выполнить массовый запрос типа INSERT OR UPDATE на сервере MySQL?

INSERT IGNORE ...

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

REPLACE ...

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

INSERT ... ON DUPLICATE KEY UPDATE

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

Поэтому я хотел бы знать, есть ли какая-либо команда типа INSERT ... ON DUPLICATE KEY UPDATE, которая может быть выпущена навалом (более одной строки одновременно).

Ответ 1

Вы можете вставить/обновить несколько строк с помощью INSERT... ON DUPLICATE KEY UPDATE. документация имеет следующий пример:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Или я не понимаю ваш вопрос?

Ответ 2

Один из возможных способов сделать это - создать временную таблицу, вставить в нее данные, а затем сделать 1 запрос с соединением, чтобы вставить записи, которые не существуют, и обновлять их до тех полей, которые существуют. Основы будут примерно такими.

CREATE TABLE MyTable_Temp LIKE MyTable

LOAD DATA INFILE..... INTO MyTable_Temp

UPDATE MyTable INNER JOIN 
MyTable_Temp
ON MyTable.ID=MyTable_Temp.ID
SET MyTable.Col1=MyTable_Temp.Col1, MyTable.Col2=MyTable_Temp.Col2.....

INSERT INTO MyTable(ID,Col1,Col2,...)
SELECT ID,Col1,Col2,... 
FROM MyTable_Temp
LEFT JOIN MyTable 
ON MyTable_Temp.ID = MyTable.ID
WHERE myTable.ID IS NULL

DROP TABLE MyTable_Temp

Синтаксис может быть не точным, но это должно дать вам основы. Кроме того, я знаю, что это не очень, но он выполняет свою работу.

Обновление

Я поменял порядок вставки и обновления, потому что при первой вставке все вставленные строки должны обновляться при вызове обновления. Если вы сначала обновляете, обновляются только существующие записи. Это должно означать немного меньше работы для сервера, хотя результаты должны быть одинаковыми.

Ответ 3

Хотя этот вопрос уже правильно ответил (что MySQL поддерживает это через ON DUPLICATE UPDATE с ожидаемым синтаксисом набора значений), я хотел бы расширить его, предоставив демонстрацию, что любой, у кого MySQL может работать:

CREATE SCHEMA IF NOT EXISTS `test`;
DROP TABLE IF EXISTS test.new_table;
CREATE TABLE test.new_table (`Key` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`Key`)) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8;

SELECT * FROM test.new_table;
INSERT INTO test.new_table VALUES (1),(2),(3),(4),(5) ON DUPLICATE KEY UPDATE `Key`=`Key`+100;
SELECT * FROM test.new_table;
INSERT INTO test.new_table VALUES (1),(2),(3),(4),(5) ON DUPLICATE KEY UPDATE `Key`=`Key`+100;
SELECT * FROM test.new_table;

Выход выглядит следующим образом:

Empty set (0.00 sec)

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

+-----+
| Key |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
5 rows in set (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 5  Duplicates: 5  Warnings: 0

+-----+
| Key |
+-----+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
+-----+
5 rows in set (0.00 sec)

Ответ 4

Попробуйте добавить триггер вставки, который выполняет предполетную проверку, и отменяет вставку на дубликат ключа (после обновления существующей строки).

Не уверен, что он будет хорошо масштабироваться для объемных вставок, не говоря уже о работе с данными загрузки данных, но это лучшее, что я могу придумать.: -)

Ответ 5

Если вы использовали Oracle или Microsoft SQL, вы можете использовать MERGE. Однако MySQL не имеет прямой корреляции с этим утверждением. Существует однострочное решение, о котором вы упомянули, но, как вы указали, оно не очень хорошо работает. Вот сообщение в блоге, которое я нашел о различии между Oracle и MySQL и о том, как сделать то, что делает Oracle с MERGE в MySQL:

http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/

Это не очень приемлемое решение, и это, вероятно, не такое полное решение, как хотелось бы, но я считаю, что это лучшее, что есть для решения.