title: Understanding character sets in column and data and how they affect select queries
body: <p>After reading articles about how to properly store your data inside a mysql database, I sort of have a good understanding of how character sets work. Yet, there are still some instances that I find hard to understand. Let me give a quick example: Say I have a table with a varchar column using the latin1 charset (for example purposes)</p>
  
  <pre><code>CREATE TABLE `foobar` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `foo` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>and I force insert a latin1 character using its hex value (á for instance, which would be E1)</p>
  
  <pre><code>SET NAMES 'latin1';
  INSERT INTO foobar SET foo = UNHEX('E1')
  </code></pre>
  
  <p>why does a SELECT query against this table under a latin1 connection fail to give me the value I am expecting? I get the replacement character instead. I would have expected it to just give back the value as it is since the column was in latin1, the connection itself was in latin1 and the value stored is a valid latin1 character. Is mysql trying to interpret E1 as a UTF8 value? Or am I missing something here?</p>
  
  <p>Using a UTF8 connection (SET NAMES 'utf8') before selecting gives me the expected value of á, which I guess is correct since from what I understand, if the connection charset is different from the column charset being selected, then mysql would read data as whatever the column charset was (so E1 gets parsed as a latin1 character, or á)</p>