Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.
TL;DR: On MySQL 8.4.11,
ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4ran in place with writes allowed when no converted column was indexed, and copied the table when one was: 9.3 seconds on 2,000,000 rows, with anINSERTwaiting 7.8 of them. It is refused by a foreign key on a string column (error 3780), an index that would pass 3,072 bytes (3886 or 1071), aVARCHARover 16,383 characters (1074) and values the new collation treats as equal (1062). When it runs, it turnsTEXTintoMEDIUMTEXTand stops ignoring trailing spaces.
Converting a MySQL table from utf8mb3 to utf8mb4 is one statement, and what that statement costs depends on something it never mentions: whether any column it converts is in an index. I ran it on MySQL 8.4.11 in a throwaway Docker container on a laptop. On a 2,000,000-row table with no index on its string column, it finished in 2.8 seconds and an INSERT sent during it returned in 0.1 seconds. On a 2,000,000-row table with one indexed VARCHAR, it copied the table for 9.3 seconds and the INSERT waited 7.8 of them. Other tables refused the conversion outright, for four different reasons, and the ones that converted came back with a different column type and different rules for which strings are equal.
The reason to do it anyway is that utf8mb3, the character set MySQL long called utf8, stores at most 3 bytes a character. An emoji needs 4, and in strict mode, the default, the write fails:
CREATE TABLE customers ( id BIGINT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL, name VARCHAR(200) NOT NULL, bio TEXT, UNIQUE KEY customers_code_key (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; INSERT INTO customers (code, name) VALUES ('rocket', 'Rocket 🚀'); -- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x9A\x80' for column 'name' at row 1 The CREATE TABLE itself already carries a warning on 8.4: 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead (warning 1287).
How do you convert utf8mb3 to utf8mb4 in MySQL?
Run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 on each table, because changing the table's default character set converts nothing.
The ALTER TABLE documentation separates the two. CONVERT TO CHARACTER SET changes "the table default character set and all character columns (CHAR, VARCHAR, TEXT)". DEFAULT CHARACTER SET changes only "the character set that is used if you do not specify the character set for columns that you add to a table later". The second one is the trap, because it succeeds and the table then reports utf8mb4:
CREATE TABLE notes ( id BIGINT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(200) NOT NULL ) DEFAULT CHARSET=utf8mb3; ALTER TABLE notes DEFAULT CHARACTER SET utf8mb4; After it, information_schema.tables showed the table as utf8mb4_0900_ai_ci, the column was still utf8mb3, and SHOW CREATE TABLE had written the old set onto the column itself: body varchar(200) CHARACTER SET utf8mb3 NOT NULL. The emoji insert failed with the same error 1366. A check that reads the table's collation passes; the data has not moved.
Does converting to utf8mb4 lock the table?
Only when a converted column is in an index: then MySQL copies the table and every write waits for the copy, and otherwise the conversion runs in place with writes allowed.
Two tables of 2,000,000 rows each show both cases. plain has one VARCHAR(255) and no index on it. events has a VARCHAR(40) with an index and a VARCHAR(255), 369 MB on disk. While each conversion ran, a second session sent one INSERT:
| Table | CONVERT TO CHARACTER SET utf8mb4 | INSERT during it | With ALGORITHM=INPLACE, LOCK=NONE |
|---|---|---|---|
plain, no string column indexed | 2.4 s | 0.09 s | Accepted, 2.8 s |
events, one string column indexed | 9.3 s | 7.8 s, returned when the copy ended | Refused, error 1846 |
On events a SELECT sent during the copy still returned in 0.10 seconds: reads go through, writes do not. The refusal reads ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. The manual's online DDL table lists "Converting a character set" as an in-place operation, and that held for plain. It does not say that one index on a converted column takes the in-place path away, and that is the common case: a table whose only strings are unindexed is rare.
The same split holds per column. On an unindexed column, MODIFY body VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL took 0.09 seconds on 2,000,000 rows, and an emoji insert worked straight after. On the indexed column it was refused in place with the same error 1846. Two other directions copy the table even with no index: back from utf8mb4 to utf8mb3, and from latin1 to utf8mb4, where the stored bytes have to change.
So add ALGORITHM=INPLACE, LOCK=NONE to the statement before you run it for real. It costs nothing when the conversion can run in place, and it turns a surprise table copy into an error. The timings are from a laptop: read them as the gap between the two cases, not as what your server will take. How the other ALTER TABLE forms compare is in which MySQL changes lock the table.
What stops the conversion from running?
Four things: a foreign key on a string column, an index that would pass 3,072 bytes, a VARCHAR longer than 16,383 characters, and two values the new collation treats as one.
| Error | What MySQL said | What to do |
|---|---|---|
| 3780 | Referencing column 'customer_code' and referenced column 'code' in foreign key constraint 'orders_customer_code_fkey' are incompatible. | Convert both tables in one session with foreign_key_checks = 0 |
| 3886, 1071 | The resulting size of index 'pages_url_key' would exceed the max key length of 3072 bytes. or Specified key was too long; max key length is 3072 bytes | Shorten the column, index a prefix, or index a hash |
| 1074 | Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead | Change the column to a TEXT type first |
| 1062 | Duplicate entry 'Hauptstrasse' for key 'streets.streets_name_key' | Resolve the duplicates, or name a collation that keeps them apart |
The foreign key. orders.customer_code references customers.code, both VARCHAR(40). Converting customers alone failed with 3780, and so did converting orders alone: the two columns of a key must share a character set at every moment, and each statement would leave them apart. The manual says the same in general terms: with foreign_key_checks enabled, "character set conversion is not permitted on tables that include a character string column used in a foreign key constraint". Turning the check off for the session let both statements through, and the key enforced again afterwards: an order for an unknown customer failed with error 1452.
SET foreign_key_checks = 0; ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4; ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4; SET foreign_key_checks = 1; The index. InnoDB keeps at most 3,072 bytes of a key and counts every character at its widest. A unique index on pages.url VARCHAR(1000) is 3,000 bytes in utf8mb3 and 4,000 in utf8mb4, so the conversion was refused with error 3886. A plain index on the same column, and a two-column index on a VARCHAR(767) and a BIGINT, were refused with error 1071 instead. The quick fix is a prefix, UNIQUE KEY pages_url_key (url(768)), and the conversion then ran. But a unique prefix index compares only the prefix: two URLs that differed after character 768 could not both be stored any more, and the second insert failed with a duplicate entry error. Uniqueness had changed from "the whole URL" to "the first 768 characters". A stored hash keeps the original rule at any length:
ALTER TABLE pages DROP INDEX pages_url_key, ADD COLUMN url_hash BINARY(32) AS (UNHEX(SHA2(url, 256))) STORED NOT NULL, ADD UNIQUE KEY pages_url_hash_key (url_hash); With it, the two long URLs both went in and an exact repeat was refused.
The long column. A VARCHAR can hold 65,535 bytes, which is 21,845 characters at 3 bytes each and 16,383 at 4. A VARCHAR(20000) column, legal in utf8mb3, refused the conversion with error 1074.
The duplicate. With no COLLATE clause the columns move from utf8mb3_general_ci to utf8mb4_0900_ai_ci, and the two disagree about ß: the old collation compares it equal to s, the new one to ss. A unique name column holding Hauptstraße and Hauptstrasse was valid before and refused the conversion with 1062. Plain accents are not the issue: é equals e in both.
What does CONVERT TO CHARACTER SET change without telling you?
Two things in the table itself, the type of TEXT columns and how trailing spaces compare, and one outside it: a join to a table you have not converted yet stops using that table's index.
TEXT becomes MEDIUMTEXT. After the conversion, customers.bio was mediumtext. The manual explains it: CONVERT TO "changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column", and a TEXT column's 65,535 bytes hold fewer 4-byte characters than 3-byte ones. An ORM model that still says TEXT now disagrees with the database, and the next schema comparison reports a difference nobody made on purpose.
Trailing spaces start to count. utf8mb3_general_ci pads before comparing, so a unique column that held 'sql' refused 'sql ' with a duplicate entry error. utf8mb4_0900_ai_ci does not pad. After the conversion the same insert was accepted, the table held both values, and WHERE name = 'sql' matched one row. Input that used to be caught as a duplicate is now stored as a second value.
The unconverted side of a join loses its index. A rollout converts tables one at a time, so for a while a utf8mb4 table joins a utf8mb3 one. I joined a converted kinds table to a 200,000-row utf8mb3 copy of events on kind. With both sides in utf8mb4, EXPLAIN showed a ref lookup on events_kind_idx. With events still in utf8mb3, it scanned the whole index, an estimated 198,074 rows, and warned: Cannot use ref access on index 'events_kind_idx' due to type or collation conversion on field 'kind' (warning 1739). MySQL converts the utf8mb3 column to compare it, and a converted column cannot be looked up in its own index. The utf8mb4 side keeps its index, so the slow queries are the ones that look up rows in a table still waiting its turn.
CONVERT TO CHARACTER SET or MODIFY each column: which should you use?
Use CONVERT TO when you want every column moved at once and accept the type changes; use MODIFY per column when the column types must stay exactly as they are.
CONVERT TO CHARACTER SET | MODIFY each column | DEFAULT CHARACTER SET | |
|---|---|---|---|
| Converts existing columns | All of them | The ones you name | None |
| Copies the table, holding writes | If any string column is indexed | If a column you name is indexed | No |
TEXT stays TEXT | No, it became MEDIUMTEXT | Yes | Yes |
| Collation | The set's default, unless you add COLLATE | Whatever each column names | Unchanged on existing columns |
The per-column form is the one the manual recommends when the types matter, and it kept body TEXT as text here:
ALTER TABLE articles DEFAULT CHARACTER SET utf8mb4, MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 NOT NULL, MODIFY body TEXT CHARACTER SET utf8mb4; It has its own cost: MODIFY restates the whole column. A VARCHAR(50) NOT NULL DEFAULT 'untitled' with a comment, modified as VARCHAR(50) CHARACTER SET utf8mb4, came back nullable with no default and no comment.
Decide the collation on purpose, and be careful with the one that looks safest. CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci keeps the old comparisons, so the Hauptstraße table converted without an error. But utf8mb4_general_ci compares every character outside the Basic Multilingual Plane as equal: a unique column that held 🚀 refused 🎉 with error 1062. For a table converted so that it can hold emoji, that is the wrong trade. utf8mb4_0900_ai_ci tells them apart, at the price of resolving the duplicates it finds first.
How do you find what the conversion will hit before you run it?
Ask the catalog for four lists, the columns still in utf8mb3, the indexes that pass 3,072 bytes at 4 bytes a character, the foreign keys on string columns and the VARCHAR columns over 16,383 characters, then read the migration against the live schema.
-- 1. every column still to convert SELECT table_name, column_name, column_type FROM information_schema.columns WHERE table_schema = 'shop' AND character_set_name = 'utf8mb3'; -- 2. indexes the conversion would push past 3,072 bytes (errors 3886 and 1071) SELECT s.table_name, s.index_name, SUM(CASE WHEN c.character_set_name = 'utf8mb3' THEN IFNULL(s.sub_part, c.character_maximum_length) * 4 ELSE IFNULL(c.character_octet_length, 8) END) AS bytes_in_utf8mb4 FROM information_schema.statistics s JOIN information_schema.columns c ON c.table_schema = s.table_schema AND c.table_name = s.table_name AND c.column_name = s.column_name WHERE s.table_schema = 'shop' GROUP BY s.table_name, s.index_name HAVING SUM(c.character_set_name = 'utf8mb3') > 0 AND bytes_in_utf8mb4 > 3072; -- 3. foreign keys on string columns: convert both tables together (error 3780) SELECT k.constraint_name, k.table_name, k.column_name, k.referenced_table_name, k.referenced_column_name FROM information_schema.key_column_usage k JOIN information_schema.columns c ON c.table_schema = k.table_schema AND c.table_name = k.table_name AND c.column_name = k.column_name WHERE k.table_schema = 'shop' AND k.referenced_table_name IS NOT NULL AND c.character_set_name = 'utf8mb3'; -- 4. columns too long for 4 bytes a character (error 1074) SELECT table_name, column_name, character_maximum_length FROM information_schema.columns WHERE table_schema = 'shop' AND character_set_name = 'utf8mb3' AND data_type = 'varchar' AND character_maximum_length > 16383; Query 2 counts 8 bytes for each column that is not a string, enough for integers and dates; on the test schema it returned the three indexes MySQL went on to refuse, at 4,000, 4,000 and 3,076 bytes. None of the four finds the duplicates a new collation creates. For those, rehearse the conversion on a copy of the data.
Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Connected to a small copy of this schema, its schema lint reported each of the three tables once, naming its utf8mb3 string columns, with the reason: an emoji written to them is refused with error 1366. A table is one finding because it is one fix, and a utf8mb3 column inside a utf8mb4 table is reported on its own.
A second rule reports a key longer than InnoDB keeps, counted at the column's own character set, so a column switched to utf8mb4 in the diagram shows the 3,072-byte problem before any migration exists.
For the migration itself, paste the file into the impact analysis. I gave it the three CONVERT TO CHARACTER SET statements for customers, orders and pages, with the two SET foreign_key_checks lines around the first pair, against that database with 300,000 rows loaded into pages. It analysed all five statements, with none left unread, and reported a table rewrite for each of the three tables with its row count and size, because each has an indexed string column; held-back writes on pages while the copy reads its rows, which it reports for tables from 10,000 rows up; and that changing customers.code reaches orders through orders_customer_code_fkey, the pair error 3780 is about. That is what it means to see the impact of every change before it runs: the copy, the wait and the key it touches are on screen while the statement is still text.
It does not predict the refusals from a migration file: the key limit, the long column and the duplicates still need the queries above and a rehearsal.
If the conversion is part of a larger move, the neighbouring cases are measured in MariaDB to MySQL schema differences, where the default collation is the first thing MySQL refuses, and in MySQL to PostgreSQL schema conversion, where the character set stops being a per-column decision at all.
