这应该使您开始:
SELECT table_schema
FROM information_schema.columns
WHERE table_name = 'table1' AND column_name = 'id'
;
由此,您可以以任何编程语言使用结果来构成针对每个数据库的特定查询。
另外,最近我也发现与此类似的边界滥用行为。
SELECT CONCAT("SELECT '", table_schema, "' "
"FROM `", table_schema, "`.`", table_name, "` "
"WHERE `", column_name, "` = ", searchId
) AS qStr
FROM information_schema.columns
WHERE table_name = 'table1' AND column_name = 'id'
;
您将它们的结果以及UNION
两者之间串联在一起,结果查询将为您提供所有模式的列表,这些模式的表名称和字段名称均与searchId相匹配。
编辑:用单引号替换了上面不适当的反引号,并…在下面添加了这个。
SET @criteriaVal := "'somestring'";
-- SET @criteriaVal := 3; -- for example
SELECT CONCAT("SELECT '", table_schema, "' "
"FROM `", table_schema, "`.`", table_name, "` "
"WHERE `", column_name, "` = ", @criteriaVal
) AS qStr
FROM information_schema.columns
WHERE table_name = 'table1' AND column_name = 'id'
;