![如何在多个数据库上创建一个更新查询(mysql)](https://linux22.com/image/579784/%E5%A6%82%E4%BD%95%E5%9C%A8%E5%A4%9A%E4%B8%AA%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%8A%E5%88%9B%E5%BB%BA%E4%B8%80%E4%B8%AA%E6%9B%B4%E6%96%B0%E6%9F%A5%E8%AF%A2%EF%BC%88mysql%EF%BC%89.png)
我在同一个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