MySQL字符集相关知识

博文记录本人学习MySQL过程中的一些理解，如果有不正确的地方欢迎指正

1 查看正在使用的字符集

mysql> show variables like '%char%';  
+--------------------------+---------------------------------------+  
| Variable_name            | Value                                 |  
+--------------------------+---------------------------------------+  
| character_set_client     | utf8                                  |  
| character_set_connection | utf8                                  |  
| character_set_database   | utf8                                  |  
| character_set_filesystem | binary                                |  
| character_set_results    | utf8                                  |  
| character_set_server     | utf8                                  |  
| character_set_system     | utf8                                  |  
| character_sets_dir       | D:\mysql-5.7.17-win32\share\charsets\ |  
+--------------------------+---------------------------------------+  
8 rows in set, 1 warning (0.00 sec)  

2 查看字符排序方式

mysql> show variables like '%colla%';  
+----------------------+-----------------+  
| Variable_name        | Value           |  
+----------------------+-----------------+  
| collation_connection | utf8_general_ci |  
| collation_database   | utf8_general_ci |  
| collation_server     | utf8_general_ci |  
+----------------------+-----------------+  
3 rows in set, 1 warning (0.00 sec)  

排序方式我的理解就是字符的排序规则，比如按照字典序排序等。

3 查看已有字符集和排序方式
学会查看使用中的字符集和排序方式之后，再来看看我们安装的MySQL数据库中，已经安装了多少种字符集和排序方式。
3.1 查看已有字符集

mysql> show character set;  
+----------+---------------------------------+---------------------+--------+  
| Charset  | Description                     | Default collation   | Maxlen |  
+----------+---------------------------------+---------------------+--------+  
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |  
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |  
| cp850    | DOS West European               | cp850_general_ci    |      1 |  
| hp8      | HP West European                | hp8_english_ci      |      1 |   
41 rows in set (0.00 sec)

3.2 查看已有的排序方式

mysql> show collation;  
+--------------------------+----------+-----+---------+----------+---------+  
| Collation                | Charset  | Id  | Default | 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 |  
222 rows in set (0.00 sec)  

知道这些字符集和排序方式之后，我就能根据需要修改所使用的的字符集和排序方式了。