MySQL 中的字符集和校对规则

1、查看当前数据库支持的字符集和校对规则

（1）查看字符集：SHOW CHARSET;

+----------+-----------------------------+---------------------+--------+

| Charset  | Description                 | Default collation   | Maxlen |

+----------+-----------------------------+---------------------+--------+

| big5      | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |

| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |

| utf16     | UTF-16 Unicode              | utf16_general_ci    |      4 |

| utf16le   | UTF-16LE Unicode            | utf16le_general_ci  |      4 |

| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |

+----------+-----------------------------+---------------------+--------+

我们常用的支持中文的字符集UTF-8 Unicode，默认的校对规则是utf8mb4_general_ci（这个一般要改为utf8_bin，即大小写敏感），注意：这个以_ci后缀是大小写不敏感的。Maxlen表示当前字符集每个字符可能占用的最大字节数。

（2）校对规则：SHOW COLLATION;

+--------------------------+----------+-----+---------+----------+---------+

| Collation                | Charset  | Id  | Default | Compiled | Sortlen |

+--------------------------+----------+-----+---------+----------+---------+

| utf8_general_ci          | utf8     |  33 | Yes     | Yes      |       1 |

| utf8_bin                 | utf8     |  83 |         | Yes      |       1 |

| utf8_unicode_ci          | utf8     | 192 |         | Yes      |       8 |

| utf8_icelandic_ci        | utf8     | 193 |         | Yes      |       8 |

| utf8_unicode_520_ci      | utf8     | 214 |         | Yes      |       8 |

+--------------------------+----------+-----+---------+----------+---------+

查看校对规则，可以看到utf8的校对规则，大致可以分为两类：其中一类是以_ci结尾，是大小写不敏感，另一类是以_bin结尾，是以二进制存储的，是大小写敏感的。

补充知识点：

校对规则以_cs、_bin结尾的是大小写敏感。

校对规则以_bin结尾的是大小写不敏感的。

（3）举个栗子：

创建表如下：

CREATE TABLE tbl_alter_table(

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

name VARCHAR(20) NOT NULL COLLATE utf8_general_ci,

PRIMARY KEY(id)

)ENGINE INNODB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

这里将整个表的默认字符集设置为utf8，默认校对规则设置为utf8_bin（大小写敏感）,但是字段name中的校对规则我们设置为utf8_general_ci（大小写不敏感）。

注意：上述建表语句被修改为如下：

CREATE TABLE `tbl_alter_table` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(20) CHARACTER SET utf8 NOT NULL,

  PRIMARY KEY (`id`)

)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

即使int被改为int(10)其实和int是一样的，占用内存仍是32位。

另外：需要注意的是CHARACTER SET utf8应该是使用默认的校对规则，大小写不敏感。

测试输入一些数据：

INSERT INTO tbl_alter_table (name) values (‘tim’);

INSERT INTO tbl_alter_table (name) values (‘TIM’)

查看根据WHERE条件中字段nam=‘tim’，看输出的结果：

SELECT * FROM tbl_alter_table WHERE name=’tim’;

输出结果为：

mysql> SELECT * FROM tbl_alter_table WHERE name='tim';

+----+------+

| id | name |

+----+------+

|  1 | tim  |

|  2 | TIM  |

+----+------+

说明根据大小写不敏感区查询tim会查出所有大小写不敏感的tim。

补充知识点：

字符集和校对规则的覆盖原则是：数据库设置<表设置<字段设置

即：如果没有设置则用上一级默认的规则。