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 and 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 ci vs 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!
civsbincollations. UTF-8 has a whole load of processing overhead that's really not needed for simple ASCII fields, and it eats index space too. As I said though, I've done this many times before without issue – this project has been doing this for a couple of years, but only just recently has this error started happening. I'm wondering if there's been some recent change in MySQL that's caused it.civsbin, but either way, those are collations, not character sets, so there is no issue of data loss. And sure, non-binary UTF-8 is certainly more complex than non-binary ASCII. But, binary is just that: binary, hence no linguistic rules. There shouldn't be much, if any, overhead forutf8_bincollation. There might be some overhead for theutf8_mb4charset regarding determination of valid byte sequences, but that shouldn't have a noticeable impact here. (cont.)