Handling Emoji in CSV Imports Without Losing Characters

The CSV has a column of customer reviews, the load finishes without an error, and later someone notices that every smiley is a question mark. The cause is a character set that is named utf8 and cannot hold all of UTF-8, and the reason you did not see it is that MySQL turns the failure into a warning on one path and an error on another. This article reproduces it on MySQL 8.4, shows the fix, and lists the checks that would have caught it. The loading procedure around it is in the MySQL CSV import guide.

What you need

A table whose character set you can read, and the file. The file used here is three rows, two of which carry emoji, including a quoted field with two of them:

id,review
1,great 😀
2,plain
3,"thumbs 👍 and 🎉"

What it looks like when it fails

The table below is declared CHARACTER SET utf8mb3, the character set that older MySQL schemas call utf8. The load asks MySQL to read the file as utf8mb4, which is correct for this file:

mysql -h mysql -uroot --local-infile=1 --default-character-set=utf8mb4 lab <<'SQL'
DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (id INT, review VARCHAR(80)) CHARACTER SET utf8mb3;
LOAD DATA LOCAL INFILE 'emoji.csv' INTO TABLE reviews CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
SHOW WARNINGS;
SELECT id, review, HEX(review) FROM reviews WHERE id = 1;
SQL
echo "exit status $?"

Level	Code	Message
Warning	1366	Incorrect string value: '\\xF0\\x9F\\x98\\x80' for column 'review' at row 1
Warning	1366	Incorrect string value: '\\xF0\\x9F\\x91\\x8D a...' for column 'review' at row 3
id	review	HEX(review)
1	great ?	6772656174203F
exit status 0

All three rows loaded. SHOW WARNINGS reports error code 1366 twice, Incorrect string value: '\xF0\x9F\x98\x80' for column 'review' at row 1 and the same for row 3, and the stored value for row 1 is the hex 6772656174203F, which is great ?. The emoji became 3F, the question mark. The command exited with status 0, so any script or scheduled job that checks only the exit status considered the load a success.

A single INSERT of the same text takes the strict route and fails:

mysql -h mysql -uroot --default-character-set=utf8mb4 lab -e "INSERT INTO reviews VALUES (9, 'great 😀')"; echo "exit status $?"

ERROR 1366 (HY000) at line 1: Incorrect string value: '\xF0\x9F\x98\x80' for column 'review' at row 1
exit status 1

ERROR 1366 (HY000), status 1. The difference, which I observed and did not trace to a documentation page, is that LOAD DATA converts a value it cannot store and reports a warning, where the strict mode applied to INSERT makes it an error. The practical rule is that after any LOAD DATA you must read the warnings, which is also true of the truncation warnings in fixing data truncated warnings in a MySQL CSV load.

Why three bytes are not enough

UTF-8 uses one to four bytes per character. Most of the world's writing fits in three, and characters outside the Basic Multilingual Plane, which includes emoji and some rare CJK characters, need four. MySQL's first implementation of utf8 stopped at three bytes. The utf8mb4 character set was added to complete it, and utf8 is now an alias for the three byte version, utf8mb3, which MySQL has deprecated. The MySQL manual page for utf8mb3 says the character set is deprecated, that it needs at most three bytes per character, and that it supports only the Basic Multilingual Plane. It recommends utf8mb4 for all new applications.

The two sets are listed in the information schema, with the maximum bytes per character:

mysql -h mysql -uroot lab -e "SELECT @@character_set_server, @@collation_server; SELECT character_set_name, maxlen FROM information_schema.character_sets WHERE character_set_name IN ('utf8mb3','utf8mb4');"

@@character_set_server	@@collation_server
utf8mb4	utf8mb4_0900_ai_ci
CHARACTER_SET_NAME	MAXLEN
utf8mb3	3
utf8mb4	4

utf8mb3 has a maximum length of 3 and utf8mb4 of 4. On a fresh MySQL 8.4 server the default is utf8mb4 with the collation utf8mb4_0900_ai_ci, so a newly created schema is safe. The columns that bite are old ones, created when utf8 meant three bytes, or ones created with an explicit CHARACTER SET utf8 in a script someone copied.

The fix

Make the column utf8mb4, and tell LOAD DATA the file's encoding. The first part is a schema change, which on a populated table is a rebuild, so schedule it. For a new table, say it in the definition. The same load into a utf8mb4 table stores the emoji:

mysql -h mysql -uroot --local-infile=1 --default-character-set=utf8mb4 lab <<'SQL'
DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (id INT, review VARCHAR(80)) CHARACTER SET utf8mb4;
LOAD DATA LOCAL INFILE 'emoji.csv' INTO TABLE reviews CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES;
SELECT id, review, CHAR_LENGTH(review) AS chars, LENGTH(review) AS bytes FROM reviews;
SQL

id	review	chars	bytes
1	great 😀	7	10
2	plain	5	5
3	thumbs 👍 and 🎉	14	20

great 😀 is 7 characters and 10 bytes, since the emoji takes four, and the third row's two emoji are in place. The column length in a VARCHAR(80) counts characters, so room for emoji does not shrink it, though index key limits count bytes and a utf8mb4 index can hit its limit sooner. Also confirm that your connection uses the right character set: the mysql client in a container with a POSIX locale showed ? for the emoji until I passed --default-character-set=utf8mb4, which is a display problem and not a storage one, and the two are easy to confuse.

The other engines need nothing

I checked the same character in the three other tools. PostgreSQL in a UTF8 database (see its character set support chapter) reports one character and four bytes, as does SQLite and Python:

psql -qAt -c "SELECT 'postgres: ' || length('😀') || ' char, ' || octet_length('😀') || ' bytes, encoding ' || current_setting('server_encoding')"
sqlite3 :memory: "SELECT 'sqlite: ' || length('😀') || ' char, ' || length(CAST('😀' AS BLOB)) || ' bytes'"
python -c "s='😀'; print('python:', len(s), 'char,', len(s.encode()), 'bytes, utf-16 units', len(s.encode('utf-16-le'))//2)"

postgres: 1 char, 4 bytes, encoding UTF8
sqlite: 1 char, 4 bytes
python: 1 char, 4 bytes, utf-16 units 2

Python also reports two UTF-16 code units for the same character, which is the figure that JavaScript's length and Java's String.length() would give, and the reason an emoji can count as two there. That matters when a limit is applied in one system and checked in another.

Finding the affected columns

Before reloading anything, list the columns that cannot hold four byte characters. A query against the information schema finds every text column declared with utf8mb3 or its alias utf8, and it is worth running once on any inherited MySQL database:

SELECT table_name, column_name, character_set_name
FROM information_schema.columns
WHERE table_schema = DATABASE() AND character_set_name IN ('utf8', 'utf8mb3');

Anything it lists is a column where an emoji, or any other character outside the Basic Multilingual Plane, will be stored as a question mark when it arrives through LOAD DATA, or rejected on a strict INSERT. Converting a column is an ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4, which rewrites the table and should be tested on a copy first. Check index lengths afterwards, since the maximum key length in bytes is fixed and a four byte character set fits fewer characters into it.

Checks that catch this

Before a load, find the characters that need four bytes in the file with a one line scan: any character above U+FFFF. After a load into MySQL, read SHOW WARNINGS and fail the job when the count is not zero. And compare the byte length and character count of one known row between the file and the table. A script that does all three costs a few lines and removes the failure that would otherwise appear weeks later in a report.

When emoji are not the problem

If the table is already utf8mb4 and the emoji still come out as ?, the problem is the connection or the file: a client sending in Latin 1, or a file that was exported through a narrow encoding and already contains 3F in place of the emoji, which the question marks article covers. If you see ð followed by symbols, the file was decoded as Windows 1252 and is mojibake, a case for the repair article. And if the target is not MySQL, the character set is not the issue and a different limit, such as a column width in bytes in some Oracle setups, is the thing to look for.