在多个数据库中搜索数值的 MySQL 方法

Question

我刚接触MySQL，对跨多个数据库查询的方法感到困惑。

现在我正在处理一个任务，需要在一个包含多个数据库的公共测试数据库中操作。

我有一个标识值，想要在所有我知道存在特定表中的数据库中搜索这个值。我将在本例中使用表名“table1”。问题是，并非所有数据库都拥有我在表内寻找的标识列。

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

在小范围内，我已经解决了检查单个表格的代码：

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

不同之处在于，我想要搜索所有数据库表1中的特定值，同时确保该列首先存在于表中。

- dpark

---

你现在处于“一个包含多个数据库的公共测试数据库”中。你具体指的是什么？ - Benvorth

我正在使用一个包含多个数据库的数据库，例如： db1 db2 db3 ...然后，在每个数据库中，都有一组表格，这些表格不一定与其他数据库相同。 - dpark

你是指模式吗？在MySQL中，这是一样的...请参见: https://dev59.com/bWgu5IYBdhLWcg3wDS4G - Benvorth

Answer 1

这应该能让你入门：

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'
;

- Uueerdo

---

请原谅我对语法的不了解，但是根据您提到“searchId”的第二个代码块，如果我想在诸如“RNASEQG00000000001”之类的标识符上对所有作为第一个代码块输出结果的数据库进行搜索，我该如何将其纳入第二个代码块以进行搜索？ - dpark

只需将 "WHERE ", column_name, " = ", searchId 更改为 WHERE ", column_name, " = 'RNASEQG00000000001' "。 - Uueerdo

@dpark请查看编辑以获取更通用的、两个语句的解决方案。 - Uueerdo

借助您的帮助，我能够生成一个包含每个有效表格查询语句的字符串列表，这正是我想要的。但是，我该使用什么语法将这些SELECT语句的所有结果UNION到一个输出中呢？ - dpark

你希望最终的查询看起来像[第一个qStr] UNION [第二个qStr] UNION [第三个qStr]等等... - Uueerdo

Answer 2

您可以通过查询 information_schema 来获取MySQL表的列：

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'myschema' -- "database"
AND table_name ='mytablename'

- Benvorth