[MySQL] 基础知识

giao_magic
232 阅读11分钟

使用由行和列组成的表来存放数据的关系型数据库管理系统有好多种，比方说甲骨文的 Oracle，IBM 的 DB2，微软的 SQL Server，开源的 PostgreSQL 和 MySQL 等等。

MySQL 的优势：免费、开源、跨平台等。

MySQL 基础

macOS 操作系统上的安装目录：/usr/local/mysql/，Windows 操作系统上的安装目录：C:\Program Files\MySQL\MySQL Server 5.7。

类 UNIX 操作系统非常多，比如 FreeBSD、Linux、macOS、Solaris 等都属于 UNIX 操作系统的范畴。命令行指的是类 UNIX 系统中的 Shell 或者 Windows 系统中的 cmd.exe。

启用/停止服务端程序

// Windows - 以服务的形式运行
net start MySQL57
net stop MySQL57

// UNIX
mysql.server start
mysql.server stop
mysql.server restart

每个进程都有一个唯一的编号 PID，启动的 MySQL 服务器进程的默认名称为 mysqld，而 MySQL 客户端进程的默认名称为 mysql。

启用/退出客户端程序

mysql -h主机名 -u用户名 -p

quit
exit
\q

服务器处理客户端请求

image.png

连接管理

在客户端程序发起连接的时候，需要携带主机信息、用户名、密码，服务器程序会对客户端程序提供的这些信息进行认证，如果认证失败，服务器程序会拒绝连接。另外，如果客户端程序和服务器程序不运行在一台计算机上，还可以采用使用了 SSL（安全套接字）的网络连接进行通信，来保证数据传输的安全性。

TCP/IP：在网络环境下，每台计算机都有一个唯一的 IP 地址，如果某个进程有需要采用 TCP 协议进行网络通信方面的需求，可以向操作系统申请一个端口号，这是一个整数值，它的取值范围是 0~65535。

命名管道和共享内存：如果在 Windows 上，那么客户端进程和服务器进程之间可以考虑使用命名管道或共享内存进行通信。

Unix 域套接字文件：如果服务器进程和客户端进程都运行在同一台操作系统为类 Unix 的机器上的话，可以使用 Unix 域套接字文件来进行进程间通信。

解析与优化

查询缓存：把历史处理过的查询请求和结果缓存起来，查询缓存可以在不同客户端之间共享。缓存不会命中的场景：

  • 两个查询请求在任何字符上的不同（例如：空格、注释、大小写）。
  • 查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql、information_schema、performance_schema 数据库中的表，那这个请求就不会被缓存。 MySQL 的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从 MySQL 5.7.20 开始，不推荐使用查询缓存，并在 MySQL 8.0 中删除。

语法解析：MySQL 服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到 MySQL 服务器内部使用的一些数据结构。

查询优化：这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是怎样的，可以使用EXPLAIN关键字来查看某个语句的执行计划。

存储引擎：连接管理、解析与优化这些并不涉及真实数据存储的功能划分为 MySQL server，把真实存取数据的功能划分为存储引擎。

存储引擎描述
ARCHIVE用于数据存档（行被插入后不能再修改）
BLACKHOLE丢弃写操作，读操作会返回空内容
CSV在存储数据时，以逗号分隔各个数据项
FEDERATED用来访问远程表
InnoDB具备外键支持功能的事务存储引擎
MEMORY置于内存的表
MERGE用来管理多个 MyISAM 表构成的表集合
MyISAM主要的非事务处理存储引擎
NDBMySQL 集群专用存储引擎

启动选项和系统变量

查看系统变量

SHOW VARIABLES [LIKE 匹配的模式];

在命令行上使用选项

在命令行中设置启动选项只对当次启动生效。

--启动选项1[=值1] --启动选项2[=值2] ... --启动选项n[=值n]
mysqld --skip-networking
mysqld --default-storage-engine=MyISAM

配置文件

[server]
default-storage-engine=MyISAM
max-connections=10

image.png

image.png

设置不同作用范围的系统变量

语句一：SET GLOBAL default_storage_engine = MyISAM;
语句二：SET @@GLOBAL.default_storage_engine = MyISAM;

语句一：SET SESSION default_storage_engine = MyISAM;
语句二：SET @@SESSION.default_storage_engine = MyISAM;
语句三：SET default_storage_engine = MyISAM;

如果某个客户端改变了某个系统变量在 GLOBAL 作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为 SESSION 的值，只会影响后续连入的客户端在作用范围为 SESSION 的值。

查看不同作用范围的系统变量

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

查看状态变量

SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

字符集和比较规则

计算机中只能存储二进制数据，建立字符与二进制数据的映射关系来存储字符串。

抽象出一个字符集的概念来描述某个字符范围的编码规则，采用 1 个字节编码一个字符的形式，字符和字节的映射关系如下：

'a' -> 00000001 (十六进制：0x01)
'b' -> 00000010 (十六进制：0x02)
'A' -> 00000011 (十六进制：0x03)
'B' -> 00000100 (十六进制：0x04)

'bA' -> 0000001000000011 (十六进制：0x0203)
'baB' -> 000000100000000100000100 (十六进制：0x020104)
'cd' -> 无法表示，字符集不包含字符'c'和'd'

一些重要的字符集

ASCII 字符集

共收录 128 个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才 128 个字符，所以可以使用 1 个字节来进行编码：

'L' -> 01001100（十六进制：0x4C，十进制：76)
'M' -> 01001101（十六进制：0x4D，十进制：77)

ISO 8859-1 字符集

共收录 256 个字符，是在 ASCII 字符集的基础上又扩充了 128 个西欧常用字符（包括德法两国的字母），也可以使用 1 个字节来进行编码，别名为 latin1。

GB2312 字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字 6763 个，其他文字符号 682 个。同时这种字符集又兼容 ASCII 字符集：

  • 如果该字符在 ASCII 字符集中，则采用 1 字节编码。
  • 否则采用 2 字节编码。

此种编码方式又称变长编码方式，比方说字符串爱u，其中爱需要用 2 个字节进行编码，编码后的十六进制表示为0xB0AE，u需要用 1 个字节进行编码，编码后的十六进制表示为0x75，所以拼合起来就是0xB0AE75。

怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？因为ASCII字符集只收录 128 个字符，所以如果某个字节是在 0～127 之内的，就意味着一个字节代表一个单独的字符。

GBK 字符集

GBK 字符集只是在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312。

utf8 字符集

收录能想到的所有字符（不断扩充），这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用 1～4 个字节。

'L' -> 01001100 (十六进制：0x4C)
'啊' -> 111001011001010110001010 (十六进制：0xE5958A)

其实准确的说，utf8 只是 Unicode 字符集的一种编码方案，Unicode 字符集可以采用 utf8、utf16、utf32 这几种编码方案，utf8 使用 1～4 个字节编码一个字符，utf16 使用 2 个或 4 个字节编码一个字符，utf32 使用 4 个字节编码一个字符。

MySQL 中支持的字符集和排序规则

MySQL 中的 utf8 和 utf8mb4

MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，在 MySQL 中 utf8 是 utf8mb3 的别名，比如存储一些 emoji 表情之类的，请使用 utf8mb4。

  • utf8mb3：阉割过的 utf8 字符集，只使用 1～3 个字节表示字符。
  • utf8mb4：正宗的 utf8 字符集，使用 1～4 个字节表示字符。

字符集查看

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
字符集名称Maxlen
ascii1
latin11
gb23122
gbk2
utf83
utf8mb44

比较规则的查看

SHOW COLLATION [LIKE 匹配的模式];

比较规则的命名的规律如下：

  • 比较规则名称以与其关联的字符集的名称开头，如以 utf8 开头的。
  • 后边紧跟着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较，utf8_general_ci 是一种通用的比较规则。
  • 名称后缀意味着该比较规则是否区分语言中的重音、大小写等。
后缀英文释义描述
_aiaccent insensitive不区分重音
_asaccent sensitive区分重音
_cicase insensitive不区分大小写
_cscase sensitive区分大小写
_binaccent sensitive以二进制方式比较

字符集和比较规则的应用

  • 服务器级别 
    [server]
character_set_server=gbk
collation_server=gbk_chinese_ci
  • 数据库级别 
    CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
  • 表级别 
    CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]
  • 列级别（CHARACTER SET、COLLATE） 
    CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

    在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。

仅修改字符集或仅修改比较规则

  • 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
  • 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

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

我在 utf8 字符集编码下的字节串长这样：0xE68891，假设使用的是 gbk 字符集来解释这串字节，解码过程就是这样的：

  • 首先看第一个字节0xE6，它的值大于0x7F（十进制：127），说明是两字节编码，继续读一字节后是0xE688，然后从 gbk 编码表中查找字节为 0xE688 对应的字符，发现是字符 鎴。
  • 继续读一个字节 0x91，它的值也大于 0x7F，再往后读一个字节发现没有了，所以这是半个字符。
  • 所以 0xE68891 被 gbk 字符集解释成一个字符 鎴 和半个字符。

MySQL 中字符集的转换

从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到 3 个系统变量。

系统变量描述
character_set_client服务器解码请求时使用的字符集
character_set_connection服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用 character_set_connection 代表的字符集进行编码
character_set_results服务器向客户端返回数据时使用的字符集

image.png

SET NAMES 字符集名;

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

[client]
default-character-set=字符集名
avatar
文章
阅读
粉丝