convert_db_to_utf8.rb

#!/usr/bin/env ruby
require 'rubygems'
require 'mysql2'

Mysql2::Client.default_query_options.merge!(:as => :array)
conn = Mysql2::Client.new(:host => 'localhost', :username => 'user', 
  :password => 'pw', :database => 'db')

tables = conn.query("SHOW TABLES").map {|row| row[0] }

# See http://dev.mysql.com/doc/refman/5.0/en/charset-column.html
# One might want to include enum and set columns, but I don't
TYPES_TO_CONVERT = %w(char varchar text)
tables.each do |table|
  puts "converting #{table}"
  # Get all the columns and we'll filter for the ones we want
  columns = conn.query("DESCRIBE #{table}")
  columns_to_convert = columns.find_all {|row|
    TYPES_TO_CONVERT.include? row[1].gsub(/\(\d+\)/, '')
  }.map {|row| row[0]}
  next if columns_to_convert.empty?

  query = "UPDATE `#{table}` SET "
  query += columns_to_convert.map {|col|
    "`#{col}` = convert(cast(convert(`#{col}` using latin1) as binary) using utf8)"
  }.join ", "
  puts query
  conn.query query
end

Awesome, you saved my day! Published a PHP port of your script.

Fantastic. One recommendation I would make is to use utf8mb4 instead of utf8. MySQL's utf8 character set does not actually fully implement utf8, their fix in 5.5.3+ is utf8mb4: https://mathiasbynens.be/notes/mysql-utf8mb4

Awesome, you saved my day! Published a PHP port of your script.

I adjusted your script with some additions:

• also converts the database AND all tables to utf8mb4 collation!

https://gist.github.com/pobegov/f8b293fb6eb658a13feb1c318e6c07ed