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