MySQL utf8mb4 Charset: Full Unicode and Emoji Support Setup

Why utf8 in MySQL does not support emoji

MySQL implemented the utf8 encoding as 3-byte, which covers most characters but not emoji (they take 4 bytes in UTF-8). When an application tries to store an emoji in a utf8 column, MySQL either silently truncates the string at the first problem character or returns an error, depending on the sql_mode. utf8mb4 is true 4-byte UTF-8, supporting the full Unicode range, including emoji and rare CJK characters.

Configuring my.cnf for utf8mb4 by default

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
character-set-filesystem = utf8mb4

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

sudo systemctl restart mysql

Checking the current encoding

mysql -u root -p -e "
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';"

Changing the encoding of an existing database

-- Change the database charset
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Change the table charset
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Change a column
ALTER TABLE mytable MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Converting every table in a database

-- Generate the commands for every table
SELECT CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` ',
    'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS migration_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_TYPE = 'BASE TABLE';

Common conversion errors explained

• Incorrect string value: '\xF0\x9F\x98\x80' for column — the column is still utf8 or latin1 while the application is already trying to write an emoji; the conversion from the section above must run on this table before inserting again.
• Specified key was too long; max key length is 767 bytes — an index on VARCHAR(255) under utf8mb4 can take up to 1020 bytes (255x4), past the old table format's limit; the fix is shortening the indexed field, switching to the InnoDB engine with large prefixes, or enabling innodb_large_prefix.
• Illegal mix of collations for operation 'UNION' — a partial conversion left some tables on utf8_general_ci while others are already on utf8mb4_unicode_ci; queries joining such tables fail until the collations are unified.
• Data too long for column after conversion — 4-byte characters take more space than was budgeted when the VARCHAR length was chosen; sometimes the column length has to grow too, not just the charset.

Collation: unicode_ci vs general_ci vs bin

Setting it up in PHP (PDO)

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
    'user', 'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
    ]
);

Comparing utf8 and utf8mb4

When the conversion is not that simple

• The database already holds emoji data saved as "truncated" strings under utf8 — the original text cannot be recovered from the corrupted bytes; the data has to be re-fetched from the source instead.
• A table uses a FULLTEXT index — changing the charset requires rebuilding it from scratch, since an on-the-fly conversion does not carry over the index statistics automatically.
• The application connects to MySQL through several different drivers (PHP, Java, Python cron scripts) — the charset must change consistently everywhere a connection is made, or some services will keep writing corrupted data even after the tables are converted.

How to verify the conversion succeeded

# Confirm every table in the database is on utf8mb4
mysql -u root -p -e "
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mydb' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';"
# An empty result means every table has been converted

# A test emoji insert
mysql -u root -p mydb -e "
CREATE TEMPORARY TABLE emoji_test (t TEXT) CHARACTER SET utf8mb4;
INSERT INTO emoji_test VALUES ('Emoji test: ');
SELECT * FROM emoji_test;"

If the query against information_schema finds no tables outside utf8mb4, and the test insert runs with no Incorrect string value error, the conversion is complete and correct at the database level.

Frequently asked questions

Does converting a database to utf8mb4 risk losing data in existing rows, or is it always a safe operation?

The conversion command is usually safe for rows that already held correct text, since it widens the character set rather than narrowing it. The risk shows up where a column already holds corrupted bytes from a failed attempt to store an emoji; conversion cannot restore such values, and the only way back is fetching the data from its source again.

Does utf8mb4 actually run slower than utf8 in practice since a character can take more bytes?

In most ordinary workloads the difference is barely noticeable: the extra byte per character adds a small amount to disk and memory usage, while string comparison under the chosen collation runs the same way either way. A visible effect shows up only where indexes on long string columns hit the key length limit and need shortening or an engine change.

If an application already has emoji data corrupted by an old encoding, can the original text be recovered?

No, if a character was truncated at the encoding level down to the first problem byte, part of the original information is destroyed for good and no later charset change brings it back. The only reliable path is fetching that data again from its source, whether an export file, an application log, or the external service it came from.

Does the application code also need its encoding changed, or is changing it in the database enough?

Settings need to change consistently in both places: if the database is already on utf8mb4 but the connection from the application still opens with the old encoding, the server will silently convert characters to the old set on the fly, bringing back the same truncation problem. Connection and database settings both need to point at the same charset.