如何在多个数据库上创建一个更新查询(mysql)

如何在多个数据库上创建一个更新查询(mysql)

我在同一个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..

谢谢

答案1

使用交易。

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;

答案2

这是我使用 bash 想出的解决方案。它假设所有数据库名称都以 为前缀,db_如您的示例中所示。如果存在大量重复数据库,并且您不想在事务中为每个数据库定义更新查询,则此方法非常有用。

mysql -N -B -e "show databases like 'db_%'" \
| while read line; \
do mysql -B -N \
-e "UPDATE mdl_modules SET visible=0 WHERE name IN ("survey","feedback","audio","testing");" \
$line; done

相关内容