Я знаю, что Вы можете вставить несколько рядов сразу, есть ли способ обновить несколько рядов сразу (как в в одном вопросе) в MySQL?
Отредактируйте: Например, у меня есть следующее
Name id Col1 Col2
Row1 1 6 1
Row2 2 2 3
Row3 3 9 5
Row4 4 16 8
Я хочу объединить все следующие Обновления в один вопрос
UPDATE table SET Col1 = 1 WHERE id = 1;
UPDATE table SET Col1 = 2 WHERE id = 2;
UPDATE table SET Col2 = 3 WHERE id = 3;
UPDATE table SET Col1 = 10 WHERE id = 4;
UPDATE table SET Col2 = 12 WHERE id = 4;
Да, that' s возможный - Вы можете использовать ВСТАВКУ... НА ДВОЙНОМ КЛЮЧЕВОМ ОБНОВЛЕНИИ.
Используя Ваш пример:
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
Так как у Вас есть динамические ценности, Вы должны использовать ЕСЛИ или СЛУЧАЙ для колонок, которые будут обновлены. Это становится довольно ужасным, но это должно работать.
Используя Ваш пример, Вы могли сделать это как:
< pre> Настольный приемник ОБНОВЛЕНИЯ Col1 = id СЛУЧАЯ КОГДА 1 ТОГДА 1 КОГДА 2 ТОГДА 2 КОГДА 4 ТОГДА 10 ЕЩЕ Col1 КОНЕЦ, Col2 = id СЛУЧАЯ КОГДА 3 ТОГДА 3 КОГДА 4 ТОГДА 12 ЕЩЕ Col2 КОНЕЦ ГДЕ id В (1, 2, 3, 4);Вопрос стар, все же I' d нравится расширять тему с другим ответом.
Мой пункт, самый легкий способ достигнуть его состоит в том, чтобы только обернуть несколько вопросов сделкой. Принятый ответ 'ВСТАВКА... НА ДВОЙНОМ КЛЮЧЕВОМ ОБНОВЛЕНИИ' Хорошее хакерское проникновение, но нужно знать о его недостатках и ограничениях:
Я сделал некоторые промышленные испытания для трех из предложенных вариантов, включая 'ВСТАВКУ... НА ДВОЙНОМ КЛЮЧЕ ОБНОВЛЯЮТ' Вариант, вариант с " случай / когда / then" пункт и наивный подход со сделкой. Вы можете получить кодекс питона и результаты [здесь] (https://gist.github.com/imankulov/5849790). Полное заключение состоит в том, что вариант с заявлением случая оказывается дважды с такой скоростью, как два других варианта, но it' s довольно трудно, чтобы написать правильный и безопасный от инъекции кодекс для него, таким образом, я лично придерживаюсь самого простого подхода: использование сделок.
Отредактируйте: Результаты [Dakusan] (https://stackoverflow.com/users/698632/dakusan) доказывают, что мои исполнительные оценки не совсем действительны. Пожалуйста, см. [этот ответ] (https://stackoverflow.com/a/39831043/848010) для другого, более тщательно продуманного исследования.
Не уверенный, почему другой полезный выбор еще не упомянут:
UPDATE my_table m
JOIN (
SELECT 1 as id, 10 as _col1, 20 as _col2
UNION ALL
SELECT 2, 5, 10
UNION ALL
SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = _col2;
Все следующее относится к InnoDB.
Я чувствую знание, что скорости 3 различных методов важны.
Есть 3 метода:
Я просто проверил это, и метод ВСТАВКИ был 6.7x быстрее для меня, чем ОПЕРАЦИОННЫЙ метод. Я примерил ряд и 3 000 и 30 000 рядов.
ОПЕРАЦИОННЫЙ метод все еще должен бежать, каждый индивидуально подвергает сомнению, который занимает время, хотя он комплектует результаты в памяти или чем-то, выполняя. ОПЕРАЦИОННЫЙ метод также довольно дорогой и в повторении и в регистрациях вопроса.
Еще хуже, метод СЛУЧАЯ был 41.1x медленнее, чем отчеты метода w/30,000 ВСТАВКИ (6.1x медленнее, чем СДЕЛКА). И 75x медленнее в MyISAM. ВСТАВКА и методы СЛУЧАЯ стали безубыточным в ~1 000 отчетах. Даже в 100 отчетах, метод СЛУЧАЯ ЕДВА быстрее.
Так в целом я чувствую, что метод ВСТАВКИ является и лучшим и самым легким использовать. Вопросы меньше и легче читать и только поднять 1 вопрос действия. Это относится и к InnoDB и к MyISAM.
Бонусный материал:
Решение для проблемы ВСТАВКИ «не область по умолчанию» состоит в том, чтобы временно выключить соответствующие способы SQL: 'СЕССИЯ НАБОРА sql_mode=REPLACE (ЗАМЕНЯЮТ (@@ Сессия sql_mode, " STRICT_TRANS_TA BLES" " "), " STRICT_AL L_TABLES" " ")'. Удостоверьтесь, что спасли 'sql_mode' сначала, если Вы планируете возвращение его.
Что касается других комментариев I' ve, замеченный, которые говорят auto_increment, повышается, используя метод ВСТАВКИ, я проверил тот также, и это, кажется, не имеет место.
Кодекс, чтобы запустить тесты следующие. Это также продукция.SQL файлы, чтобы удалить php переводчика наверху
<?
//Variables
$NumRows=30000;
//These 2 functions need to be filled in
function InitSQL()
{
}
function RunSQLQuery($Q)
{
}
//Run the 3 tests
InitSQL();
for($i=0;$i<3;$i++)
RunTest($i, $NumRows);
function RunTest($TestNum, $NumRows)
{
$TheQueries=Array();
$DoQuery=function($Query) use (&$TheQueries)
{
RunSQLQuery($Query);
$TheQueries[]=$Query;
};
$TableName='Test';
$DoQuery('DROP TABLE IF EXISTS '.$TableName);
$DoQuery('CREATE TABLE '.$TableName.' (i1 int NOT NULL AUTO_INCREMENT, i2 int NOT NULL, primary key (i1)) ENGINE=InnoDB');
$DoQuery('INSERT INTO '.$TableName.' (i2) VALUES ('.implode('), (', range(2, $NumRows+1)).')');
if($TestNum==0)
{
$TestName='Transaction';
$Start=microtime(true);
$DoQuery('START TRANSACTION');
for($i=1;$i<=$NumRows;$i++)
$DoQuery('UPDATE '.$TableName.' SET i2='.(($i+5)*1000).' WHERE i1='.$i);
$DoQuery('COMMIT');
}
if($TestNum==1)
{
$TestName='Insert';
$Query=Array();
for($i=1;$i<=$NumRows;$i++)
$Query[]=sprintf("(%d,%d)", $i, (($i+5)*1000));
$Start=microtime(true);
$DoQuery('INSERT INTO '.$TableName.' VALUES '.implode(', ', $Query).' ON DUPLICATE KEY UPDATE i2=VALUES(i2)');
}
if($TestNum==2)
{
$TestName='Case';
$Query=Array();
for($i=1;$i<=$NumRows;$i++)
$Query[]=sprintf('WHEN %d THEN %d', $i, (($i+5)*1000));
$Start=microtime(true);
$DoQuery("UPDATE $TableName SET i2=CASE i1\n".implode("\n", $Query)."\nEND\nWHERE i1 IN (".implode(',', range(1, $NumRows)).')');
}
print "$TestName: ".(microtime(true)-$Start)."<br>\n";
file_put_contents("./$TestName.sql", implode(";\n", $TheQueries).';');
}
Используйте временный стол
// Reorder items
function update_items_tempdb(&$items)
{
shuffle($items);
$table_name = uniqid('tmp_test_');
$sql = "CREATE TEMPORARY TABLE `$table_name` ("
." `id` int(10) unsigned NOT NULL AUTO_INCREMENT"
.", `position` int(10) unsigned NOT NULL"
.", PRIMARY KEY (`id`)"
.") ENGINE = MEMORY";
query($sql);
$i = 0;
$sql = '';
foreach ($items as &$item)
{
$item->position = $i++;
$sql .= ($sql ? ', ' : '')."({$item->id}, {$item->position})";
}
if ($sql)
{
query("INSERT INTO `$table_name` (id, position) VALUES $sql");
$sql = "UPDATE `test`, `$table_name` SET `test`.position = `$table_name`.position"
." WHERE `$table_name`.id = `test`.id";
query($sql);
}
query("DROP TABLE `$table_name`");
}
UPDATE table1, table2 SET table1.col1='value', table2.col1='value' WHERE table1.col3='567' AND table2.col6='567'
Это должно работать на Вас.
Есть ссылка в руководство MySQL для нескольких столов.
Почему не делает никакого упоминания несколько заявлений в одном вопросе ?
В php Вы используете 'multi_query' метод mysqli случая.
MySQL произвольно позволяет иметь несколько заявлений в одной последовательности заявления. Отправка нескольких заявлений сразу уменьшает путешествия туда и обратно клиент-сервер, но требует специальной обработки.
Вот является результат по сравнению с другими 3 методами в обновлении 30 000 сырья. Кодекс может быть найден здесь, который основан на ответе от @Dakusan
Сделка: 5.5194580554962< br> Вставка: 0.20669293403625< br> Случай: 16.474853992462< br> Много: 0.0412278175354< br>
Как Вы видите, несколько вопросов заявлений более эффективны, чем самый высокий ответ.
Если Вы получаете сообщение об ошибке как это:
PHP Warning: Error while sending SET_OPTION packet
Вы, возможно, должны увеличить 'max_allowed_packet' в mysql файле конфигурации, который в моей машине является '/etc/mysql/my.cnf', и затем перезапустите mysqld.
Вы можете псевдоним тот же стол, чтобы дать Вам id' s Вы хотите вставить (если Вы делаете обновление ряда рядом:
UPDATE table1 tab1, table1 tab2 -- alias references the same table
SET
col1 = 1
,col2 = 2
. . .
WHERE
tab1.id = tab2.id;
Кроме того, должно казаться очевидным, что Вы можете также обновить от других столов также. В этом случае обновление удваивается как " SELECT" заявление, давая Вам данные из стола Вы определяете. Вы явно заявляете в своем вопросе ценности обновления так, второй стол незатронут.
Есть урегулирование, Вы можете изменить названный ' много statement' это отключает MySQL' s ' безопасность mechanism' осуществленный, чтобы предотвратить (больше чем один) команду инъекции. Типичный к MySQL' s ' brilliant' внедрение, это также препятствует тому, чтобы пользователь делал эффективные вопросы.
Здесь (http://dev.mysql.com/doc/refman/5.1/en/mysql-set-server-option.html) некоторая информация о внедрении C урегулирования.
Если you' ре используя PHP, Вы можете использовать mysqli, чтобы сделать много заявления (я думаю, что php отправил с mysqli некоторое время теперь),
$con = new mysqli('localhost','user1','password','my_database');
$query = "Update MyTable SET col1='some value' WHERE id=1 LIMIT 1;";
$query .= "UPDATE MyTable SET col1='other value' WHERE id=2 LIMIT 1;";
//etc
$con->multi_query($query);
$con->close();
Надежда, которая помогает.
Вы можете также интересоваться использованием соединений на обновлениях, который возможен также.
Update someTable Set someValue = 4 From someTable s Inner Join anotherTable a on s.id = a.id Where a.id = 4
-- Only updates someValue in someTable who has a foreign key on anotherTable with a value of 4.
Отредактируйте: Если ценности, которые Вы обновляете, являются not' t прибывающий из где-то в другом месте в базе данных, you' ll должен выпустить несколько вопросов обновления.
использовать
REPLACE INTO`table` VALUES (`id`,`col1`,`col2`) VALUES
(1,6,1),(2,2,3),(3,9,5),(4,16,8);
Пожалуйста, отметьте:
Следующее обновит все ряды в одном столе
Update Table Set
Column1 = 'New Value'
Следующий обновит все ряды, где ценность Column2 - больше чем 5
Update Table Set
Column1 = 'New Value'
Where
Column2 > 5
Есть весь Unkwntech' s пример обновления больше чем одного стола
UPDATE table1, table2 SET
table1.col1 = 'value',
table2.col1 = 'value'
WHERE
table1.col3 = '567'
AND table2.col6='567'
Да.. это - возможная ВСТАВКА использования НА sql заявлении ОБНОВЛЕНИЯ ДВОЙНОГО КЛЮЧА.. синтаксис: ВСТАВКА В table_name (a, b, c) ЦЕННОСТИ (1,2,3), (4,5,6) НА ДВОЙНОМ КЛЮЧЕ ОБНОВЛЯЮТ a=VALUES (a), b=VALUES (b), c=VALUES (c)
С PHP я сделал это. Используйте точку с запятой, разделите ее на множество и затем подчинитесь через петлю.
$con = new mysqli('localhost','user1','password','my_database');
$batchUpdate = true; /*You can choose between batch and single query */
$queryIn_arr = explode(";", $queryIn);
if($batchUpdate) /* My SQL prevents multiple insert*/
{
foreach($queryIn_arr as $qr)
{
if(strlen($qr)>3)
{
//echo '<br>Sending data to SQL1:<br>'.$qr.'</br>';
$result = $conn->query($qr);
}
}
}
else
{
$result = $conn->query($queryIn);
}
$con->close();
UPDATE `your_table` SET
`something` = IF(`id`="1","new_value1",`something`), `smth2` = IF(`id`="1", "nv1",`smth2`),
`something` = IF(`id`="2","new_value2",`something`), `smth2` = IF(`id`="2", "nv2",`smth2`),
`something` = IF(`id`="4","new_value3",`something`), `smth2` = IF(`id`="4", "nv3",`smth2`),
`something` = IF(`id`="6","new_value4",`something`), `smth2` = IF(`id`="6", "nv4",`smth2`),
`something` = IF(`id`="3","new_value5",`something`), `smth2` = IF(`id`="3", "nv5",`smth2`),
`something` = IF(`id`="5","new_value6",`something`), `smth2` = IF(`id`="5", "nv6",`smth2`)
//Вы просто строительство его в php как
$q = 'UPDATE `your_table` SET ';
foreach($data as $dat){
$q .= '
`something` = IF(`id`="'.$dat->id.'","'.$dat->value.'",`something`),
`smth2` = IF(`id`="'.$dat->id.'", "'.$dat->value2.'",`smth2`),';
}
$q = substr($q,0,-1);
Таким образом, Вы можете обновить стол отверстия с одним вопросом