文字編碼和文字校對

今天在煩文字編碼和文字校對的問題,在網上找找相關資料,發現一篇不錯的說明。
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);
//SELECT * FROM `table` WHERE name=CONVERT("測試" USING latin1) COLLATE latin1_swedish_ci;

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