Recipe 5.6. Changing a String s Character Set or Collation

Recipe 5.6. 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 = CONVERT(@s1 USING utf8) COLLATE utf8_spanish_ci; mysql> SELECT CHARSET(@s1), COLLATION(@s1), CHARSET(@s2), COLLATION(@s2); +--------------+-------------------+--------------+-----------------+ | CHARSET(@s1) | COLLATION(@s1)    | CHARSET(@s2) | COLLATION(@s2)  | +--------------+-------------------+--------------+-----------------+ | latin1       | latin1_swedish_ci | utf8         | utf8_spanish_ci | +--------------+-------------------+--------------+-----------------+ 

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:

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):

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