Insert statements working when inserting emojis, but throws error when using eloquent

Question

Running the query,

INSERT INTO table (ref_id, user_id, role_id, text) VALUES (233, 3, 40, 'Hdhdhdh😜😀😊😃hzhzhzzhjzj 我爱你 ❌')

works just fine in Sequel Pro, but when using eloquent, it throws an error,

"SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x9C\xF0\x9F...' for column 'text' at row 1 (SQL: INSERT INTO table (ref_id, user_id, role_id, text) VALUES (233, 3, 40, 'Hdhdhdh😜😀😊😃hzhzhzzhjzj 我爱你 ❌')"

Answer 1

Cause of the problem:

Laravel, by default, uses utf8 as charset for MySQL. In MySQL, a UTF8 char is up to 3 bytes long (utf8 is an alias for utf8mb3). Whereas Emoji characters are up to 4 bytes long.

Therefore we need to use utf8mb4 as our charset.

Solution:

1. Open your config/database.php

2. Find the MySQL section:

'mysql' => [
    'driver' => 'mysql',
    [...]
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    [....]

3. Change charset, collation to utf8mb4 and utf8mb4_unicode_ci respectively:

'mysql' => [
    'driver' => 'mysql',
    [...]
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    [....]

4. Save and reset your database:

Note that if you reset your database, all of your data will be erased!

php artisan migrate:reset