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! 😊