I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn’t need UTF-8 overhead:
CREATE TABLE `things` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `uuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `things_uuid_unique` (`uuid`) ) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
and I’m getting query failures like this:
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_bin,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select * from `things` where (`uuid` = 1abb9e11-4f00-4904-988e-233a3c0ce411) limit 1)
My connection is using
utf8mb4_unicode_ci, the other fields, table, and database are using the same collation, and my scripts themselves are also UTF-8, though that makes no practical difference here since it’s just ASCII. This particular query uses a string literal containing only 7-bit ASCII chars, not a field name or variable.
I don’t understand why this fails, since (unlike ISO-8859 charsets), ASCII is UTF-8 compatible; there is no ASCII string that is not also a valid UTF-8 string, so why wouldn’t this be coercible? Is it something to do with
bin? I can’t solve this by switching the connection to ASCII because the real queries use multiple fields, some of which are UTF-8.
I know that I can force the comparison using:
select * from `things` where (`uuid` = BINARY '1abb9e11-4f00-4904-988e-233a3c0ce411')
But that also seems unnecessary, and not something I’ve needed to do before, in the same circumstances. I guess worst case I could switch the UUID field to UTF-8, but that offends my developer sensibilities!
It’s doubly frustrating as I know I’ve used this pattern successfully many times before!