We added emoji reactions to an internal message board. The counts came back wrong in a way that made no sense: one emoji with a count of 40, several others missing entirely, and a total that was correct.

Nothing was lost. Everything had been merged.

SELECT emoji, COUNT(*) FROM reactions GROUP BY emoji;
-- 👍  40      <- this is actually 👍 and 👎 and several others, combined

What’s actually happening

The column was utf8mb4 with the default collation, utf8mb4_unicode_ci. That collation is based on an older Unicode collation table that has no weights defined for most characters outside the Basic Multilingual Plane — which is exactly where emoji live.

Characters with no defined weight sort as equal to each other. Equal, to MySQL, means the same group. So GROUP BY emoji puts them in one bucket, DISTINCT collapses them, and WHERE emoji = '👍' matches rows containing a completely different emoji.

The data is intact. Every byte you stored is still there. Only comparison is broken, which is why SELECT * looks perfectly fine and every aggregate is wrong.

This is a nasty one to catch because it’s collation-dependent, not data-dependent. It works on a dev box with a different default and fails in production, and the two databases look identical in every schema dump you’d think to compare.

The fix

Compare the column as binary for these queries:

SELECT emoji COLLATE utf8mb4_bin AS e, COUNT(*) AS n
FROM reactions
GROUP BY e
ORDER BY n DESC;

Or fix it at the column level, which is what you want if emoji are a real part of your data model:

ALTER TABLE reactions
  MODIFY emoji VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

utf8mb4_bin compares code points directly. Two emoji are equal if and only if they are the same emoji, which is the behavior you assumed you had.

If you’re on MySQL 8 or MariaDB 10.10+, utf8mb4_0900_ai_ci / uca1400 collations use a modern Unicode table and handle emoji correctly too — but they also apply case- and accent-insensitivity you may not want on an identifier column. For a column that holds one emoji, binary is the honest choice.

Check whether you have it — right now

This takes one query and doesn’t change anything:

SELECT '👍' = '👎' AS should_be_zero;

If that returns 1, your connection’s collation merges emoji, and every GROUP BY, DISTINCT, UNIQUE index and WHERE = on an emoji column is currently lying to you.

Also worth checking, because it’s the same root cause with worse consequences:

-- a UNIQUE index under this collation will reject a *different* emoji as a duplicate
SHOW CREATE TABLE reactions;

If you have UNIQUE(user_id, emoji), a user who reacted 👍 cannot then react 👎 — the insert fails as a duplicate key. That one arrives as a bug report about the app “not letting me react,” which nobody would connect to a collation.

What made it hard to see

Every individual piece looked correct. The emoji stored correctly, displayed correctly, round-tripped correctly. The only broken operation was equality, and equality is the operation you never think to test — it’s the thing you use to test other things.

I’d have found this much faster by asking the database a question with a known answer ('👍' = '👎') rather than reading my own aggregate query for the fifth time. When a query returns a wrong number, the fastest move isn’t to inspect the query. It’s to ask the engine something you already know the answer to, and see whether it agrees.

Comments

Loading comments…

Post Comment