Search code examples
mysqlutf-8character-encodinglatin1

Character encoding issues with MySQL - Collation set to UTF-8, but text stored as ISO Latin 1

I have a table for which the collation is set to ut8_general_ci. The columns in the table are also set to this encoding.

For some reason though, when I view my table in MySQL the text in the table is rendered/stored as ISO Latin 1, and if I wan't to edit any of the text in any of my entries in the table I have to use ISO Latin 1 encoding to make sure the text renders correctly on the page (the page user UTF8).

Manually entering ISO Latin 1 text to the table normally works, but for a couple of entries it isn't working, i.e. the characters aren't rendering correctly on my page (they also don't render correctly when I use UTF8). Hence this question. If I could work out how MySQL handles character encoding I could probably sort this issue.

So, the question is can someone explain how MySQL handles encoding, and specifically why I am seeing ISO Latin 1 characters in my table when the collation is set to UTF-8.

Solution

  • It turns out that despite setting the character encoding on the page and on the table and the columns of the table, the connection to the table was defaulting to ISO Latin 1. Adding 'SET NAMES utf8' to my query fixed this, i.e.

    $pdo = new PDO('mysql:dbname=****;host=localhost', '****', '****',
                array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

    The article deceze linked to was very helpful. Thanks!