常用的字符集如下：

• latin1 支持西欧字符、希腊字符等。
• gbk 支持中文简体字符。
• big5 支持中文繁体字符。
• utf8 几乎支持所有国家的字符。

也可以通过查询 information_schema.character_set 表中的记录，来查看 MySQL 支持的字符集。SQL 语句和执行过程如下：

mysql> SELECT * FROM information_schema.character_sets;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
| hp8                | hp8_english_ci       | HP West European                |      1 |
......

可以使用 SHOW COLLATION LIKE '***';命令来查看相关字符集的校对规则。

mysql> SHOW COLLATION LIKE 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation      | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 |
| gbk_bin        | gbk     | 87 |         | Yes      |       1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)

上面运行结果为 GBK 字符集所对应的校对规则，其中 gbk_chinese_ci 是默认的校对规则，对大小写不敏感。而 gbk_bin 按照二进制编码的值进行比较，对大小写敏感。



也可以通过查询 information_schema.COLLATIONS 表中的记录，来查看 MySQL 中可用的校对规则。SQL 语句和执行过程如下：

mysql> SELECT * FROM information_schema.COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci          | big5               |   1 | Yes        | Yes         |       1 |
| big5_bin                 | big5               |  84 |            | Yes         |       1 |
| dec8_swedish_ci          | dec8               |   3 | Yes        | Yes         |       1 |
| dec8_bin                 | dec8               |  69 |            | Yes         |       1 |
| cp850_general_ci         | cp850              |   4 | Yes        | Yes         |       1 |
| cp850_bin                | cp850              |  80 |            | Yes         |       1 |
......

例 1

分别指定“A”和“a”按照 gbk_chinese_ci 和 gbk_bin 校对规则进行比较。SQL 语句和运行结果如下：

mysql> SELECT CASE WHEN 'A' COLLATE gbk_chinese_ci = 'a' COLLATE gbk_chinese_ci then 1
    -> else 0 end;
+-------------------------------------------------------------------------------------+
| CASE WHEN 'A' COLLATE gbk_chinese_ci = 'a' COLLATE gbk_chinese_ci then 1
else 0 end |
+-------------------------------------------------------------------------------------+
|                                                                                   1 |
+-------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT CASE WHEN 'A' COLLATE gbk_bin = 'a' COLLATE gbk_bin then 1
    -> else 0 end;
+-----------------------------------------------------------------------+
| CASE WHEN 'A' COLLATE gbk_bin = 'a' COLLATE gbk_bin then 1
else 0 end |
+-----------------------------------------------------------------------+
|                                                                     0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

由于 gbk_chinese_ci 校对规则忽略大小写，所以认为两个“A“和“a”是相同的。 gbk_bin 校对规则不忽略大小写，则认为两个字符是不同的。