Cast Functions and Operators CAST( string  AS CHAR[( N )] CHARACTER SET  charset_name ) SELECT CONVERT(’test’, CHAR CHARACTER SET utf8mb4); SELECT CAST(’test’ AS CHAR CHARACTER SET utf8mb4); SELECT CONVERT(’test’ USING utf8mb4) COLLATE utf8mb4_bin; SELECT CONVERT(’test’, CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin; SELECT CAST(’test’ AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin; SELECT CONVERT(’test’ USING utf8mb4 COLLATE utf8mb4_bin); SELECT CONVERT(’test’, CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin); SELECT CAST(’test’ AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin); SELECT ’A’ LIKE CONVERT( blob_col  USING utf8mb4) FROM  tbl_name ; SELECT ’A’ LIKE CONVERT( blob_col  USING utf8mb4) COLLATE utf8mb4_unicode_ci FROM  tbl_name ; mysql>  SET @s1 = _latin1 ’abc’, @s2 = _latin2 ’abc’; mysql>  SELECT @s1 = @s2; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_general_ci,IMPLICIT) for operation ’=’ mysql>  SELECT @s1 = CONVERT(@s2 USING latin1); +---------------------------------+ | @s1 = CONVERT(@s2 USING latin1) | 2231 Normally, you cannot compare a  BLOB  value or other binary string in case-insensitive fashion because binary strings use the  binary  character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, first use the  CONVERT()  or  CAST()  function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the conversion result collation is not case-sensitive, a  LIKE  operation is not case-sensitive. That is true for the following operation because the default  utf8mb4  collation ( utf8mb4_0900_ai_ci ) is not case-sensitive: But these are illegal: To specify a particular collation for the converted string, use a  COLLATE  clause following the  CONVERT() call: To use a different character set, substitute its name for  utf8mb4  in the preceding statements (and similarly to use a different collation). CONVERT()  and  CAST()  can be used more generally for comparing strings represented in different character sets. For example, a comparison of these strings results in an error because they have different character sets: Converting one of the strings to a character set compatible with the other enables the comparison to occur without error: If you specify  CHARACTER SET  charset_name  as just shown, the character set and collation of the result are  charset_name  and the default collation of  charset_name . If you omit CHARACTER SET  charset_name , the character set and collation of the result are defined by the character_set_connection  and  collation_connection  system variables that determine the default connection character set and collation (see Section 10.4, “Connection Character Sets and Collations” ). A  COLLATE  clause is not permitted within a  CONVERT()  or  CAST()  call, but you can apply it to the function result. For example, these are legal: Examples: