How can I force the value of a MySQL query to use

Question

I want to use UUID() to generate primary keys.

After some investigation, I thought this should work:

INSERT INTO aTable (`id`) VALUES (UNHEX(REPLACE(UUID(),'-','')));

Where id is of the type BINARY(16).

Unfortunately, UUID() returns a value which uses the utf8_general_ci collation. The rest of my database uses utf8_unicode_ci, which means I get the following error:

#1270 - Illegal mix of collations (utf8_general_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'replace'

How can I persuade UUID() to play nicely and use utf8_unicode_ci?

Answer 1

INSERT INTO aTable (`id`) VALUES (UNHEX(REPLACE(UUID() COLLATE utf8_unicode_ci,'-','')));