Fix 'Incorrect string value' for emoji inserts in MySQL

Your emoji won't save, right?

Yeah, that error is annoying. You paste a smiley face or a thumbs-up emoji into your database and MySQL throws error 1366: 'Incorrect string value: '\xF0\x9F\x98\x81''. Here's the fix.

The real fix

Two things need to happen. First, change the column's character set to utf8mb4. Second, make sure your connection uses utf8mb4 too. Here's how.

ALTER TABLE your_table_name MODIFY your_column_name TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

If your column is VARCHAR, just swap TEXT with VARCHAR(255) or whatever length you need. After that, update your connection settings. In PHP that looks like:

$db = new mysqli($host, $user, $pass, $dbname);
$db->set_charset('utf8mb4');

In MySQLi or PDO, you set the charset before any queries. For PDO, add it to the DSN:

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', $user, $pass);

That's it. Emoji works now. Had a client last month whose entire chat log broke because they skipped the connection charset. Their column was utf8mb4 but the PHP connection was still utf8. Took 30 seconds to fix.

Why this works

MySQL's default utf8 (which is actually utf8mb3) only supports 3-byte characters. Emoji are 4-byte characters. So MySQL sees the 4-byte sequence and says "nope, can't store that." utf8mb4 is the real UTF-8 that handles 4-byte characters. The column and connection both need to be on the same charset. If one is utf8 and the other is utf8mb4, you get mismatches.

Less common variations

1. The table default is utf8 but individual column is fine

Sometimes the table's default charset is utf8, even if you changed the column. That causes issues when you add new columns later. Run this to check:

SHOW CREATE TABLE your_table_name;

If the table default is still utf8, change it:

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Old data with emoji still broken after fix

If you already have bad data (like question marks or truncated strings), you'll need to re-insert it. No magic tool recovers it. But future inserts will work.

3. Server-level charset mismatch

Some shared hosts lock the server's default charset to utf8. You can override it per connection. If that fails, contact your host—but usually the connection charset override works.

4. Using a framework (Laravel, Rails, Django)

Check the framework's database config file. For Laravel, edit config/database.php and set 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci'. For Rails, add encoding: utf8mb4 in database.yml. Django needs 'OPTIONS': {'charset': 'utf8mb4'} in settings.

Prevention

From day one, set your database default charset to utf8mb4. When creating a new table:

CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Also set the connection charset in your app's bootstrap. If you use an ORM, it often handles this automatically if you configure it right. Test with an emoji after setup—just one line in your test suite saves headaches.

One more thing: indexes on varchar columns using utf8mb4 are a bit larger. For columns that don't need emoji (like usernames), keep them utf8. But for any text field where users might paste emoji, use utf8mb4. That's the pragmatic way.

"Skip the pain. If your app accepts user input, expect emoji at some point. A client last week lost three days of customer messages because they ignored this."

That's it. Change the column, change the connection, test with an emoji. Done.