Storing Emoji in a Database: utf8mb4, Byte Sizes and the .length Trap

To store emoji safely in MySQL, the column, the table and the connection all need utf8mb4. The older utf8 charset (really utf8mb3) caps characters at three bytes, and 3,779 of the 3,963 emoji in Unicode 18.0, 95.4%, need a four-byte character somewhere inside them. Once the charset is right, the next bugs are length limits: an emoji costs between 3 and 35 bytes, and every database counts a "character" differently.

We measured every byte figure below against Unicode's own list of fully-qualified emoji, the same 3,963 our complete emoji list is built from. Database behaviour is quoted from each vendor's documentation, checked on 2026-10-22.

What one emoji costs, by type

UTF-8 spends one to four bytes per code point, and most emoji are several code points glued together. Here is the spread across all 3,963, grouped by how each emoji is built:

• Single code point (1,193 emoji): 3 or 4 bytes, median 4. The 60 that sit below U+FFFF, such as ☕ and ⚡, take 3. 😀 takes 4.
• Base plus the U+FE0F style selector (207): 5 to 7 bytes, median 6. ❤️ is 6. The cheapest are ©️ and ®️ at 5.
• Keycaps (12): 7 bytes each, for a digit, FE0F and U+20E3.
• Skin tone, no joiner (675): 7 or 8 bytes, median 8. 👍🏽 is 8.
• Country flags (259): always 8 bytes, two regional indicators at 4 each.
• Joined sequences without tones (249): 10 to 27 bytes, median 13. The four-person family is 25.
• Joined sequences with tones (1,365): 15 to 35 bytes, median 17. 👩🏽‍💻 is 15.
• Tag-sequence flags (3): 28 bytes each. 🏴󠁧󠁢󠁳󠁣󠁴󠁿 is a black flag followed by six invisible tag characters at 4 bytes apiece.

Across everything the median is 8 bytes and the mean is 11.33. Only 30.1% fit in 4 bytes, 59.2% fit in 8, and 87.6% fit in 20. The largest is 🧑🏻‍❤️‍💋‍🧑🏼, a kiss with two different skin tones, at 35 bytes, 15 UTF-16 code units and 10 code points.

The practical reading: "4 bytes per emoji" is the figure in most tutorials, and it is right for less than a third of them. If you budget storage, a message queue payload or an SMS segment in bytes, budget for the tail.

The 184 emoji that hide the utf8mb3 bug

MySQL's documentation is blunt about the old charset. utf8mb3 "supports BMP characters only (no support for supplementary characters)" and requires "a maximum of three bytes per multibyte character". It is deprecated, and the manual says to expect its removal in a future major release.

The trap is that some emoji are built entirely from BMP code points, and those store fine in utf8mb3. We counted 184 of them: 60 single characters, 110 symbols with a style selector, all 12 keycaps and two joined sequences, ⛹️‍♂️ and ⛹️‍♀️. ❤️, ☕, ✅, ⭐ and ✈️ are all in that group.

Those are also exactly the emoji a developer is likely to type when testing a form. The heart saves, the check mark saves, the ticket gets closed, and the first real user who sends 😂 hits the error. Test with a four-byte emoji such as 😀, whose UTF-8 bytes are F0 9F 98 80, and with a long sequence like the family. If those round-trip, the rest will.

What MySQL actually does with a four-byte character

It depends on SQL mode. MySQL 8.4's default sql_mode includes STRICT_TRANS_TABLES, and for transactional tables in strict mode an invalid value means "the statement is aborted and rolled back". The error is 1366, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD, whose message template is Incorrect %s value: '%s' for column '%s' at row %ld. For an emoji it fills in as an incorrect string value showing the raw bytes, which is why \xF0\x9F is the giveaway in a log.

Without strict mode, MySQL "inserts adjusted values for invalid or missing values and produces warnings". The insert reports success, the stored value is no longer the emoji you sent, and the only trace is a warning that nobody reads. That silent version is far worse, because the damage is only found when someone opens the record weeks later.

Your installer or hosting provider may have changed the mode. Check with SELECT @@GLOBAL.sql_mode; rather than assuming the default.

Moving a table to utf8mb4 without breaking indexes

The conversion itself is one ALTER TABLE. MySQL's own example sets DEFAULT CHARACTER SET utf8mb4 and then MODIFYs each column to CHARACTER SET utf8mb4. Three details catch people out:

1. Index limits are in bytes, not characters. On InnoDB tables with the COMPACT or REDUNDANT row format the key limit is 767 bytes, which the manual translates to 255 characters for utf8mb3 but only 191 for utf8mb4. With DYNAMIC or COMPRESSED the limit rises to 3,072 bytes, or 768 utf8mb4 characters. An index on VARCHAR(255) under the old format will need shortening.
2. Small text types shrink. A TINYTEXT holds 255 bytes, which the manual puts at 85 three-byte or 63 four-byte characters.
3. The connection must match. A utf8mb4 column fed through a utf8mb3 connection still fails. Set the client charset in your driver's connection options, or run SET NAMES 'utf8mb4'.

On MySQL 8.4 the server defaults are already utf8mb4 with the utf8mb4_0900_ai_ci collation, so new databases are fine. The problems live in schemas created years ago and carried forward through dumps and migrations.

The collation that thinks every emoji is the same emoji

Fixing the charset is not the end. MySQL's manual says that in UCA 4.0.0 collations such as utf8mb4_unicode_ci, every supplementary character gets the collating weight 0xfffd: "to MySQL, all supplementary characters are equal to each other." The general collations do the same thing.

Put a unique index on a column with that collation and 😀 collides with 💩. A tag table rejects 🍕 because 🔥 already exists. GROUP BY on a reaction column merges every emoji reaction into one row. Nothing errors, so the bug looks like a product decision.

The fix is a collation based on a newer UCA, such as the default utf8mb4_0900_ai_ci, which the manual says "works for all characters in the range [U+0, U+10FFFF]", or a binary collation where you want exact matching. Check what your existing tables use before you trust a uniqueness constraint on user text.

PostgreSQL and SQLite: fewer charset traps, different length rules

PostgreSQL has no three-byte UTF-8 mode to fall into. With a UTF8 database encoding (check with SHOW server_encoding;) any emoji stores as is. Its documentation says varchar(n) stores "up to n characters (not bytes)", meaning code points. A string that is too long raises value too long for type character varying(n), but an explicit cast to varchar(n) "will be truncated to n characters without raising an error". That cast is where Postgres splits sequences silently.

SQLite goes further and ignores the number entirely. Its type documentation says the digits in VARCHAR(255) "are ignored by SQLite" and that it "does not impose any length restrictions" beyond a global maximum. Its length() function returns code points, octet_length() returns bytes, and substr() indexes by code point. If your app relies on the column definition to enforce a limit, on SQLite nothing enforces it.

SQL Server counts in yet another unit

Microsoft's docs call it "a common misconception" that n means characters. For nvarchar(n) it is byte-pairs, so a supplementary character uses two of them: the same surrogate pairs JavaScript sees. For varchar(n) with a UTF-8 collation, available since SQL Server 2019, n is bytes.

That means the same declared size of 20 holds very different amounts depending on the engine:

• 😀: 20 in MySQL or PostgreSQL VARCHAR(20), 10 in nvarchar(20), 5 in UTF-8 varchar(20).
• 👍🏽 or 🇯🇵: 10, 5 and 2.
• 👨‍👩‍👧‍👦 or 🏴󠁧󠁢󠁳󠁣󠁴󠁿: 2, 1 and zero. A UTF-8 varchar(20) cannot hold one family emoji.

One 20-character username, five different answers

Take the display name Priya 👩🏽‍💻🇮🇳. A person reads it as eight characters: five letters, a space and two emoji. Here is what each layer of a typical stack measures:

• HTML maxlength and JavaScript .length: 17 UTF-16 code units.
• MySQL, PostgreSQL and SQLite character functions, and Python's len(): 12 code points.
• Bytes on disk and on the wire: 29.
• SQL Server nvarchar: 17 byte-pairs.
• Intl.Segmenter: 8 graphemes.

Now apply a rule of "20 characters" at three points. The browser enforces 20 code units, the API validates 20 code points in Python, and the column is VARCHAR(20) in MySQL. Each layer accepts a different set of names. A user with a long name and two toned emoji passes the database and fails the form, and a script posting straight to the API can store names the form would have refused.

The HTML part is in the spec, not a browser quirk. WHATWG says maxlength is "measured using length", and its Infra standard defines a string's length as "the number of code units it contains". Pick one unit for the whole stack, document it, and enforce it once on the server.

What a cut in the wrong place stores

When a value is too long, something usually trims it. Here is that same name cut by slice(), which works in UTF-16 code units, at each length from 7 to 16:

• 7, 9, 12, 14 and 16: the cut lands between the two halves of a surrogate pair and leaves a lone surrogate. JavaScript's isWellFormed() returns false for every one.
• 8: Priya 👩. The woman survives, without her tone or her laptop.
• 10: Priya 👩🏽, a toned woman with no job.
• 11: the toned woman plus a trailing zero width joiner, invisible and waiting to glue itself to whatever is appended next.
• 13: the technologist, whole, and no flag.
• 15: the technologist plus a single regional indicator 🇮, which most systems draw as a boxed letter.

The lone surrogate is the dangerous one, because it changes when the string is serialised. Passed through TextEncoder, Priya \ud83d becomes EF BF BD at the end, the UTF-8 for U+FFFD, the replacement character. Cutting the UTF-8 bytes directly has the same effect: trimming the name to 12 bytes decodes as Priya 👩�. That � is now permanent data in your table.

Trimming by code points never produces invalid text, which is why PostgreSQL's truncating cast does not corrupt anything. It still strands joiners and half flags. The only safe cut is at a grapheme boundary: segment the string, then add whole segments until the next one would cross the limit. Run with a 15 code unit budget, that approach returns Priya 👩🏽‍💻 and drops the flag cleanly.

Checklist before you ship a text column

1. MySQL: column, table, database and connection all on utf8mb4. Grep your migrations for utf8 without the mb4.
2. Collation: not a UCA 4.0.0 or general collation on any column with a unique index on user text.
3. sql_mode: strict, so a bad write fails loudly instead of storing an adjusted value.
4. Indexes: prefix lengths recalculated for four-byte characters.
5. One length unit chosen for the product, usually graphemes for anything a user sees, enforced on the server.
6. Column sized in the database's own unit, with headroom. A 20-grapheme name can need 20 code points or several times that.
7. Truncation only on grapheme boundaries, anywhere: SQL casts, log formatters, notification previews.
8. Test data that includes 😀, a family, a toned profession, a country flag and 🏴󠁧󠁢󠁳󠁣󠁴󠁿.

Tools for checking a specific emoji

To see the exact bytes and code points of anything above, paste it into our emoji Unicode converter, which prints UTF-8, UTF-16 and escape sequences side by side. The ZWJ sequence list shows the size of every joined design, the skin tone chart has all five shades of every toneable emoji for test fixtures, and the flag test page decodes the regional indicator pairs. Our developer reference collects length tables for twelve languages and databases in one place.

For background, our explainer on joined sequences covers why families are seven code points, the skin tone guide covers how modifiers attach, and the piece on flags that will not render explains the letter pairs behind every country flag. If you need to find emoji in the text before you store it, we tested the common patterns in which emoji regex actually works.