MySQL Cookbook, 2nd Edition

Changing a String’s Character Set or Collation

Problem

You want to convert a string from one character set or collation to another.

Solution

Use the CONVERT() function to convert a string to another character set. Use the COLLATE operator to convert a string to another collation.

Discussion

To convert a string from one character set to another, use the CONVERT() function:

mysql>SET @s1 = 'my string';
mysql> SET @s2 = CONVERT(@s1 USING utf8);
mysql> SELECT CHARSET(@s1), CHARSET(@s2);
+--------------+--------------+
| CHARSET(@s1) | CHARSET(@s2) |
+--------------+--------------+
| latin1       | utf8         |
+--------------+--------------+

To change the collation of a string, use the COLLATE operator:

mysql>SET @s1 = 'my string';
mysql> SET @s2 = @s1 COLLATE latin1_spanish_ci;
mysql> SELECT COLLATION(@s1), COLLATION(@s2);
+-------------------+-------------------+
| COLLATION(@s1)    | COLLATION(@s2)    |
+-------------------+-------------------+
| latin1_swedish_ci | latin1_spanish_ci |
+-------------------+-------------------+

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