How to use COLLATE in MySQL prepared statement

Question

I need to specify COLLATE in connection with LIKE inside a prepared statement inside a stored procedure, e.g. <col> LIKE ? COLLATE utf8_unicode_ci. I'm getting the following error:

COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'binary'

I have also tried to cast the parameter by all of: LIKE _utf8 ? COLLATE utf8_unicode_ci, LIKE CONVERT(? AS utf8) COLLATE utf8_unicode_ci and LIKE CAST(? AS varchar CHARACTER SET utf8) COLLATE utf8_unicode_ci but the error is then something like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar CHARACTER SET utf8) COLLATE utf8_unicode_ci ...

Any hint would be greatly appreciated.

Answer 1

AFAIK, the _utf8 is part of the syntax for string literals. Since you don't have, you cannot use it.

The signature for CAST() is this:

CAST(expr AS type)

I think you really want CONVERT():

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

[...]

CONVERT() with USING converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8 character set:

SELECT CONVERT('abc' USING utf8);