MySQL在多个数据库中搜索值 [英] MySQL Searching for value across multiple databases

问题描述

我是MySQL的新手，我无法理解如何在多个数据库间进行查询。

I'm new to MySQL and I am having difficulty understanding how to query across multiple databases.

解释基础架构，我目前正在一个作业，我在一个公共测试数据库，有一组数据库。

Explaining the infrastructure, I am currently working on an assignment where I am in a public test Database that has a collection of databases.

我有一个标识符值，我想在特定表中存在的所有数据库中搜索。我将使用表名称table1这个例子。问题是，并非所有数据库都拥有我在表中查找的标识符列。

I have an identifier value that I want to search across all of the databases that I know exists in a specific table. I'll use the table name "table1" for this example. The problem is that, not all of the databases possess the identifier column I am looking for within the table.

我的问题涉及两个部分：
- 我搜索所有的数据库返回所有的数据库名称的集合，其中包含此列中的特定值（table1.id）
- 我可以验证列存在，以便我可以去做检查我查找的id是否匹配其他数据库的table1.id值？

My question involves two parts: -How do I search all of the databases to return a collection of all of the database names that contain a particular value within this column (table1.id) -How can I verify that the column exists so that I can actually go about doing the check to see if the id that I am looking for matches the other databases' table1.id value?

为了更小的规模，我编写了代码检查个别表：

To a smaller scale, I worked out the code for checking an individual table:

SELECT * FROM table1
WHERE searchId = db1.table1.id;

不同的是，我想搜索所有数据库table1的这个特定值，

The difference is, I want to search all of the database table1's for this particular value while insuring that this column exists in the table first.

推荐答案

这会让你开始：

SELECT table_schema 
FROM information_schema.columns 
WHERE table_name = 'table1' AND column_name = 'id'
;

从这里，您可以使用任何编程语言的结果，

From this, you can use the results in whatever programming language you are using to compose queries specific for each of those databases.

或者，我一直在寻找与此类似的边界滥用行为。

Alternately, I've been finding borderline abuses similar to this helpful lately.

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匹配的表的模式。

You concatenate the results of this together, with UNION between, and the resulting query should give you a list of all schemas who have a table with that name (and column) whose value matches 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'
;

这篇关于MySQL在多个数据库中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！