Convert latin1 MySQL database to utf-8

mysql-db-convert.rb

require 'mysql2'

database_name = ENV.fetch('DATABASE') { "voicepublisher_development2" }

CLIENT = Mysql2::Client.new(host: "127.0.0.1", username: "user", password: "pass", database: database_name, encoding: "utf8")

def column_definition(schema, table, column)
  sql = <<SQL
 SELECT CONCAT(
      CAST(COLUMN_NAME AS CHAR),
      ' ',
      CAST(COLUMN_TYPE AS CHAR),
      IF(ISNULL(CHARACTER_SET_NAME),
         '',
         CONCAT(' CHARACTER SET ', 'utf8')),
      IF(ISNULL(COLLATION_NAME), '', CONCAT(' COLLATE ', 'utf8_unicode_ci')),
      ' ',
      IF(IS_NULLABLE = 'NO', 'NOT NULL ', ''),
      IF(IS_NULLABLE = 'NO' AND ISNULL(COLUMN_DEFAULT),
         '',
         CONCAT('DEFAULT ', QUOTE(COLUMN_DEFAULT), ' ')),
      UPPER(extra))
      AS column_definition
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = '#{schema}'
   AND TABLE_NAME = '#{table}'
   AND COLUMN_NAME = '#{column}';
SQL
  CLIENT.query(sql).first["column_definition"]
end


result = CLIENT.query("SELECT column_name, table_name FROM INFORMATION_SCHEMA.`COLUMNS` where table_schema='#{database_name}' and character_set_name='latin1';")
result.group_by{|r| r["table_name"]}.each do |table_name, group|
  puts "# TABLE: #{table_name}"
  alter_table = ["alter table #{table_name} CHARACTER SET utf8 COLLATE utf8_unicode_ci"]
  group.each do |row|
    column_name = row["column_name"]
    alter_table << "CHANGE #{column_name} #{column_definition(database_name, table_name, column_name)}"
    puts "update #{table_name} set #{column_name} = convert(binary convert(#{column_name} using latin1) using utf8);"
  end
  puts alter_table.join(",").concat(";")
end