将多个表从多个模式从 MySQL 加载到 R，并合并所有表

Question

在我开始之前，我知道存在以下问题： 获取具有来自多个 MySQL 表的合并值的 R 数据帧，以及在 R 中合并多个 MySQL 表的最佳方法。但我不能将它们用于我的问题。

我在MySQL数据库中有一个模式列表(假设80个模式)，具有特定的ProductID，例如shj637，jsfh748，hasd548等。我在所有这些架构中添加了一个具有特定名称的特定表作为ProductINFO(因此所有架构都有此表)。它们都具有相同的列名称(如 ID、名称、产品组等)，但行中的值不同。

我想做的是创建一个循环或函数，它将加载并合并来自不同ProductID 架构的所有ProductINFO表，并使用包含其特定 ProductID 的所有信息(作为列)创建一个数据框。

例如，如果：具有 ProductIDshj637的架构A和具有ProductIDkly457和 80 的模式B！ 他们都有一个名为ProductINFO的表。我想生产：

ProductID		group	otherstuff
SHJ637	1	Q2	ghafdj...
SHJ637	2	Q4	日上自卫队
SHJ637	3	Q1	jhfdl...
kly457	1	Q2	jshfd...
kly457	2	Q4	uiepc...
kly457	3	Q1	qapfn..
.. 对于所有 80个产品 ID 架构

我认为您可以在 R 中相当直接地将查询与用户创建的数据库/模式名称向量一起粘贴。

从名为"quux"的表中手动选择"mt1"和"mt2"的两个数据库为：

dbGetQuery(mycon, "select * from mt1.quux")
#       row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1     Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2 Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3    Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
dbGetQuery(mycon, "select * from mt2.quux")
#           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 2 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 3           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1
dbGetQuery(mycon, "(select * from mt1.quux) union all (select * from mt2.quux)")
#           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
# 4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

若要以编程方式执行此操作，请使用

schemas <- c("mt1", "mt2")
qry <- paste(sprintf("(select * from %s.quux)", schemas), collapse = " union all ")
dbGetQuery(mycon, qry)
#           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
# 1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
# 2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
# 3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
# 4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
# 5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
# 6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

(PS：这旨在演示如何使用已知模式名称向量的一个连接中的schema.table方法。许多人认为select * ...的使用有点草率，为了简洁起见，我在这里使用它。

<小时 />

设置

我正在使用码头工人和mysql:8.从命令行(不是 R)：

$ docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8

在 R 中：

mycon <- DBI::dbConnect(odbc::odbc(), driver="MySQL ODBC 8.0 ANSI Driver",
host="127.0.0.1", port=3306, user="root", password="my-secret-pw")
# initially, no database
dbGetQuery(mycon, "select database()") # NA?
#   database()
# 1       <NA>
dbExecute(mycon, "create schema mt1")
dbExecute(mycon, "create schema mt2")
dbGetQuery(mycon, "select schema_name from information_schema.schemata")
#          SCHEMA_NAME
# 1              mysql
# 2 information_schema
# 3 performance_schema
# 4                sys
# 5                mt1
# 6                mt2
dbExecute(mycon, "use mt1")
# [1] 0
dbGetQuery(mycon, "select database()")
#   database()
# 1        mt1
dbWriteTable(mycon, "quux", mtcars[1:3,], create = TRUE)
dbExecute(mycon, "use mt2")
# [1] 0
dbGetQuery(mycon, "select database()")
#   database()
# 1        mt2
dbWriteTable(mycon, "quux", mtcars[4:6,], create = TRUE)

在SO中，我已经看到并学习了至少一种可行的方法来解决动态列或表甚至MySQL数据库的问题。该函数称为预准备语句。

以这个查询为例：

/*1*/
SET @sql := NULL;
/*2*/
SET @sql := (SELECT GROUP_CONCAT(
CONCAT('SELECT ID, ProductID, Name, ProductGroup FROM ',table_name) 
SEPARATOR ' UNION ALL ') FROM information_schema.tables
WHERE table_schema='my_database_name' 
/*[AND table_name ...  another condition to get only the tables you want]*/;
/*For checking purpose only. Not part of the prepared statement. Can be removed.*/
SELECT @sql;
/*3*/
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我将根据数字一一解释：

1. 第一步是确定一个变量。在这个例子中，我使用的是@sql，我将其设置为NULL以防万一之前使用过该变量并且仍然保留一些值。
2. 在这里，我们将使用稍后将用于执行的查询设置@sql变量。查询是根据CONCAT和GROUP_CONCAT数据库上的查询的组合创建的information_schema。这就是获取与最终查询连接的表名的方式。请注意，部件/*[AND table_name ..是您必须定义表标识的位置。像这样：

• 例如，如果这些表具有部分匹配的名称(如tablename_shj637, tablename_jsfh748, tablename_hasd548 ..)，则条件可能是：

AND table_name LIKE 'tablename%'

• 如果数据库 (table_schema) 包含包括productinfo表在内的所有必需表，则条件可能是：

AND table_name != 'productinfo'

最终生成的@sql如下所示：

SELECT ID, ProductID, Name, ProductGroup FROM hasd548 
UNION ALL 
SELECT ID, ProductID, Name, ProductGroup FROM jsfh748 
UNION ALL 
SELECT ID, ProductID, Name, ProductGroup FROM shj637

您可以通过在第二步后运行SELECT @sql来检查变量值@sql。这将有助于查看是否正确构造了查询。

3. 最后一部分是PREPARE, EXECUTE然后DEALLOCATE准备好的语句。

我在这里做了一个小提琴：https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=be294ef5fd46ed6e04f8b03d33f34ca2。但是，由于小提琴条件(temp db，temp tables)，SET @sql查询略有不同。