MySQL字符集和校对规则

字符集(Character set)

字符集是一套符号和编码

特点

1. 字符编码方式是用一个或多个字节表示字符集中的一个字符
2. 每种字符集都有自己特有的编码方式，因此同一个字符，在不同字符集的编码方式下，会产生不同的二进制

常见字符集

• ASCII 字符集：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码。
• GB2312 字符集：收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集。
• GBK 字符集：GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。
• UTF8 字符集：收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

命令

查看字符集编码设置

mysql> show variables like '%character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.10 sec)

Variable_name	note
character_set_client	客户端来源数据使用的字符集
character_set_connection	连接层字符集
character_set_database	当前选中数据库的默认字符集
character_set_results	查询结果字符集
character_set_server	默认的内部操作字符集
character_set_system	系统元数据(字段名等)字符集

库、表、列字符集

1. 建库时，若未明确指定字符集，则采用 character_set_server 指定的字符集。
2. 建表时，若未明确指定字符集，则采用当前库所采用的字符集。
3. 新增、修改字段时，若未明确指定字符集，则采用当前表所采用的字符集。

设置字符集编码

mysql> set names 'utf8';
Query OK, 0 rows affected

等于

mysql> set character_set_client = utf8;
mysql> set character_set_results = utf8;
mysql> set character_set_connection = utf8;

修改数据库字符集

mysql> alter database database_name character set utf8;

只修改库的字符集，影响后续创建的表的默认定义。对于已创建的表的字符集不产生影响。

修改表的字符集

mysql> alter table table_name character set utf8;

只修改表的字符集，影响后续创建的表的默认定义。对于已创建的列的字符集不产生影响。

mysql> alter table table_name convert character set utf8;

同时修改表字符集和已有列字符集，并将已有的数据进行字符集编码转换

修改列的字符集

mysql> alter table table_name modify col_name varcher(col_length) character set utf8;

校对规则(collation)

在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的不区分。

特征

1. 两个不同的字符集不能有相同的校对规则
2. 每个字符集有一个默认校对规则
3. 存在校对规则命名约定 ：以其相关的字符集命名开始，中间包括一个语言名，并且以_ci（不区分大小写） _cs（区分大小写） _bin（二元）结束

case

建表是不指定校对规则

mysql> create table test_coll(id int,name varchar(5));
Query OK, 0 rows affected

mysql> insert into test_coll(id,name) values(1,'a'),(2,'A');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_coll;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | A    |
+----+------+
2 rows in set

mysql> select * from test_coll where name ='a';
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | A    |
+----+------+
2 rows in set

命令

查看数据库支持的所有校对规则

mysql> show collation;

查看当前字符集和校对规则设置

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8_general_ci    |
| collation_server     | utf8_unicode_ci    |
+----------------------+--------------------+
3 rows in set