如何从R脚本创建新MySQL数据库并存储抽样结果表？

需求说明 #

已有SQLRowRandomSampling函数可从指定MySQL数据库的表中随机抽样并返回结果表，但项目要求不能修改原数据库，需通过R脚本创建新的MySQL数据库来存储抽样结果。已知SQLite数据库创建方法，但需要适配MySQL实现。

实现步骤 #

1. 核心原理 #

MySQL创建数据库需先连接到MySQL服务器（而非具体数据库），执行CREATE DATABASE语句完成创建，再连接到新库写入数据。需确保使用的MySQL账号拥有创建数据库的权限。

2. 修改后的完整实现代码 #

整合抽样、创建新库、写入结果的逻辑：

library(RMariaDB)
library(dplyr)

SQLRowRandomSamplingWithNewDB <- function(username, passwords, sourceDBName, sourceTableName,
                                          SamplingSize, targetDBName = "Sampling_Database"){   
  # 1. 连接到源数据库进行抽样
  sourceConn <- dbConnect(MariaDB(), user=username,
                           password=passwords, dbname=sourceDBName, 
                           host='localhost')
  
  # 检查源表是否存在
  tryCatch({
    dbframe <- dbReadTable(sourceConn, sourceTableName)
  }, error = function(e){
    dbDisconnect(sourceConn)
    stop('源表不存在，请重新输入！') 
  })
  
  # 验证抽样大小合法性
  actualRowNumber <- nrow(dbframe)  
  if(SamplingSize < 1){
    dbDisconnect(sourceConn)
    stop("抽样大小必须是正整数")
  } else if (SamplingSize >= actualRowNumber){
    dbDisconnect(sourceConn)
    stop("抽样大小必须小于源表的总行数")
  } 
  
  # 执行随机抽样
  RandomRowSmpling <- sample_n(dbframe, SamplingSize)  
  dbDisconnect(sourceConn) # 断开源数据库连接
  
  # 2. 连接到MySQL服务器（不指定数据库）创建新库
  serverConn <- dbConnect(MariaDB(), user=username,
                          password=passwords, host='localhost')
  
  # 创建新数据库（如果不存在则创建）
  dbExecute(serverConn, paste0("CREATE DATABASE IF NOT EXISTS ", targetDBName))
  dbDisconnect(serverConn)
  
  # 3. 连接到新创建的数据库，写入抽样结果
  targetConn <- dbConnect(MariaDB(), user=username,
                          password=passwords, dbname=targetDBName, 
                          host='localhost')
  
  # 将抽样结果写入新表（存在则覆盖，可按需修改参数）
  dbWriteTable(conn = targetConn, name = "RandomRowSampling",
               value = RandomRowSmpling, overwrite = TRUE)
  
  # 验证写入结果（可选）
  cat("抽样结果已成功写入数据库", targetDBName, "中的RandomRowSampling表\n")
  sampleCheck <- dbReadTable(targetConn, "RandomRowSampling")
  cat("写入的表行数：", nrow(sampleCheck), "\n")
  
  dbDisconnect(targetConn)
  
  # 返回抽样结果
  return(RandomRowSmpling)
}

3. 关键说明 #

• 权限要求：使用的MySQL账号需要有CREATE DATABASE权限，若无权限需联系数据库管理员授权。
• 连接逻辑：分三次连接：源数据库（抽样）→ MySQL服务器（创建新库）→ 新数据库（写入数据），操作完成后及时断开连接避免资源占用。
• 参数可配置：targetDBName参数默认值为Sampling_Database，可根据需求自定义新数据库名称。
• 写入控制：dbWriteTable的overwrite = TRUE表示目标表已存在时会覆盖，若需追加数据可改为append = TRUE并设置overwrite = FALSE。

使用示例 #

# 替换为实际的账号、密码、源库和表名
result <- SQLRowRandomSamplingWithNewDB(
  username = "your_mysql_username",
  passwords = "your_mysql_password",
  sourceDBName = "your_source_database",
  sourceTableName = "your_source_table",
  SamplingSize = 100,
  targetDBName = "My_Sampling_Results"
)

内容的提问来源于stack exchange，提问作者majed