title: Where did I go wrong with this unicode field in MySQL?
body: <p>I have a table with a field which contains strings in my MySQL database.  </p>
  
  <p>The MySQL version is 5.0.51a.  The default character set for the table is 'utf8'.</p>
  
  <p>Many of the strings have unicode characters such as \xae and \u21222 (registered symbol and trademark symbol respectively).  </p>
  
  <p>For example, suppose I have a row with a field this value:</p>
  
  <pre><code>"Bing® Blang™ Blaow"
  </code></pre>
  
  <p>The default character set of my mysql command line client is "latin1".</p>
  
  <p>If I issue a SELECT statement in the mysql client program from the command line without specifying a character set, the output of the title shows up like so:</p>
  
  <pre><code>"Bing® Blang Blaow"
  </code></pre>
  
  <p>The (R) symbol is correct but the (TM) symbol is missing.  If I cut and paste this string from the console into TextMate, the (TM) symbol appears, but is half-way behind the g in the word "Blang".</p>
  
  <p>I am assuming that the half-way-behind-the-g thing is a just a display error in TextMate (though if anyone can provide further detail that'd be great, but that's not really the important part).</p>
  
  <p>The main thing I am inferring from the its-there-after-you-cut-and-paste behavior is that the data is in the database but there's something wrong with some sort of character set setting somewhere.</p>
  
  <p>If I override the default encoding of the mysql client on the command line like so:</p>
  
  <pre><code>mysql --default-character-set=utf8 
  </code></pre>
  
  <p>Then do the same select, the string comes out as:</p>
  
  <pre><code>"BingÂ® BlangÂ™ Blaow"
  </code></pre>
  
  <p>which is to say that both the (R) and (TM) symbols appear and are in the right place but both are preceded by the unicode character \xae which is an A with a circumflex on top.</p>
  
  <p>(Incidentally this is also how the data is displayed when I pull it out using python and display it on a web page, which is what my real problem is).</p>
  
  <p>Anyway, what is going on here?  Everything we have done recently has used UTF8 everywhere possible, but it's possible that some of these rows were inserted prior to that change which means they would've been using the latin1 default... however neither encoding seems to produce the right result? </p>
  
  <p>If the rows were inserted when the default encoding on the table was latin1 before it was switched to utf8, then the encoding was switched (via alter table..) then would the encoding have actually been updated?  Should one of the encodings work now?  Will unicode ever stop kicking my ass?</p>