Rails 5新手求助：如何为新域名动态创建MySQL表？

Hey there! As a fellow Rails developer who’s tackled similar dynamic schema needs before, let’s walk through how to pull this off—and also talk about some important tradeoffs to keep in mind.

First: Track Your Domains with a Model #

Before we start creating tables on the fly, we need a way to keep track of which domains already exist (so we don’t duplicate tables). Let’s generate a Domain model first:

rails generate model Domain name:string last_scraped_at:datetime
rails db:migrate

This will store each unique domain and let us trigger table creation when a new domain is added.

Step 1: Add a Callback to Create Tables on Domain Creation #

We’ll use an after_create callback in the Domain model to run the table creation logic right when a new domain is saved. Here’s how to write that:

# app/models/domain.rb
class Domain < ApplicationRecord
  after_create :create_change_log_table

  private

  def create_change_log_table
    # Sanitize the domain name to make a valid table name (replace dots with underscores, lowercase)
    table_name = "#{name.downcase.gsub('.', '_')}_change_logs"

    # Skip if table already exists to avoid errors
    return if ActiveRecord::Base.connection.table_exists?(table_name)

    # Use raw SQL to create the table—adjust columns to match your needs!
    ActiveRecord::Base.connection.execute(<<~SQL)
      CREATE TABLE #{table_name} (
        id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
        scraped_data TEXT NOT NULL,
        price DECIMAL(10,2), # Add fields you need for comparison (like price, title)
        scraped_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      );
    SQL
  end
end

• We sanitize the domain name to avoid invalid table names (e.g., example.com becomes example_com_change_logs).
• We check if the table already exists to prevent duplicate creation errors.
• The SQL creates a table with core fields for tracking changes—tweak columns like price or add new ones (e.g., product_title) to match your actual scraping needs.

Step 2: Access Dynamic Tables with a Flexible Model #

To interact with these dynamic tables (save scraped data, query price changes), you can create an abstract base model that adapts to each domain’s table. Here’s a clean approach:

# app/models/change_log.rb
class ChangeLog < ApplicationRecord
  self.abstract_class = true # Mark as abstract so Rails doesn't look for a `change_logs` table

  # Class method to get a model instance tied to a specific domain
  def self.for_domain(domain)
    table_name = "#{domain.name.downcase.gsub('.', '_')}_change_logs"
    # Create an anonymous subclass with the correct table name
    Class.new(self) do
      self.table_name = table_name
    end
  end
end

Now you can use it in your scraper code like this:

# Fetch the domain (or create it if new)
domain = Domain.find_or_create_by!(name: "example.com")
# Get the model for this domain's change log table
change_log_model = ChangeLog.for_domain(domain)

# Save new scraped data
change_log_model.create!(
  scraped_data: "<html>...</html>",
  price: 29.99
)

# Query all historical changes for the domain
all_changes = change_log_model.order(scraped_at: :desc)

Important Tradeoffs to Keep in Mind #

• Schema Maintenance Headaches: Dynamically created tables won’t show up in schema.rb, so if you need to add/remove columns later, you’ll have to write code to update all existing tables (or run manual SQL). This gets messy fast with many domains.
• Database Performance: Having hundreds or thousands of tables can slow down your database (PostgreSQL/MySQL have overhead per table). For large-scale scraping, this might become a bottleneck.
• Rails-Idiomatic Alternative: Instead of separate tables, consider a single change_logs table with a domain_id foreign key. This is simpler to maintain and works for most use cases:

  rails generate model ChangeLog domain:references scraped_data:text price:decimal scraped_at:datetime
  

  You can then query changes for a domain with domain.change_logs—no dynamic tables required!

Test It Out #

Fire up your Rails console and test creating a new domain:

Domain.create!(name: "example.com")
# Check your database—you should see a `example_com_change_logs` table!

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