mysql - 如何在 MySQL 准备语句中使用 COLLATE

Question

我需要在存储过程中的预准备语句中指定与 LIKE 相关的 COLLATE，例如<col> LIKE ? COLLATE utf8_unicode_ci .我收到以下错误:

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

我还尝试通过所有参数转换参数:LIKE _utf8 ? COLLATE utf8_unicode_ci , LIKE CONVERT(? AS utf8) COLLATE utf8_unicode_ci和 LIKE CAST(? AS varchar CHARACTER SET utf8) COLLATE utf8_unicode_ci但错误是这样的:

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 ...

任何提示将不胜感激。

Answer 1

据我所知，_utf8 是 string literals 语法的一部分.因为没有，所以不能使用。

signature for CAST()这是:

CAST(expr AS type)

我认为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);