utf8mb4_0900_ai_ci, and the collation mismatch in a join

MySQL 8.0 changed the default collation, so a table created before an upgrade and one created after cannot be joined on a string column without a conversion.

SELECT * FROM orders o JOIN legacy_refs r ON r.code = o.code;
-- ERROR 1267: Illegal mix of collations
--   (utf8mb4_0900_ai_ci) and (utf8mb4_unicode_ci)

SELECT table_name, table_collation FROM information_schema.tables
WHERE table_schema = DATABASE();

ALTER TABLE legacy_refs CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

The error is at least explicit, which is more than can be said for the version where the join silently works and cannot use the index — a mismatch on a joined column can force a full scan without any error at all. Auditing collations after a major version upgrade is a five-minute query and is the sort of thing nobody does until a report takes ninety seconds. 0900 refers to Unicode 9.0 and is not comparable byte-for-byte with the older unicode_ci.