我在各自的數(shù)據(jù)庫(kù)中都有WordPress實(shí)例。要進(jìn)行更新，我需要查詢所有活動(dòng)插件，這些插件存儲(chǔ)在表“ wp_options”中，可通過(guò)以下方式訪問(wèn)WHERE option_name='active_plugins'如何訪問(wèn)所有活動(dòng)的插件設(shè)置（分布在多個(gè)數(shù)據(jù)庫(kù)中）并將其輸出到一個(gè)SQL結(jié)果中？我知道database.tablename語(yǔ)法，但是如何使用上面的Where語(yǔ)句從那里繼續(xù)？單個(gè)數(shù)據(jù)庫(kù)中的請(qǐng)求如下所示：SELECT option_value FROM `database`.`wp_options` WHERE option_name="active_plugins"
3 回答
慕萊塢森
TA貢獻(xiàn)1810條經(jīng)驗(yàn) 獲得超4個(gè)贊
SELECT option_value
FROM `database1`.`wp_options`
WHERE option_name="active_plugins"
UNION
SELECT option_value
FROM `database2`.`wp_options`
WHERE option_name="active_plugins"
MMTTMM
TA貢獻(xiàn)1869條經(jīng)驗(yàn) 獲得超4個(gè)贊
Pentium10的解決方案很好，但是它的缺點(diǎn)是您必須為要包含的每個(gè)架構(gòu)擴(kuò)展查詢。以下解決方案使用一條準(zhǔn)備好的語(yǔ)句為MySQL服務(wù)器上具有該表的所有模式生成結(jié)果集wp_options。這對(duì)您來(lái)說(shuō)應(yīng)該更方便。
DROP PROCEDURE IF EXISTS `MultipleSchemaQuery`;
DELIMITER $$
CREATE PROCEDURE `MultipleSchemaQuery`()
BEGIN
declare scName varchar(250);
declare q varchar(2000);
DROP TABLE IF EXISTS ResultSet;
create temporary table ResultSet (
option_value varchar(200)
);
DROP TABLE IF EXISTS MySchemaNames;
create temporary table MySchemaNames (
schemaName varchar(250)
);
insert into MySchemaNames
SELECT distinct
TABLE_SCHEMA as SchemaName
FROM
`information_schema`.`TABLES`
where
TABLE_NAME = 'wp_options';
label1:
LOOP
set scName = (select schemaName from MySchemaNames limit 1);
set @q = concat('select option_value from ', scName, '.wp_options where option_name=\'active_plugins\'');
PREPARE stmt1 FROM @q;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
delete from MySchemaNames where schemaName = scName;
IF ((select count(*) from MySchemaNames) > 0) THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SELECT * FROM ResultSet;
DROP TABLE IF EXISTS MySchemaNames;
DROP TABLE IF EXISTS ResultSet;
END
$$
DELIMITER ;
CALL MultipleSchemaQuery();
添加回答
舉報(bào)
0/150
提交
取消