【MySQL架构篇】MySQL字符集、大小写规范及默认数据库

1. 字符集与字符集比较规则

MySQL有4个级别的字符集和比较规则，分别是

• 服务器级别
• 数据库级别
• 表级别
• 列级别

当创建对应表或列未指定字符集时，默认会取其上一级别的字符集

执行如下SQL语句：

SHOW variables LIKE '%character%';

• character_set_server: 服务器级别的字符集
• character_set_database: 当前数据库的字符集
• character_set_client: 服务器解码请求时使用的字符集
• character_set_connection: 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
• character_set_results: 服务器向客户端返回数据时使用的字符集

查看字符集默认比较规则：

SHOW charset;

上表中，MySQL版本一共支持41种字符集，其中的 Default collation 列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较 utf8_spanish_ci 是以西班牙语的规则比较，utf8_general_ci 是一种通用的比较规则

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下:

后缀	英文释义	描述
_ai	accent insensitive	不区分重音
_as	accent sensitive	区分重音
_ci	case insensitive	不区分大小写
_cs	case sensitive	区分大小写
_bin	binary	以二进制方式比较

通常我们都是使用utf8_general_ci

说明1：

utf8_unicode_ci 和 utf8_general_ci 对中、英文来说没有实质的差别

utf8_unicode_ci 校对速度快，但准确度稍差

utf8_general_ci 准确度高，但校对速度较差

一般情况，用 utf8_general_ci 就够了，但如果应用中有德语、法语或者俄语，请一定使用 utf8_unicode_ci

说明2：

修改了数据库的默认字符集和比较规则后，原来创建的表的字符集和比较规则并不会改变，如果需要，那么需要单独修改

响应过程中编码解码的总结图：

2. 大小写规范

在MySQL中，windows系统默认大小写不敏感，但是 linux 系统是大小写敏感的

通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%'; -- linux 0; windows 1

• lower_case_table_names参数值的设置：
  • Linux默认为0，大小写敏感
  • 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找
  • 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行

两个平台上SQL大小写的区别具体来说：

1. 数据库名、表名、表的别名、变量名是严格区分大小写的;
2. 关键字、函数名称在SQL 中不区分大小写;
3. 列名(或字段名)与列的别名(或段别名)在所有的情况下均是忽略大小写的

在MySQL 8下禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置成不同于初始化 MySOL 服务时设置的 lower_case_table_names 值

PS：非大佬请不要尝试将windows的修改为0，作者已经因为想要这么操作导致不得不重装MySQL了

如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

1. 关键字和函数名称全部大写;
2. 数据库名、表名、表别名、字段名、字段别名等全部小写:
3. SQL 语句必须以分号结尾

3. 默认数据库

安装MySQL后，系统默认带的四个默认数据库

• mysql

  MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息一些运行过程中产生的日志信息，一些帮助信息以及时区信息等

• information_schema

  保存着MySQL服务器 维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表，用于表示内部系统表

• performance_schema

  主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控MySQL 服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息

• sys

  主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能

4. 与文件系统相关

举例: 数据库a ， 表b

1、如果表b采用 InnoDB，data\a中会产生1个或者2个文件：

• b.frm : 描述表结构文件，字段长度等
• 如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
• 如果采用 独立表空间 存储模式，data\a中会产生 b.ibd 文件(存储数据信息和索引信息)

此外

1. MySQL5.7 中会在data\a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如: 字符集、比较规则。而MySQL8.0不再提供 db.opt文件
2. MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中

2、如果表b采用MyISAM，data\a中会产生3个文件:

• MySQL5.7 中: b.frm : 描述表结构文件，字段长度等

  MySQL8.0 中 b.xxx.sdi : 描述表结构文件，字段长度等

• b.MYD (MYData): 数据信息文件，存储数据信息(如采用独立表存储模式)

• b.MYI(MYIndex): 存放索引信息文件