title: Converting MySQL table with incorrectly encoded data to UTF-8
body: <p>I've got a big ol' MySQL 5.1 database, and for a variety of stupid reasons, I've been storing, I believe, UTF8 characters encoded as LATIN1 in a UTF8 table. It's... strange. And I'd like to fix it.</p>
  
  <p>The <a href="https://stackoverflow.com/questions/9407834/mysql-convert-latin1-characters-on-a-utf8-table-into-utf8">MySQL - Convert latin1 characters on a UTF8 table into UTF8</a> question seems to work -- a column at a time. But I have 24 tables and dozens of columns to convert. I'm really looking for a solution that'll convert at least a table at once.</p>
  
  <p>For reference, the single-column solution that works for me is:</p>
  
  <pre><code>UPDATE foo SET col1 = CONVERT(CAST(CONVERT(col1 USING latin1) AS binary) USING utf8);
  </code></pre>
  
  <p>For tables, I can do:</p>
  
  <pre><code>ALTER TABLE foo CONVERT TO CHARACTER SET latin1;
  ALTER TABLE foo CONVERT TO CHARACTER SET binary;
  ALTER TABLE foo CHARACTER SET utf8  COLLATE utf8_unicode_ci;
  </code></pre>
  
  <p>which gets me very close -- however, the <code>CONVERT TO CHARACTER SET binary</code> step turns all my VARCHAR columns into VARBINARY and my TEXT columns into BLOBs in one fell swoop. I can go through and change them back and all appears to be well... but then I'm back in the "let's modify all the columns individually" world -- in which case, I may just as well </p>
  
  <p>I've tried about 50 variations on those SQL statements, but I can't find one that both leaves my columns in character data types and encodes the data properly.</p>
  
  <p>Any suggestions?</p>
  
  <p><strong>Update:</strong> Deciding to just fix the columns rather than waiting for a database- or table-wise solution, I came up with:</p>
  
  <pre class="lang-ruby prettyprint-override"><code>#!/usr/bin/env ruby
  require 'rubygems'
  require 'mysql2'
  
  CONNECT_OPTS = {} # whatever you want
  Mysql2::Client.default_query_options.merge!(:as =&gt; :array)
  conn = Mysql2::Client.new(CONNECT_OPTS)
  
  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; I don't have them
  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
  </code></pre>
  
  <p>... which gets the job done. Amusingly, this runs on my database in 36 seconds, rather than the ALTER TABLE route which took 13 minutes (and had the VARBINARY problem) or the mysqldump solutions which would take upwards of twenty assuming I could get them to run.</p>
  
  <p>I'll still accept an answer if someone knows an elegant way to do this for a whole database or table in one step.</p>