The new collation must be legal for the character set of the string. For example, you can use the utf8_general_ci collation with utf8 strings, but not with latin1 strings:
mysql> SELECT _latin1 'abc' COLLATE utf8_bin; ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'
To convert both the character set and collation of a string, use CONVERT() to change the character set, and apply the COLLATE operator to the result:
mysql> SET @s1 = 'my string'; mysql> SET @s2 = CONVERT(@s1 USING utf8) COLLATE utf8_spanish_ci; mysql> SELECT CHARSET(@s1), COLLATION(@s1), CHARSET(@s2), COLLATION(@s2);
The CONVERT() function can also be used to convert binary strings to nonbinary strings and vice versa. To produce a binary string, use binary; any other character set name produces a nonbinary string:
The function can also be used to convert binary strings to nonbinary strings and vice versa. To produce a binary string, use ; any other character set name produces a nonbinary string:
mysql> SET @s1 = 'my string'; mysql> SET @s2 = CONVERT(@s1 USING binary); mysql> SET @s3 = CONVERT(@s2 USING utf8); mysql> SELECT CHARSET(@s1), CHARSET(@s2), CHARSET(@s3);
CHARSET(@s1) CHARSET(@s2) CHARSET(@s3)
latin1 binary utf8
Alternatively, you can produce binary strings using the BINARY operator, which is equivalent to CONVERT(str USING binary):
Please check back next week for the continuation of this article.
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.
