MySQL utf8mb4迁移引发数据库架构不一致问题问询

Let's break down how to resolve the character set migration issues you're facing. First, a quick recap: MySQL's utf8 is actually utf8mb3 (max 3 bytes), which can't store 4-byte Unicode characters like Emojis—so switching to utf8mb4 is the right call. The schema inconsistency post-migration usually stems from misconfigured Rails settings, incomplete migration scripts, or issues with how rake db:schema:dump generates the schema file. Here's a step-by-step fix:

1. Verify Database-Level Character Sets First #

Before touching Rails, confirm your MySQL database, tables, and columns are actually using utf8mb4. Run these commands in your MySQL console:

• Check database settings: SHOW CREATE DATABASE your_database_name;
• Check a specific table: SHOW CREATE TABLE your_table_name;

You should see CHARACTER SET utf8mb4 and COLLATE utf8mb4_unicode_ci (or a compatible collation) for both the database and tables. If not, we'll fix that with a migration.

2. Update Rails Database Configuration #

Open config/database.yml and add encoding: utf8mb4 (and optionally collation: utf8mb4_unicode_ci) to each environment block. Example:

production:
  adapter: mysql2
  database: your_app_prod
  username: db_user
  password: your_password
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci

Also, make sure you're using a recent version of the mysql2 gem (>= 0.4.4) in your Gemfile—older versions have poor support for utf8mb4.

3. Create a Comprehensive Migration to Convert All Objects #

Instead of relying on partial migrations, create a dedicated migration to convert every database object to utf8mb4:

class ConvertDatabaseToUtf8mb4 < ActiveRecord::Migration[5.1] # Match your Rails version
  def up
    # Update database-level character set
    execute "ALTER DATABASE #{connection.current_database} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

    # Convert all tables and their columns
    ActiveRecord::Base.connection.tables.each do |table|
      # Convert table-level character set
      execute "ALTER TABLE #{table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

      # Ensure individual string/text columns are updated (some tables might need explicit changes)
      ActiveRecord::Base.connection.columns(table).each do |column|
        if [:string, :text].include?(column.type)
          # Preserve the original column type (e.g., varchar(255), text)
          execute "ALTER TABLE #{table} MODIFY #{column.name} #{column.sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        end
      end
    end
  end

  def down
    # Optional: Add rollback logic if you need to revert to utf8
    execute "ALTER DATABASE #{connection.current_database} CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
    ActiveRecord::Base.connection.tables.each do |table|
      execute "ALTER TABLE #{table} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
      ActiveRecord::Base.connection.columns(table).each do |column|
        if [:string, :text].include?(column.type)
          execute "ALTER TABLE #{table} MODIFY #{column.name} #{column.sql_type} CHARACTER SET utf8 COLLATE utf8_unicode_ci;"
        end
      end
    end
  end
end

Run the migration with rake db:migrate.

4. Regenerate a Correct schema.rb #

After the migration completes, regenerate your schema file to reflect the new character sets:

rake db:schema:dump

Open db/schema.rb—you should now see encoding: "utf8mb4" on string/text columns, like this:

create_table "posts", force: :cascade do |t|
  t.string "title", encoding: "utf8mb4"
  t.text "content", encoding: "utf8mb4"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

5. Validate the Migration Works #

Test with Emoji characters to confirm everything works:

1. Open your Rails console: rails c
2. Create a record with an Emoji: Post.create(title: "Hello 😎", content: "Testing Emoji Support 🚀")
3. Fetch the record back: post = Post.last
4. Check that the Emojis display correctly (no question marks or garbled text).

6. Fix Common Pitfalls #

• InnoDB Prefix Limitation: If you get errors about index length limits, ensure MySQL has innodb_large_prefix=ON, innodb_file_format=Barracuda, and innodb_file_per_table=ON enabled. These settings allow longer index prefixes needed for utf8mb4 (since each character takes 4 bytes). Restart MySQL after updating my.cnf/my.ini.
• Consistent Connections: Make sure all tools connecting to your database (backup scripts, reporting tools) use utf8mb4 encoding to avoid data corruption during transfers.

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