Connections, Character Sets and Strings - 5.6 Changing a String’s Character Set or Collation
(Page 4 of 4 )
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:
Changing a String’s Character Set or Collation
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:
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):
mysql> SET @s1 = 'my string';
mysql> SET @s2 = BINARY @s2;
mysql> SELECT CHARSET(@s1), CHARSET(@s2);
+--------------+--------------+
| CHARSET(@s1) | CHARSET(@s2) |
+----------------+----------------+
| latin1 | 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. |
|
 This article is excerpted from chapter five of the MySQL Cookbook, Second Edition, written by Paul DuBois (O'Reilly; ISBN: 059652708X). Check it out today at your favorite bookstore. Buy this book now.
|
|
