MySQL 的简单介绍

一、MySQL 的简单介绍

MySQL 是一个关系型数据库管理系统 (Relational Database Management System, RDBMS)，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。

1.1 MySQL 的客户端 / 服务器架构

MySQL 由客户端程序和服务器程序两部分组成。

1.1.1 服务器与客户端的通信

运行过程中的 MySQL 服务器程序和客户端程序在本质上来说都算是计算机中的进程（其中代表 MySQL 服务器程序的进程称之为 MySQL 数据库实例），所以客户端进程和服务器进程的通信实际上是进程间的通信！MySQL支持以下通信过程：

• TCP / IP

  当数据库服务器进程和客户端进程运行在不同的主机中，它们之间必须通过网络进行通信。MySQL 采用 TCP 作为服务器和客户端之间的网络通信协议，并在启动服务器时默认申请 3306 端口号。

• 命名管道和共享内存

  当数据库服务器进程和客户端进程运行在相同的 Windows 主机中，那么可以考虑使用命名管道或者共享内存进行通信。

• UNIX 套接字

  当数据库服务器进程和客户端进程运行在相同的类 UNIX 主机中，那么可以考虑使用 UNIX 套接字进行通信。

1.1.2 服务器处理客户端请求

无论客户端和服务器采用哪一种方式进行通信，最后实现的效果都是客户端进程向服务器进程发送一段文本（MySQL 语句），服务器进程处理后再向客户端进程返回一段文本（处理结果）。

当服务器程序处理来自客户端的查询请求时，大致可分为 3 部分：连接管理、解析优化和存储引擎¹。

• 连接管理

  客户端进程可以通过 TCP/IP、命名管道和共享内存、UNIX 套接字等几种方式与服务器进程建立联系。每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程专门处理与这个客户端进程的交互²；当客户端退出时会与服务器断开连接，服务器会把这个不用的线程缓存起来，从而节省频繁创建和销毁线程的开销。

  在客户端程序发起连接时，需要携带主机信息，用户名、密码等信息，服务器根据这些信息进行认证。如果客户端程序和服务器程序不在一台计算机上，还可以采用传输层安全性协议（Transport Layer Security，TLS） 对连接进行加密。

  当连接建立后，与该客户端关联的服务器会一直等待客户端发送过来的请求。

• 解析优化

  至此，服务器已经获得了文本形式的请求，接下来就是对这段文本进行处理：

  1. 查询缓存³

     MySQL 服务器程序处理查询请求的时候会把刚刚处理过的查询请求和结果缓存起来。如果下一次有同样的请求，直接从缓存中查找，就不用再去底层的表中查找了（这个查询缓存可以在客户端之间共享）。

     当然，MySQL 不会像人一样聪明，也存在缓存不命中的情况，比如：

     • 如果两个查询请求有任何字符上的不同（如空格、注释、大小写等）
     • 如果查询请求中包含某些系统函数、用户自定义变量和函数、系统表

     除了缓存不会名中，还有缓存失效的情况，MySQL 的缓存系统会监测缓存涉及的每一张表，只要该表的结构或者数据被修改，则与该表有关的所有查询缓存都将变为无效并且从缓存中删除。

  2. 语法解析

     如果查询缓存没有命中，接下来就要进入正式的查询阶段了。因为客户端向服务器端发送过来的是一段文本，所以 MySQL 服务器程序首先要对这段文本进行分析。

  3. 查询优化

     在语法解析后，服务器程序获得了需要的信息（如：要查询的表、列，搜索条件等）。但光有这些是不够的，因为我们写的 MySQL 语句执行起来效率可能并不是很高，MySQL 的优化程序会对我们的语句进行优化（如：外连接转内连接、表达式简化等），优化的结果是产生一个执行计划。

• 存储引擎

  MySQL 服务器把数据的存储和提取操作都封装到了一个名为存储引擎的模块中。

  数据库中的表是由一行一行的记录组成的，但这只是一个逻辑上的概念。在物理上怎么表示，怎么从表中读取数据，以及怎么把数据写入具体的屋里存储器上，都是存储引擎负责的事情。不同的表可以有不同的物理存储结构、不同的读写方式。

1.2 相关操作

1. 查看当前服务器支持的存储引擎

   mysql> SHOW ENGINES;
   +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
   | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
   +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
   | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
   | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
   | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
   | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
   | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
   | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
   | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
   | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
   | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
   +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
   9 rows in set (0.00 sec)
   

   • Support：表示该存储引擎是否可用，DEFAULT 指代表当前服务器程序默认的存储引擎⁴。
   • Comment：对该存储引擎的描述。
   • Transactions：代表该存储引擎是否支持事物。
   • XA：代表该存储引擎是否支持分布式事物。
   • Savepoints：代表该存储引擎是否支持事物的部分回滚。

2. 设置表的存储引擎

   # 显式指定表的存储引擎
   create table temp_table (
   	id int
   ) engine = MyISAM;
   
   # 修改表的存储引擎
   ALTER TABLE temp_table ENGINE = InnoDB;
   

二、字符集和比较规则

2.1 字符集和比较规则

众所周知，在计算机中数据是以二进制的形式存储的。字符集的出现解决了字符与二进制数据的映射关系。一个字符集包含了字符范围和相应的编码规则。

• 字符范围：要把哪些字符映射成二进制数。
• 编码规则：指具体怎么进行映射。包含编码、解码，其中使用不同字节数表示一个字符的编码方式称为变长编码方式。

在确定了字符集后，常见的操作是对两个字符进行比较。最简单的比较规则就是二进制比较规则，即：直接比较这两个字符对应的二进制编码的大小。

2.2 一些需要了解的字符集

不同的人说不同的语言，指定了不同的字符集。它们表示的范围和编码规则各不相同。

字符集	字符范围	编码方式	补充
ASCII	包含：空格、标点符号、数据、英文字母和一些不可见字符，共 128 个字符	使用 1 byte 进行编码	全称美国信息交换标准代码，是最通用的字符集
ISO 8859-1	ASCII 的基础上扩充了 128 个西欧常用字符，共 256 个字符	使用 1 byte 进行编码	别名 Latin 1
GB2312	兼容 ASCII。包含汉字、拉丁字母、希腊字母、日文字母、俄语西里尔字母。	采用边长编码方式（1 ~ 2 byte）。编码方式兼容 ASCII（在 ASCII 字符集中，采用 1byte 编码，否则采用 2 byte 编码）
GBK	兼容 GB2312，并进行了扩充	编码方式兼容 GB2312
Unicode		可采用 UTF-8（1~4 byte）、UTF-16（2 或 4 byte）、UTF-32（4 byte） 的编码方式进行编码	如果把各种文字编码形容为各地的方言，那么 Unicode 就是世界各国合作开发的一种语言。

2.3 字符集和比较规则在 MySQL 中的应用

2.3.1 相关操作

1. 查看 MySQL 支持的字符集和比较规则

   mysql> show charset;
   +----------+---------------------------------+---------------------+--------+
   | Charset  | Description                     | Default collation   | Maxlen |
   +----------+---------------------------------+---------------------+--------+
   | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
   | ascii    | US ASCII                        | ascii_general_ci    |      1 |
   | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
   | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
   | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
   | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
   | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
   | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
   ...
   +----------+---------------------------------+---------------------+--------+
   41 rows in set (0.01 sec)
   
   mysql> show charset like 'gb2312';
   +---------+---------------------------+-------------------+--------+
   | Charset | Description               | Default collation | Maxlen |
   +---------+---------------------------+-------------------+--------+
   | gb2312  | GB2312 Simplified Chinese | gb2312_chinese_ci |      2 |
   +---------+---------------------------+-------------------+--------+
   1 row in set (0.00 sec)
   

   每一个字符集都有相应的比较规则。在第一个查询结果中，只保留了一些需要记住的字符集⁵。

   需要注意的是，为了节约存储和提升性能，MySQL 中的 utf8 字符集是“阉割”过的字符集（只使用 1~3 byte，是 utf8mb3 的别名），utf8mb4 才是真正的 utf8 字符集。

2. 查看比较规则

   mysql> show collation;
   +--------------------------+----------+-----+---------+----------+---------+
   | Collation                | Charset  | Id  | Default | Compiled | Sortlen |
   +--------------------------+----------+-----+---------+----------+---------+
   | latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
   | latin1_swedish_ci        | latin1   |   8 | Yes     | Yes      |       1 |
   | latin1_danish_ci         | latin1   |  15 |         | Yes      |       1 |
   | latin1_german2_ci        | latin1   |  31 |         | Yes      |       2 |
   | latin1_bin               | latin1   |  47 |         | Yes      |       1 |
   | latin1_general_ci        | latin1   |  48 |         | Yes      |       1 |
   | latin1_general_cs        | latin1   |  49 |         | Yes      |       1 |
   | latin1_spanish_ci        | latin1   |  94 |         | Yes      |       1 |
   ...
   +--------------------------+----------+-----+---------+----------+---------+
   222 rows in set (0.01 sec)
   

   每一种字符集都有其默认的比较规则，比较规则的命名是有规律的 字符集名称_语言名称_采用的比较规则，其中，后缀解释如下：

   • _ai：accent insensitive，不分重音
   • _as：accent sensitive，分重音
   • _ci：case insensitive，不分大小写
   • _cs：case sensitive，分大小写
   • _bin：binary，使用二进制比较规则

3. 设置、修改 MySQL 的字符集和比较规则

   MySQL 中有 4 个级别的字符集和比较规则：服务器级别、数据库级别、表级别、列级别。

   • 服务器级别

     MySQL 提供了两个系统变量来对其进行控制。如下：

     mysql> show variables like '%server';
     +----------------------+-------------------+
     | Variable_name        | Value             |
     +----------------------+-------------------+
     | character_set_server | latin1            |
     | collation_server     | latin1_swedish_ci |
     +----------------------+-------------------+
     2 rows in set, 1 warning (0.02 sec)
     

     可以看出，服务器默认的字符集为 latin1、比较规则为 latin1_swedish_ci。

     可以通过修改配置文件中的启动选项或者在服务器运行过程中修改系统变量的值来改变该级别的字符集和比较规则。

   • 数据库级别

     可以在创建可修改数据库时设置该数据库的字符集和比较规则。

     # 创建数据库时指定
     CREATE DATABASE temp CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
     # 修改指定数据库的字符集和编码规则
     ALTER DATABASE temp CHARACTER SET utf8 COLLATE utf8_bin;
     # 查看指定数据库的字符集和编码规则
     mysql> USE temp;
     Database changed
     mysql> SHOW VARIABLES LIKE '%database';
     +------------------------+----------+
     | Variable_name          | Value    |
     +------------------------+----------+
     | character_set_database | utf8     |
     | collation_database     | utf8_bin |
     ...
     +------------------------+----------+
     3 rows in set, 1 warning (0.00 sec)
     

     需要注意的是，对于这两个系统变量只是用来供用户查询，不能通过修改这两个系统变量来改变当前数据库的字符集和比较规则。

   • 表级别和列级别

     # 创建表时指定
     CREATE TABLE `temp` (
       `id` int(11) DEFAULT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;
     # 修改指定表的字符集和编码规则
     ALTER TABLE `temp` CHARACTER SET = latin1, COLLATE = latin1_bin;
     
     # 创建表时指定列的字符集和比较规则
     CREATE TABLE `temp`.`Untitled`  (
       `code` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NULL,
       `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL
     );
     # 修改指定字段的字符集和编码规则
     ALTER TABLE `temp` MODIFY COLUMN `code` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin;
     

     需要注意的是，对于存储字符串的列，同一个表中可以有不同的字符集和比较规则。

   在显示指定字符集和比较规则时，默认指定上一级的比较字符集和比较规则。即：如果不指定列的字符集和比较规则则默认为表的字符集和比较规则；如果不指定表的，则默认为数据库的；如果不指定数据库的，则默认指定服务器的；服务器当然是有一个默认的字符集和比较规则啦。

2.3.2 注意事项

• 如果具体存储的数据与字符集不匹配时则会发生错误。如：往一个 ascii 字符集的列存储汉字、将存储汉字的列的字符集转换为 ascii 等。

• 由于字符集和比较规则之间相互关联，所以，当只修改其中一方时，另一方也会发生相应的变化。如：修改表的字符集为 ascii，则其比较规则会默认为 ascii_general_ci（ascii 默认的比较规则）； 修改列的比较规则为 ascii_bin 时，其字符集被默认修改为 ascii。

2.4 客户端和服务器通信过程中的字符集

从计算机的角度来看，客户端发起请求和服务器返回相应的本质就是一段段的字节序列。在这一来一回中，发生了多次字符集的转换。要搞清楚的是：

1. 客户端发送的请求字节序列是采用哪种字符集进行编码的；
2. 服务器接收请求的字节序列会认为它是使用哪种字符集进行编码的；
3. 服务器在运行过程中会把请求序列转换为哪种字符集；
4. 服务器在向客户端返回字节序列时采用哪种字符集进行编码；
5. 客户端在收到字节序列后，是怎么进行处理的。

从以下几个方面进行叙述：

• 客户端发送请求

  当使用类 UNIX 系统时，使用环境变量 LC_ALL、LC_TYPE、LANG 这 3 个环境变量决定当前操作系统使用哪种字符集；当使用 Windows 系统时，默认使用当前系统的字符集（多为 GBK），也可通过指定启动项 default-character-set 的值来指定客户端请求的字符串编码类型（仅 Windows 系统独有，如：mysql -uroot -p --default-character-set=utf8，算作是 Windows 改变字符集不方便的一种弥补吧...）。

• 服务器接收请求

  当服务器接收到客户端发送的请求字节序列时，服务器会将这个字节序列当作系统变量 character_set_client 代表的字符集进行编码的字节序列。如果 character_set_client 对应的字符集不能解释请求的字节序列，服务器就会发出警告。

• 服务器处理请求

  在真正处理请求时，服务器会将经过 character_set_client 转换后的字符序列再次使用 character_set_connection 代表的字符集进行转换。

  注意，这不是多此一举，有一部分原因是存储数据时，会对数据使用规定的字符集编码、存储，服务器收到请求要对数据进行操作时，如果二者字符集不一样，那首先要做的就是将其转换为同意的字符集后，再进行操作。如：使用 GBK 字符集的列 c，和客户端发送过来的使用 UTF-8 的字节序列，进行比较时，自然而然地要统一二者的字符集。

  由此，又产生了新的问题：列的字符集可能是在创表时指定的（是固定的），而 character_set_connection 代表的字符集可能是不同的（这一点在后面说到），在这种情况下，MySQL 大叔规定，列的优先级和排序规则更高。因此，在这种情况下，将二者的字符集统一转换为列的字符集后在进行操作。

• 服务器相应请求

  在服务器处理完请求要返回结果给客户端时，先将结果字节序列转换为 character_set_result 代表的字符集编码后的字符序列，然后发送给客户端。

• 客户端收到结果请求

  当使用类 UNIX 系统时，将收到的字节序列通过当前系统使用的字符集进行解释（如果 character_set_result 代表的字符集与当前系统的字符集不一样或者不兼容，就会发生乱码）；当使用 Windows 系统时，默认使用当前系统的字符集进行解释，如果在连接时使用了 default-character-set 显示指定客户端的字符串编码类型，则使用该类型解释接收的结果字节序列。

对于上述过程中提到的 3 个系统变量：character_set_client、character_set_connection 和character_set_result 的作用范围都是 SESSION 级别，也就意味着每个客户端与服务器建立连接后，服务器都会为该客户端维护这 3 个变量。

每个 MySQL 客户端都维护着一个客户端默认字符集，客户端在启动时会自动检测所在操作系统当前使用的字符集，并按照一定的规则映射成 MySQL 支持的字符集，然后将映射后的字符集作为客户端默认的字符集。通常的情况是，操作系统使用什么字符集，就映射为什么字符集。但也有一些特殊的情况：假如 MySQL 不支持操作系统当前使用的字符集，则会将客户端默认的字符集设置为 MySQL 的默认字符集。

在连接服务器时，客户端将字符集信息（默认或者指定）、用户名、密码等信息一起发送给服务器，服务器在接收到后，会将character_set_client、character_set_connection 和character_set_result 这 3 个系统变量的值初始化为客户端的默认字符集。

在客户机成功连接服务器后，也可以通过修改上述系统变量来达到修改字符集的作用。但要注意的是，这时修改系统变量并不会改变客户端在编码请求字节序列时使用的字符集，也不会修改客户端的默认字符集。

set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_result = utf8;
# 等效与上述 3 条语句
set names utf8;

# 查看
SHOW VARIABLES LIKE 'character_set_%';

Footnotes

1. 为了方便，把 MySQL 服务器处理请求的过程简单地划分为 service 层和存储引擎层：连管理、解析优化这些并不涉及真实数据存取的功能划分为 service 层；存取真实数据的功能划分为存储引擎层。 ↩

2. 线程分配过多会严重影响系统兴能，所以我们也要限制可以同时连接到服务器的客户端数量。 ↩

3. 虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销。从 MySQL 5.7.20 开始，不推荐使用查询缓存，在 MySQL 8.0 中直接将其删除。 ↩

4. MySQL 5.5.5 之后使用 InnoDB 作为默认存储引擎，之前使用 MyISAM。 ↩

5. MySQL 5.7 以及之前的版本中，MySQL 的默认字符集为 Latin 1。自 8.0 开始，uft8mb4 被优化，MySQL 默认的字符集改为 utf8mb4。 ↩