Enable full Unicode support in a MySQL database.

mysql-unicode.rb

require 'rubygems'
require 'mysql'

@database = {
  'host' => '',
  'user' => '',
  'password' => '',
  'name' => ''
}

def sql
  # start your engines
  @@mysql = Mysql.new(@database['host'], @database['user'], @database['password'], @database['name'])
end

def database_is_unicode_ready?
  character_set = sql.query("show variables like 'character_set_database';").fetch_hash
  collation = sql.query("show variables like 'collation_database';").fetch_hash
  (character_set['Value'].eql?('utf8mb4') && collation['Value'].eql?('utf8mb4_unicode_ci')) ? true : false
end

def table_is_unicode_ready?(table)
  collation = sql.query("show table status where Name = '#{table}';").fetch_hash
  collation['Collation'].eql?('utf8mb4_unicode_ci') ? true : false
end

def transaction(&block)
  return unless block_given?
  begin
    sql.query("BEGIN;")
    yield
    sql.query("COMMIT;")
  rescue
    sql.query("ROLLBACK;")
  end
end

# update database
if database_is_unicode_ready?
  puts "Database #{@database['name']} already supports full Unicode."
else
  sql.query("ALTER DATABASE #{@database['name']} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci")
  puts "Database #{@database['name']} upgraded to support full Unicode."
end

# update tables
tables = sql.list_tables

tables.each do |table|
  if table_is_unicode_ready?(table)
    puts "Table #{table} already supports full Unicode."
  else
    mysql.query("ALTER TABLE `#{table}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
    puts "Table #{table} upgraded to support full Unicode."
  end
end

# update columns
tables.each do |table|
  columns = sql.query("SHOW FULL COLUMNS FROM `#{table}` WHERE COLLATION LIKE '%utf8%';")
  columns.each_hash do |column|
    if column['Collation'].eql?('utf8mb4_unicode_ci')
      puts "Column #{table}.#{column['Field']} already supports full Unicode."
    else
      transaction do
        sql.query("SET FOREIGN_KEY_CHECKS = 0;")
        sql.query("ALTER TABLE `#{table}` CHANGE COLUMN `#{column['Field']}` `#{column['Field']}` #{column['Type']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT #{column['Default'] || 'NULL'};")
        sql.query("SET FOREIGN_KEY_CHECKS = 1;")
      end
      puts "Column #{table}.#{column['Field']} upgraded to support full Unicode."
    end
  end
end

# shut down
sql.close