Mysql

如何在多個數據庫上創建一個更新查詢(mysql)

  • March 6, 2018

我在同一個 mysql 伺服器上有幾個 DBS。DBS 的結構和架構完全一樣(只是數據不同)

某些數據庫上的查詢 像這樣工作得很好(用 phpmyadmin 編寫)

UPDATE  `mdl_modules` SET `visible`=0 
WHERE `name` IN ("survey","feedback","audio","testing")

我認為它可能是這樣的:

UPDATE `db_name1`.`mdl_modules`, `db_name2`.`mdl_modules`,`db_name3`.`mdl_modules`
SET `visible`=0  WHERE `name` 
IN ("survey","feedback","audio","testing")

但這不起作用,我得到一個錯誤當然我有更多的 3 dbs ..

謝謝

使用事務。

START TRANSACTION;
 UPDATE  `db_name1`.`mdl_modules` SET `visible`=0 
   WHERE `name` IN ("survey","feedback","audio","testing");
 UPDATE  `db_name2`.`mdl_modules` SET `visible`=0 
   WHERE `name` IN ("survey","feedback","audio","testing");
 UPDATE  `db_name3`.`mdl_modules` SET `visible`=0 
   WHERE `name` IN ("survey","feedback","audio","testing");
COMMIT;

引用自:https://serverfault.com/questions/373788