Database Reference
In-Depth Information
mysql> SET @s1 = _latin1 'my string', @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 = _latin1 'my string', @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 = _latin1 '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 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 = _latin1 '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, produce binary strings using the BINARY operator, which is equivalent to
CONVERT( str USING binary) :
Search WWH ::




Custom Search