Блог

Есть ли какой-либо способ массового выполнения запроса, подобного INSERT OR UPDATE , на сервере MySQL?

 INSERT IGNORE ...
  

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

 REPLACE ...
  

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

 INSERT ... ON DUPLICATE KEY UPDATE
  

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

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

Вы можете вставить / обновить несколько строк с помощью INSERT … ПРИ ОБНОВЛЕНИИ ДУБЛИРУЮЩЕГО КЛЮЧА. В документации приведен следующий пример:

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

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

Один из возможных способов сделать это — создать временную таблицу, вставить в нее данные, а затем выполнить 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
  

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

Обновить

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

Хотя на этот вопрос уже был дан правильный ответ (что 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)
  

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

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

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

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

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