如何基于MySQL查询数据在R语言中绘制图表？

Hey there! Let's expand your existing Shiny app to include visualizations using the data from your MySQL database. I'll walk you through a solid approach with examples using ggplot2 (for clean, customizable static charts) and plotly (for interactive ones) since they're the most common tools for this.

First, make sure you have all the necessary libraries installed and loaded. You'll need your existing Shiny/DB packages plus a plotting library:

# Install packages if you haven't already
install.packages(c("shiny", "shinydashboard", "RMySQL", "DBI", "ggplot2"))

# Load them into your R session
library(shiny)
library(shinydashboard)
library(DBI)
library(RMySQL)
library(ggplot2)

Your original code has a small issue: you disconnect from the database before launching the Shiny app. While it might work for a one-time read, using a reactive expression to fetch data inside the server is better practice—it handles the database connection lifecycle properly and makes it easier to update data if needed later.

Here's a revised full app that includes both the table and a chart:

ui <- dashboardPage(
  dashboardHeader(title = "MySQL Data Dashboard"),
  dashboardSidebar(),
  dashboardBody(
    # Split the body into two columns for table and chart
    fluidRow(
      box(width = 6, tableOutput('table')),
      box(width = 6, plotOutput('chart'))
    )
  )
)

server <- function(input, output) {
  # Reactive expression to fetch data from MySQL
  # This runs once when the app starts, and serves data to outputs
  mysql_data <- reactive({
    # Connect to the database
    conn <- dbConnect(
      drv = RMySQL::MySQL(),
      dbname = "shinytest",
      host = "localhost",
      username = "root",
      password = "root"
    )
    
    # Fetch your data
    rs <- dbGetQuery(conn, "Select * from test;")
    
    # Disconnect immediately after fetching to avoid hanging connections
    dbDisconnect(conn)
    
    # Return the fetched data
    rs
  })
  
  # Render the table (same as your original, now using reactive data)
  output$table <- renderTable({
    mysql_data()
  })
  
  # Render a sample bar chart (customize this to match your data!)
  output$chart <- renderPlot({
    data <- mysql_data()
    
    # Example: Bar chart assuming your table has 'category' and 'value' columns
    ggplot(data, aes(x = category, y = value)) +
      geom_bar(stat = "identity", fill = "#2E86AB") +
      labs(title = "Values by Category", x = "Category", y = "Value") +
      theme_minimal()
  })
}

shinyApp(ui, server)

The ggplot2 code above is a sample—you'll need to tweak it based on your actual table structure:

• If you have time-series data: Use geom_line() instead of geom_bar(), and map your date column to x.
• For histograms: Use geom_histogram(aes(x = your_numeric_column), bins = 10) to visualize distribution.
• For scatter plots: Use geom_point(aes(x = column1, y = column2)) to show relationships between variables.
• Add colors, facets, or themes to make the chart fit your needs—ggplot2 is super flexible!

If you want users to hover over data points, zoom, or pan, switch to plotly for interactive visuals. Here's how to adjust the chart part:

# First install and load plotly
install.packages("plotly")
library(plotly)

# Replace the renderPlot block in the server with this:
output$chart <- renderPlotly({
  data <- mysql_data()
  
  # Interactive bar chart example
  plot_ly(data, x = ~category, y = ~value, type = 'bar', marker = list(color = '#2E86AB')) %>%
    layout(title = "Values by Category", xaxis = list(title = "Category"), yaxis = list(title = "Value"))
})

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