title: MySQL Character Encodings
body: <p>I have a large database that was originally created with latin1 encoding and latin1_swedish_ci collations.</p>
  
  <p>I've made a lot of changes and now I am using Doctrine2, which uses <code>SET NAMES UTF8</code> before queries.  I was using the Yii framework previously which was also setup to use UTF-8.  Basically I overlooked all of this at the start but from what I understand, I've been writing UTF-8 data to the database, although it's all supposed to be latin1.</p>
  
  <p>I'd like to convert my database to utf-8 but have no idea how to do it safely and ensure I'm not losing data.</p>
  
  <p><strong>Two questions:</strong></p>
  
  <ol>
  <li><p>Is there a way to ensure that I have done this correctly? I have like 2 GB of data so I can't just scan through it to test, but are there certain characters that I can look for to determine if the database is using UTF-8 already?</p></li>
  <li><p>What is the proper way to convert everything?  I've seen some people say I have to mysqldump and re-import (with certain flags, <a href="http://blog.makezine.com/2007/05/08/mysql-database-migration-latin/" rel="nofollow">http://blog.makezine.com/2007/05/08/mysql-database-migration-latin/</a> and <a href="http://docs.moodle.org/22/en/Converting_your_MySQL_database_to_UTF8" rel="nofollow">http://docs.moodle.org/22/en/Converting_your_MySQL_database_to_UTF8</a>). Others say you can <code>ALTER TABLE...</code> per column (http://www.bothernomore.com/2008/12/16/character-encoding-hell/). And I've seen some command like <code>SET utf8</code> as part of an <code>ALTER TABLE</code> I think, but I don't know if that works.</p></li>
  </ol>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I've dumped the data and have found several accented characters.  Would this indicate that the data itself is UTF-8? If so, I htink the instructions here apply since I can just "convert" to blob and back safely <a href="http://codex.wordpress.org/Converting_Database_Character_Sets" rel="nofollow">http://codex.wordpress.org/Converting_Database_Character_Sets</a></p>
  
  <p><strong>EDIT AGAIN:</strong></p>
  
  <p>After doing what I read in the previous link, I found that comparing the data, I was missing all characters after the first non-ascii(?) one.  So I had titles set with curly single quotes and the new database had that character and all characters after it missing. As an example, here is the code I was running:</p>
  
  <pre><code>ALTER TABLE articles CHANGE title title VARBINARY(255) NOT NULL;
  ALTER TABLE articles CHANGE title title VARCHAR(255) CHARACTER SET utf8 NOT NULL;
  </code></pre>
  
  <p>Somehow that caused me to lose data.</p>
  
  <p>But if I dump, change the charset for each table from latin1 to utf8, it works.  I'd prefer to just modify things rather than dump and re-create but I'll resort to it if no one has any other suggestions or ideas based on this.</p>