Convert all Rails table column collation and character set

character_set_and_collation.rb

#!/usr/bin/env ruby

# Put this file in the root of your Rails project,
# then run it to output the SQL needed to change all
# your tables and columns to the same character set 
# and collation.
#
# > ruby character_set_and_collation.rb

DATABASE      = ''
CHARACTER_SET = 'utf8'
COLLATION     = 'utf8_general_ci'

schema = File.open('db/schema.rb', 'r').read
rows = schema.split("\n")

table_name = nil
rows.each do |row|
  if row =~ /create_table/
    table_name = row.match(/create_table "(.+)"/)[1]
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` DEFAULT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
  elsif row =~ /t\.string/
    field_name = row.match(/"(.+)"/)[1]
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
  elsif row =~ /t\.text/
    field_name = row.match(/"(.+)"/)[1]
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
  end
end

# rails generate migration change_to_utf8mb_encoding

class ChangeToUtf8mbEncoding < ActiveRecord::Migration
  # def execute(text)
  #   puts text
  # end

  def db
    ActiveRecord::Base.connection
  end

  def up
    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4;"
    db.tables.each do |table|
      execute "ALTER TABLE `#{table}` CHARACTER SET = utf8mb4;"
      db.columns(table).each do |column|
        case column.sql_type
        when "text"
          execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        when /varchar\(([0-9]+)\)/i
          # InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4
          # columns, you can index a maximum of 255 or 191 characters, respectively.
          # If you currently have utf8 columns with indexes longer than 191 characters,
          # you will need to index a smaller number of characters.
          indexed_column = db.indexes(table).any? { |index| index.columns.include?(column.name) }
          sql_type = (indexed_column && $1.to_i > 191) ? "VARCHAR(191)" : column.sql_type.upcase
          execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        end
      end
    end
  end
end

small update to keep default values and null: false/true

# rails generate migration change_to_utf8mb_encoding

class ChangeToUtf8mbEncoding < ActiveRecord::Migration
  def db
    ActiveRecord::Base.connection
  end

  def up
    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4;"
    db.tables.each do |table|
      execute "ALTER TABLE `#{table}` CHARACTER SET = utf8mb4;"
      db.columns(table).each do |column|
        case column.sql_type
        when "text"
          execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        when /varchar\(([0-9]+)\)/i
          # InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4
          # columns, you can index a maximum of 255 or 191 characters, respectively.
          # If you currently have utf8 columns with indexes longer than 191 characters,
          # you will need to index a smaller number of characters.
          indexed_column = db.indexes(table).any? { |index| index.columns.include?(column.name) }
          sql_type  = (indexed_column && $1.to_i > 191) ? 'VARCHAR(191)' : column.sql_type.upcase
          default   = (column.default.blank?) ? '' : "DEFAULT '#{column.default}'"
          null      = (column.null) ? '' : 'NOT NULL'
          execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
        end
      end
    end
  end
end

Convert not only TEXT fields but all *TEXT fields (longtext, mediumtext, etc)
Also cut length of all VARCHAR to 191, not only indexed (which may not detected correctly).

class MigrateToUtf8mb4 < ActiveRecord::Migration[5.0]

  def db
    ActiveRecord::Base.connection
  end

  def up
    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4;"
    db.tables.each do |table|
      execute "ALTER TABLE `#{table}` CHARACTER SET = utf8mb4;"
      db.columns(table).each do |column|
        case column.sql_type
          when  /([a-z]*)text/i
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{$1.upcase}TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
          when /varchar\(([0-9]+)\)/i
            # InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4
            # columns, you can index a maximum of 255 or 191 characters, respectively.
            # If you currently have utf8 columns with indexes longer than 191 characters,
            # you will need to index a smaller number of characters.
            indexed_column = db.indexes(table).any? { |index| index.columns.include?(column.name) }

            sql_type  = ($1.to_i > 191) ? 'VARCHAR(191)' : column.sql_type.upcase
            default   = (column.default.blank?) ? '' : "DEFAULT '#{column.default}'"
            null      = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
        end
      end
    end
  end

end

Few fixes and warnings:

1. In the last version you dropped the indexed_column. You should not cut VARCHAR o/w (Other option is changing the index itself)
2. This code should not be run on production server w/ large tables, as it may copy large tables few times (for each ALTER) that may result in a long downtime of you system(if your table are really large you should use pt-online-schema-change for MySQL case)

class MigrateToUtf8mb4 < ActiveRecord::Migration[5.0]

  def db
    ActiveRecord::Base.connection
  end

  def up
    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4;"
    db.tables.each do |table|
      execute "ALTER TABLE `#{table}` CHARACTER SET = utf8mb4;"
      db.columns(table).each do |column|
        case column.sql_type
          when  /([a-z]*)text/i
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{$1.upcase}TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
          when /varchar\(([0-9]+)\)/i
            # InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4
            # columns, you can index a maximum of 255 or 191 characters, respectively.
            # If you currently have utf8 columns with indexes longer than 191 characters,
            # you will need to index a smaller number of characters.
            indexed_column = db.indexes(table).any? { |index| index.columns.include?(column.name) }

            sql_type  = (indexed_column && $1.to_i > 191) ? 'VARCHAR(191)' : column.sql_type.upcase
            default   = (column.default.blank?) ? '' : "DEFAULT '#{column.default}'"
            null      = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
        end
      end
    end
  end
end

class MigrateToUtf8mb4 < ActiveRecord::Migration[5.0]

  def db
    ActiveRecord::Base.connection
  end

  def up
    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4;"
    db.tables.each do |table|
      next if %w(ar_internal_metadata schema_migrations).include?(table)
      execute "ALTER TABLE `#{table}` CHARACTER SET = utf8mb4;"
      db.columns(table).each do |column|
        case column.sql_type
          when  /([a-z]*)text/i
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{$1.upcase}TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
          when /varchar\(([0-9]+)\)/i
            # InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4
            # columns, you can index a maximum of 255 or 191 characters, respectively.
            # If you currently have utf8 columns with indexes longer than 191 characters,
            # you will need to index a smaller number of characters.
            indexed_column = db.indexes(table).any? { |index| index.columns.include?(column.name) }

            sql_type  = (indexed_column && $1.to_i > 191) ? 'VARCHAR(191)' : column.sql_type.upcase
            default   = (column.default.blank?) ? '' : "DEFAULT '#{column.default}'"
            null      = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
        end
      end
    end
  end
end

I added the line next if %w(ar_internal_metadata schema_migrations).include?(table) as there is no need to change Rails' tables. Also, I was having issues with index sizes.

Unfortunately, the loop over db.tables includes a mysql view of ours, which doesn't allow changing its character set:

Mysql2::Error: 'wca_development.rails_persons' is not BASE TABLE: ALTER TABLE `rails_persons` CHARACTER SET = utf8mb4;

Apparently the .tables method will change in Rails 5.1 to not include views:

irb(main):001:0> ActiveRecord::Base.connection.tables.include?("rails_persons")
DEPRECATION WARNING: #tables currently returns both tables and views. This behavior is deprecated and will be changed with Rails 5.1 to only return tables. Use #data_sources instead. (called from irb_binding at (irb):1)

But until then, I've had to add another "get me out of here" escape hatch like @jules2689 (I also ran into index size issues with the internal Rails tables): next if db.views.include?(table).

Other problems:

• The migration ignored char columns, so I've changed it to support those in addition to varchar.
• The migration ignored default empty string values (it was checking .blank? so I've changed it to check .nil?)

class ConvertUtf8ToUtf8mb4 < ActiveRecord::Migration[5.0]
  def db
    ActiveRecord::Base.connection
  end

  def up 
    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4;"
    db.tables.each do |table|
      next if %w(ar_internal_metadata schema_migrations).include?(table)
      next if db.views.include?(table) # Skip views. This will not be necessary in Rails 5.1, when `db.tables` will change to only return actual tables.
      execute "ALTER TABLE `#{table}` CHARACTER SET = utf8mb4;"
      db.columns(table).each do |column|
        case column.sql_type 
          when /([a-z]*)text/i
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{$1.upcase}TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
          when /((?:var)?char)\(([0-9]+)\)/i
            # InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4
            # columns, you can index a maximum of 255 or 191 characters, respectively.
            # If you currently have utf8 columns with indexes longer than 191 characters,
            # you will need to index a smaller number of characters.
            indexed_column = db.indexes(table).any? { |index| index.columns.include?(column.name) }

            sql_type = (indexed_column && $2.to_i > 191) ? "#{$1}(191)" : column.sql_type.upcase
            default = (column.default.nil?) ? '' : "DEFAULT '#{column.default}'"
            null = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` CHANGE `#{column.name}` `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
        end
      end
    end
end

Thanks a lot for this, saved me some coding :)

If you are on MySQL 5.7, you probably don't need to shorten columns to 191 chars. Maximum index size is not limit of InnoDB, but row_format used on specific table.

SELECT @@innodb_default_row_format;

This should return dynamic. dynamic and compressed support indexes up to 3072 bytes, if innodb_large_prefix is on (1). That is default in 5.7.

SELECT @@innodb_large_prefix;

You can see row_format for every table with:

SHOW TABLE STATUS FROM <your-database-name>;

If you still have some tables with compact row format, you can change it with:

ALTER TABLE <table-name> ROW_FORMAT=DYNAMIC;

@Simplify is right about column limits. I was worried about data truncation until I read this: https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html that explains that columns can store more than their limit when the row format is dynamic.

My final migration file was:

def change

    db = ActiveRecord::Base.connection

    execute "ALTER DATABASE `#{db.current_database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    db.tables.each do |table|
      execute "ALTER TABLE `#{table}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

      db.columns(table).each do |column|
        case column.sql_type
          when /([a-z]*)text/i
            default = (column.default.nil?) ? '' : "DEFAULT '#{column.default}'"
            null = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` MODIFY `#{column.name}` #{column.sql_type.upcase} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
          when /varchar\(([0-9]+)\)/i
            sql_type = column.sql_type.upcase
            default = (column.default.nil?) ? '' : "DEFAULT '#{column.default}'"
            null = (column.null) ? '' : 'NOT NULL'
            execute "ALTER TABLE `#{table}` MODIFY `#{column.name}` #{sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci #{default} #{null};"
        end
      end
    end

  end

That was run on a database with dynamic row formats and on MySQL 5.7.

Oh! and a trap for new players. Some of my tables were still MyISAM for some reason! This blew up the migrations around key length of course. Converting them to InnoDB did the trick.

Just adding on, the default COLLATE for MySQL 8.0 is now utf8mb4_0900_ai_c. Scripts may need to be updated to support this.

See http://mysqlserverteam.com/new-collations-in-mysql-8-0-0/ for more information.