Avris

Putting emojis in your database should be a piece of cake, right? You’ve had enough trouble with encodings in your lifetime, and now that we have the blessing of UTF-8, you’re always so careful to use it everywhere, so you’d expect all the characters to just finally work out of the box, right?

Well, I did expect that. But I’ve recently realised I can only put some emojis (like “️”) in by blog posts. Most of them were just lost or replaced with “?” by MySQL... Oh, those damn encodings again!

But fortunately the solution is quite simple.

Instead of the utf8 charset, you need to use its superset, utf8mb4. This one handles 4-byte characters and that’s exactly what we need. We just have to make both the database and Doctrine use it.

phpMyAdmin or Adminer let you change the charset easily. Just go to edit table schema and switch the collation of relevant columns and tables to utf8mb4_unicode_ci. Since you’re going from utf8 to its superset, no data should become corrupted in the process. But just to play it safe, better create a backup before you start.

You might encounter an error: “Specified key was too long; max key length is 767 bytes”. That’s because of some limitations of InnoDB regarding indices length. To make an index into mb4, you’ll have to shorten the max length of a column to 191 (instead of max 255 for simple utf8).

When it comes to Doctrine, you have to specify the collation and charset on the level of an entity/table and on the level of the connection.

For an entity, if you’re using annotations:

/**
 * @ORM\Entity
 * @ORM\Table(options={"collate"="utf8mb4_unicode_ci", "charset"="utf8mb4"})
 */

Or if you’re using XML:

<entity name="Acme\DemoBundle\Entity\User">
    <options>
        <option name="charset">utf8mb4</option>
        <option name="collate">utf8mb4_unicode_ci</option>
    </options>
</entity>

As for the connection, go with:

$conn = \Doctrine\DBAL\DriverManager::getConnection(
    [
        'driver'   => 'pdo_mysql',
        'host'     => 'localhost',
        'user'     => 'user',
        'password' => 'password',
        'dbname'   => 'database',
        'charset'  => 'utf8mb4',
    ],
    new \Doctrine\DBAL\Configuration()
);

Or in Symfony:

# app/config/config.yml
doctrine:
    dbal:
        charset:  utf8mb4

Or in Micrus:

# app/Config/parameters.yml
database:
  driver: pdo_mysql
  host: 127.0.0.1
  dbname: database
  user: user
  password: password
  charset: utf8mb4

And that would be it (hopefully). Enjoy your new emoji support!