Unable to store unicode characters into MySQL database

Before I go on with this article I have to throw in an important warning, before you start updating change your database structure. 

​Important warning: Don't change the database encoding or structure on a live database or a database filled with content. Changing the encoding of your database or tables can lead to content loss.

Now it is time to start with my story. As I said in my intro, I was recently busy upgrading my blog and updating a few articles. Everything went without problems until I tried to save my blog about the new Twitter symbol. When I tried to save this one it returned a MySQL database error: 

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9D\x95\x8F

When an error message like this shows up it normally means that the database doesn't support one or more characters from the data you want to store. It can be hard to figure out which character is wrong most of the time. The best thing you can do is to remove content in the data set you want to store until the error is gone. Then you know that the character was in the last part you deleted.

In my case, the error was in the new Twitter symbol: 𝕏. This Unicode character is not supported by the MySQL UTF8 charset. While UTF8 is today's standard you should not use it for storing data in the database. You should always set your MySQL charset to utf8mb4. The charset utf8mb4 offers full Unicode support, while utf8 doesn't. Besides the 𝕏 character, more characters cannot be stored in a database with the charset set to utf8.

If you're using the PHP PDO connection you should also update your DSN string to charset=utf8mb4 and include the "SET NAMES utf8mb4" command.

$pdo = new PDO(
    dsn: 'mysql:dbname=database;host=127.0.0.1;port=3306;charset=utf8mb4',
    username: 'username',
    password: 'password,
    options: [
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
    ]
);

Final word

By luck, I ran into this issue. So when I found this simple solution I thought it was time to create a blog. It is not often you get problems like this. I hope this one was helpful for you guys.

​遠くから誰かを見守ることも、一種の愛です。
Watching Over Someone From Afar Is A Kind Of Love, Too. - Sailor Moon