如何在shinyapps中运行RMySQL (在本地运行良好)

library(RMySQL)

# 连接数据库
con <- dbConnect(MySQL(),
                 host = "your_database_host",
                 port = your_database_port,
                 user = "your_username",
                 password = "your_password",
                 dbname = "your_database_name")

# 测试连接
dbListTables(con)

GRANT ALL PRIVILEGES ON your_database_name.* TO 'your_username'@'%';
FLUSH PRIVILEGES;

# 使用分页查询
query <- paste0("SELECT * FROM your_table LIMIT ", offset, ", ", limit)
result <- dbGetQuery(con, query)

# 使用环境变量
host <- Sys.getenv("DB_HOST")
port <- Sys.getenv("DB_PORT")
user <- Sys.getenv("DB_USER")
password <- Sys.getenv("DB_PASSWORD")
dbname <- Sys.getenv("DB_NAME")

con <- dbConnect(MySQL(),
                 host = host,
                 port = port,
                 user = user,
                 password = password,
                 dbname = dbname)

library(shiny)
library(RMySQL)

ui <- fluidPage(
  titlePanel("MySQL Data Viewer"),
  sidebarLayout(
    sidebarPanel(
      textInput("query", "Enter SQL Query:")
    ),
    mainPanel(
      tableOutput("result")
    )
  )
)

server <- function(input, output) {
  con <- dbConnect(MySQL(),
                   host = Sys.getenv("DB_HOST"),
                   port = Sys.getenv("DB_PORT"),
                   user = Sys.getenv("DB_USER"),
                   password = Sys.getenv("DB_PASSWORD"),
                   dbname = Sys.getenv("DB_NAME"))

  output$result <- renderTable({
    result <- dbGetQuery(con, input$query)
    result
  })

  on.exit(dbDisconnect(con))
}

shinyApp(ui = ui, server = server)