如何从不同的MySQL数据库表中提取创建语句？

Question

我想通过使用SHOW CREATE TABLE db.table、SHOW CREATE TABLE db1.mytable或SHOW CREATE TABLE db2.sometable或SHOW CREATE TABLE db3.mytable1来提取我50个MySQL数据库中所有Create Statements。因此，每个DB都有一些表，如db1(table, mytable...), db2(table1, sometable)等。

以下是一个查询示例，用于说明这些数据库：

SELECT *
FROM db.table1 m
   LEFT JOIN db1.sometable o ON m.id = o.id
   LEFT JOIN db2.sometables t ON p.id=t.id
   LEFT JOIN db3.sometable s ON s.column='john'


library(RMySQL)
library(DBI)

con <-  dbConnect(RMySQL::MySQL(), 
                    username = "", 
                    password = "",
                    host = "", 
                    port = 3306,
                    dbname= mydbname)#  when using dbs<-dbGetQuery(con ,"SHOW DATABASES") I have to ## dbname= mydbname## to get all DBs 

使用dbs<-dbGetQuery(con ,"SHOW DATABASES")，我可以将dbConnection中的所有50个数据库提取为字符向量。我想循环遍历dbs中的每个DB，并对每个行/数据库应用SHOW CREATE TABLE。我想我必须将每个行/数据库解析为dbname= mydbname和dbs<-dbGetQuery(con ,"SHOW CREATE TABLE")。但我就是无法想出如何制作循环。

我尝试过：

apply(dbs, 1, function(row) {
      dbname <- row[]
      for (i in 1:length(dbname)) {

        create<-dbGetQuery(con,"SHOW CREATE TABLE") }

    })

但这似乎不对。我想我必须以某种方式将con包含在循环中。否则，我会得到以下错误：Error in .local(drv, ...) : object 'dbname' not found 所以我尝试了：

apply(dbs, 1, function(row) {
      dbname <- row[]
      for (i in 1:length(dbname)) {
                    con <-  dbConnect(RMySQL::MySQL(), 
                    username = "", 
                    password = "",
                    host = "", 
                    port = 3306,
                    dbname= [i])
        create<-dbGetQuery(con,"SHOW CREATE TABLE") }})

我想这已经接近解决方案了，但是我还缺少一些东西：

dbs<-dbGetQuery(con,"show databases")

library(foreach)

foreach(i = 1:(length(dbs))%dopar%{
  query<-paste("SHOW CREATE TABLE",dbs[i])
  creates<-dbGetQuery(con,query)
})

- Googme

Answer 1

考虑采用导入每个数据库的数据帧的方法（不包括系统数据库INFORMATION_SCHEMA和MYSQL），以及它们对应的表。然后，运行SHOW CREATE TABLE语句。最后，将原始数据框与绑定的创建语句数据框合并。

现在，唯一需要注意的是重复命名的表。为了返回这种组合的不同值，使用aggregate()和head函数。

con <-  dbConnect(RMySQL::MySQL(),
                  username = "****", password = "****",
                  host = "****", port = 3306,
                  dbname= "****")
dbtbls <- dbGetQuery(con, "SELECT `TABLE_SCHEMA` AS `Database`, 
                                  `TABLE_NAME` AS `Table`
                            FROM  `INFORMATION_SCHEMA`.`TABLES` 
                            WHERE `TABLE_TYPE` = 'BASE TABLE'
                              AND `TABLE_SCHEMA` NOT LIKE '%SCHEMA%' 
                              AND `TABLE_SCHEMA` NOT LIKE '%MYSQL%' ")
# LIST OF SQL STATEMENTS
sql <- paste0("SHOW CREATE TABLE ", dbtbls$Database, ".", dbtbls$Table)

# LIST OF DATAFRAMES
createstmts <- lapply(sql, function(x) dbGetQuery(con, x))
dbDisconnect(con)

# ROW BIND LIST INTO ONE DATAFRAME TO MERGE WITH ORIGINAL    
stmtsdf <- do.call(rbind, createstmts)
finaldf <- merge(dbtbls, stmtsdf, by='Table')

# RETURN DISTINCT RECORDS
finaldf <- aggregate(.~Database+Table, finaldf, FUN=head, 1)

- Parfait

---

这比预期的还要好。非常感谢你！！ - Googme

@ Parfait。我能问你一个关于stmtsdf <- do.call(rbind, createstmts)的问题吗？我的creatstmts非常大，几乎有2MB，我会得到Error in rbind(deparse.level, ...) : numbers of columns of arguments do not match错误。有没有一般的方法来处理这个问题？ - Googme

我认为我的数据库中也有一些“视图”。我猜想这会以某种方式改变rbind的数据结构。 - Googme

太好了！很高兴能帮忙。确实，您是正确的，初始选择查询会拉取视图以及表格，并且 CREATE TABLE table 与 CREATE TABLE view 具有不同的列和名称，因此 do.call() 失败了。我添加了一个 WHERE 条件 TABLE_TYPE = 'BASE TABLE' 来仅拉取表格。 - Parfait

Answer 2

mysqldump --no-data

此功能完全符合您的需求。(可能还有其他需要避免/包含CREATE DATABASE等的参数.)

如果要随后将CREATEs拉入R中，那么我想知道这是一次性任务还是经常性任务。对于一次性任务，总体上建议使用mysqldump方法可能会更简单。

- Rick James

Answer 3

首先，您可以简单地使用

for (i in 1:length(dbs)) { } 

或者你可以研究一下apply函数，特别是sapply。在那里，你可以按dbConnection字符串进行解析，连接并将所有表作为列表或向量获取。然后，你可以在这些内部循环以获取创建表语句。 因此，基本上是apply内部的apply。 关于apply函数的良好解释，你可以查看http://www.r-bloggers.com/using-apply-sapply-lapply-in-r/。

- Zakkery

---

是的，看起来我必须使用嵌套循环。这就是为什么我发帖问问题的原因，因为我无法实现它。所以我假设第一个循环必须考虑长度。谢谢你的建议。 - Googme