mysql字符集小结
字符集是英文,汉字或者其他语言字符的集合, 字符集种类有很多, 每个字符集包含的字符个数也不相同. 字符编码方式是用一个或多个字节表示字符集中的一个字符; 每种字符集都有自己编码方式, 因此同一个字符, 在不同字符集的编码方式下, 会产生不同的二进制值;
ASCII字符集, UTF8字符集, GBK字符集都是常见字符集. ASCII字符集: 基于罗马字母表的一套字符集, 是采用1个字节的低7位表示字符, 高位始终为0. UTF8字符集: Unicode字符集的一种, 支持了所有国家的文字字符, utf8采用1-4个字节表示字符. GBK字符集: 支持中文, 字符有一字节编码和两字节编码方式.
一. MySQL字符集
1.1
字符集
只要涉及到文字的地方, 就会存在字符集和编码方式.
MySQL系统变量值:
mysql> show variables like '%character%';
+--------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql5.7/share/charsets/ |
+--------------------------+-------------------------------------+character_set_client: 客户端使用的字符集 character_set_connection: 连接层使用的字符集 character_set_database: 当前数据库使用的字符集 character_set_results: 查询结果使用的字符集 character_set_server: mysql服务使用的默认字符集 character_set_system: 系统元数据使用的字符集
1.2
字符集应用
(1) 基础应用
建库时, 若未明确指定字符集, 则采用character_set_server指定的字符集; 建表时, 若未明确指定字符集, 则采用当前库所采用的字符集; 新增或修改表字段时, 若未明确指定字符集, 则采用当前表所采用的字符集;
(2) 更新,查询涉及到得字符集
插入或更新流程字符集转换过程 character_set_client-->character_set_connection-->表(列)字符集. MySQL服务端接到插入或更新SQL后, 发现有字符, 会查看客户端字符集(character_set_client), 当MySQL发现客户端字符集与自己的connection不一样时, 会将client的字符集转换为connection的字符集, MySQL将编码转换后的数据存储到MySQL表的列上, 在存储时, 会再判断编码是否与列字符集上的编码是否一致, 如果不一致需要再次转换.
查询流程字符集转换过程 表(列)字符集-->character_set_result 结果转换为与客户端相同的字符集再传递给客户端. (character_set_results默认等于character_set_client)
注意: 所有的字符集转换都发生在数据库端, 为避免出现乱码问题, 要保证各字符集一致.
1.3
字符集操作命令
(1) 查看字符集编码设置
mysql> show variables like '%character%';(2) 设置字符集编码
mysql> set names 'utf8';相当于同时执行以下3个命令:
set character_set_client = utf8;
set character_set_results = utf8;
set character_set_connection = utf8;(3) 修改数据库字符集 只修改库的字符集, 影响以后创建的表的默认定义；对于已创建的表的字符集不受影响.一般在数据库修改字符集即可, 表和列都默认采用数据库的字符集.
mysql> alter database database_name character set xxx;(4) 修改表的字符集 只修改表的字符集, 影响后续该表新增列的默认定义, 已有列的字符集不受影响.
mysql> alter table table_name character set xxx；(5) 同时修改表字符集和已有列字符集, 并将已有数据进行字符集编码转换.
mysql> alter table table_name convert to character set xxx;(6) 修改列字符集
mysql> alter table table_name modify col_name varchar(col_length) character set xxx;二. 校对规则(collation)
校对规则是在字符集内用于字符比较和排序的一套规则, 可以设置区分/无视大小写.
校对规则命名规则是字符集名+语言名+区分后缀, 区分后缀一般是_ci(不区分大小写), _cs(区分大小写)和_bin(二进制)三种.
2.1
校对规则操作命令
查看数据库支持的所有校对规则
mysql> show collation;查看当前字符集和校对规则设置
mysql> show variables like 'collation_%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+2.2
默认校对规则下查询
在不区分大小写(默认)的校对规则下查询, 会忽略字母大小写, 与'a'匹配的值也可以包括'A'.
mysql> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into t1(id, name) values(1, 'a');
Query OK, 1 row affected (0.10 sec)
mysql> insert into t1(id, name) values(2, 'A');
Query OK, 1 row affected (0.74 sec)
mysql> select * from t1 where name = 'a';
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | A |
+----+------+
2 rows in set (0.10 sec)2.3
自定义校对规则下查询
在区分大小写的校对规则下查询, 会严格区分字母的大小写.与'a'匹配的值只能是'a'.
mysql> create table t2(id int,name varchar(20)) character set=utf8mb4 collate=utf8mb4_bin;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t2(id, name) values(1, 'a');
Query OK, 1 row affected (0.10 sec)
mysql> insert into t2(id, name) values(2, 'A');
Query OK, 1 row affected (0.10 sec)
mysql> select * from t2 where name='a';
+----+------+
| id | name |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.10 sec)
mysql> select * from t2 where name='A';
+----+------+
| id | name |
+----+------+
| 2 | A |
+----+------+
1 row in set (0.99 sec)总结
本文主要是对 mysql字符集做下小结, 说明字符集在 MySQL 中都有哪些应用方式, 以及他们之间的关系又是什么样的.