MySQL字符集和校对规则
1.MySQL字符集和校验规则
- 字符集：是一套符号和编码的规则
- 校验规则：是对该套符号和编码的校验，定义字符的排序和比较规则,其中是否区分大小写，跟校验规则有关。
2.查看字符集方法
netstat -lntup |grep 3306
tcp6 0 0 :::3306 :::* LISTEN 1241/mysqld
2.1 查看mysql支持的字符集
show character set;
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 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
2.2 查看字符集的校对规则
show collation;
gbk_chinese_ci ： 不区分大小写
gbk_bin ： 区分大小写
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------+---------+----+---------+----------+---------+---------------+
| gbk_bin | gbk | 87 | | Yes | 1 | PAD SPACE |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 | PAD SPACE |
+----------------+---------+----+---------+----------+---------+---------------+
2 rows in set (0.01 sec)
2.3 查看当前数据库的字符集
\s
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.36, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.36 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 8 min 2 sec
Threads: 1 Questions: 12 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.024
--------------
show variables like ‘character%’;
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-5.7.36-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
character_set_client #客户端请求数据的字符集
character_set_connection #客户端与服务端连接的字符集
character_set_database # 数据库默认使用的字符集
character_set_filesystem # binary表示操作系统中的文件名不做任何转换
character_set_results #查询结果返回数据的结果集
character_set_server # mysql服务端的字符集
character_set_system #系统的字符集
2.4 查看当前数据库的校对规则
show variables like ‘collation%’;
mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
3.MySQL字符集设置
3.1 字符集设置层级关系
服务端：character_set_server > database > table
（如果在创建表没有指定字符集，则默认用数据库，如果数据库也没有设置，则默认使用server的字符集。）
客户端：character_set_connection > character_set_results
3.2 设置MySQL服务器级别字符集
在配置文件设置：
[root@sdns ~]# cat /etc/my.cnf
[client]
default_character_set=utf8 //客户端不需要重启，就能影响。它影响的参数是：character_set_client character_set_connection character_set_results
[mysqld]
character_set_server=utf8 // 需要重启才能生效，character_set_server character_set_database 以及表的字符集
临时设置：
set 一旦数据库重启，设置失效
set [session] character_set_server=utf8; 针对当前会话窗口有效
set global character_set_server=utf8; 设置全局，对所有会话有效。只针对新打开的会话。
set names gbk; 设置客户端的字符集，只影响client、connection、results
这样将其服务端和客户端都设置为utf8
mysql> \s;
--------------
mysql Ver 14.14 Distrib 5.7.36, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.36 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 min 10 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.085
--------------
ERROR:
No query specified
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| 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 | /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.01 sec)
3.3 设置创建对象的字符集
库：create database db1 character set utf8;
show create database db1; 查看数据库创建的语句
表：create table t1(id int)engine=innodb charset=utf8;
show create table t1; 查看创建表的语句
4.字符集案例
4.1常用字符集每个汉字占用字节多少
utf8mb4: 4byte
utf8: 3byte
gbk: 2byte
4.2大小案例
字段类型为varchar(30)，30代表什么？30个字符，不是字节
字段类型为varchar(30)，在utf8字符集下，可以存多少汉字？共占用多少字节？
30个汉字，30*3+1（<255, 多占1字节；>255占2字节）
字段类型为varchar(30)，在utf8字符集下，可以存多少英文？共占用多少字节？
30个英文，30*1+1
字段类型为varchar(30)，在gbk字符集下，可以存多少汉字？共占用多少字节？
30个汉字，30*2+1
字段类型为varchar(30)，在gbk字符集下，可以存多少英文？共占用多少字节？
30个英文，30*1+1
如何选择：
如果是内部人员使用，则使用gbk，节点空间和带宽
如果有交互，则使用utf8
如果支持大空间，例如小图标则使用utf8mb4
5.插入中文乱码解决
mysql> create table t(id int default null,name varchar(10) default null);
Query OK, 0 rows affected (0.01 sec)
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb3 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.0.31-linux-glibc2.12-x86_64/share/charsets/ |
mysql> insert into t values(1,'zs');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t values(2,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+------+--------+
| id | name |
+------+--------+
| 1 | zs |
| 2 | 张三 |
+------+--------+
2 rows in set (0.00 sec)
如果退出重新进，查看乱码
mysql> select * from t;
+------+---------------+
| id | name |
+------+---------------+
| 1 | zs |
| 2 | å¼ ä¸‰ |
+------+---------------+
2 rows in set (0.00 sec)
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | latin1 // 不统一 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb3 |
| character_set_system | utf8mb3
因为这些保持不一致，所以乱码，要保持统一
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
+------+--------+
| id | name |
+------+--------+
| 1 | zs |
| 2 | 张三 |
+------+--------+
2 rows in set (0.00 sec)
6.数据库常见字符集及如何选择字符集
| 长度 | |||
|---|---|---|---|
| latin1 | 1 | 默认 | |
| gbk | 2 | ||
| utf8 | 3 | ||
| utf8mb4 | 4 |
7.生产中如何彻底避免出现乱码
操作系统
[root@mysql8 ~]# cat /etc/local
locale.conf localtime
[root@mysql8 ~]# cat /etc/locale.conf
LANG="en_US.UTF-8"
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
保证这个配置统一设置
1830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
