SQL基本命令

启动与连接

MySQL启动
启动MySQL：net start mysql
停止MySQL：net stop mysql
这里的mysql是服务的名称，每个人可以自定义，初始化的名称是mysql80
MySQL客户端连接
MySQL自带的客户端命令行
mysql -u username -p [-h hostname] [-P port]
-u username：用于指定用户名，比如：-u root
-p：用于提示输入密码
-h hostname：用于指定MySQL服务器的主机名或IP地址的选项，默认是localhost
-P port：指定MySQL服务器的端口号，默认端口号是3306

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾
SQL语句可以使用空格/缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
注释：
- 单行注释：– 注释内容或 # 注释内容(MySQL特有)
- 多行注释：/* 注释内容 */

数据类型

数值类型

类型	大小(byte)	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述	备注
`tinyint`	1	(-128，127)	(0，255)	小整数值
smallint	2	(-32768，32767)	(0，65535)	大整数值
mediumint	3	(-8388608，8388607)	(0，16777215)	大整数值
`int`	4	(-2147483648，2147483647)	(0，4294967295)	大整数值
`bigint`	8	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
float	4	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值	float(5,2)：5表示整个数字长度，2 表示小数位个数
double	8	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值	double(5,2)：5表示整个数字长度，2 表示小数位个数
`decimal`				小数值(精度更高)	decimal(5,2)：5表示整个数字长度，2 表示小数位个数

字符串类型

类型	大小	描述	备注
`char`	0-255 bytes	定长字符串	char(10): 最多只能存10个字符,不足10个字符,占用10个字符空间【性能高，浪费空间】
`varchar`	0-65535 bytes	变长字符串	varchar(10): 最多只能存10个字符,不足10个字符, 按照实际长度存储【性能低，节省空间】（实际上还会在数据库中单独开辟一个字符的空间，记录实际长度）
tinyblob	0-255 bytes	不超过255个字符的二进制数据
tinytext	0-255 bytes	短文本字符串
blob	0-65535 bytes	二进制形式的长文本数据
text	0-65535 bytes	长文本数据
mediumblob	0-16777215 bytes	二进制形式的中等长度文本数据
mediumtext	0-16777215 bytes	中等长度文本数据
longblob	0-4294967295 bytes	二进制形式的极大文本数据
longtext	0-4294967295 bytes	极大文本数据

日期时间类型

类型	大小(byte)	范围	格式	描述
`date`	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
year	1	1901 至 2155	YYYY	年份值
`datetime`	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

DDL(数据定义语言)

数据库操作

创建
- 创建数据库：create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则]
在 MySQL 中，当你使用特殊符号、关键字或空格来命名数据库时，需要使用反引号 ` 符号括起来，而不是单引号。单引号通常用于字符串值，而反引号用于标识数据库对象名称。
1
create database if not exists `数据库名`;
使用
- 使用数据库：use 数据库名
查询
- 查询所有数据库：show databases
- 查询当前数据库：select database()
修改数据库编码
- 修改某个数据库字符编码：alter database 数据库名 character set / charset 字符编码
1
alter database mydb1 character set utf8
修改数据库 mydb1 的编码为 utf8。注意，在 MySQL 中所有的 UTF-8 编码都
不能使用中间的"-"，即 UTF-8 要书写为 UTF8。
注意：数据库的字符集不建议设置为utf8，因为这里的utf8只能是3个字节的，而数据库中存在一些数据是4个字节的，最好设置为utf8mb4
删除
- 删除数据库：drop database [ if exists ] 数据库名

上述语法中的database，也可以替换成 schema，如：create schema db01;

表操作

创建

create table 表名(
    字段1 字段类型 [约束] [comment 字段1注释],
    ……
    字段n 字段类型 [约束] [comment 字段n注释]
)[comment 表标注];

查询
- 查询当前数据库所有表：show tables
- 查询表结构：desc 表名
- 查询创建表的SQL语句：show create table 表名
修改
- 添加字段：alter table 表名 add 字段名数据类型 [comment 注释]
- 修改字段的数据类型：alter table 表名 modify 字段名新数据类型 [约束] [comment 新字段注释] 【字段的约束和注释并不会被继承下来】
- 修改字段名和数据类型：alter table 表名 change 旧字段名新字段名新数据类型 [约束] [comment 新字段注释]【旧字段的约束和注释并不会被继承下来】
- 删除字段：alter table 表名 drop column 字段名
- 修改表名：rename table 表名 to 新表名
在修改字段名或数据类型的时候，若表中已经存在数据，修改成不能兼容原先数据的数据类型的话会报错。因此，在修改字段的数据类型时，你需要考虑到已有数据的情况，并确保新的数据类型与现有数据兼容。如果需要更改数据类型，最好先对现有数据进行备份或转换，然后再进行数据类型修改。
删除
- 删除表：drop table [ if exists ] 表名【在删除表时，表中的全部数据也会被删除】
- 删除并重建该表：truncate table 表名

DML(数据操作语言)

插入数据(INSERT)

指定字段添加数据：insert into 表名 (字段名1，字段名2……) values (值1，值2……)
全部字段添加数据：insert into 表名 values (值1，值2……)
批量添加数据( 指定字段 )：insert into 表名 (字段名1，字段名2……) values (值1，值2……)，(值1，值2……)
批量添加数据( 全部字段 )：insert into 表名 values (值1，值2……)，(值1，值2……)

注意事项：
插入数据时，指定的字段顺序需要与值的顺序一一对应。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

修改数据(UPDATE)

修改数据：update 表名 set 字段名1 = 值1，字段名2 = 值2，… [where 条件]

注意事项：
修改语句的条件可以有，也可以没有。如果没有条件，则会修改整张表的所有数据。

删除数据(DELETE)

删除数据：delete from 表名 [where 条件]

注意事项：
delete语句的条件可以有，也可以没有。如果没有条件，则会删除整张表的所有数据。
delete语句不能删除某一个字段的值（如果要操作，可以使用UPDATE，将该字段的值设置为NULL）

DQL(数据查询语言)

聚合函数

将一列数据作为一个整体，进行纵向计算。

常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：
select 聚合函数(字段列表) from 表名;
【注意：null值不参与所有聚合函数运算】

条件查询

语法：
select 字段列表 from 表名 where 条件列表;

条件：

比较运算符	功能	逻辑运算符	功能
>，>=，<，<=，=	大于、小于、等于	and 或 &&	并且(多条件同时成立)
<> 或 !=	不等于	or 或 \|\|	或者(多条件成立一个)
between…and…	在某个范围之内(闭区间)	not 或 !	非，不是
in(…，…)	在in之后的列表中的值，多选一
like ‘占位符’	模糊匹配( _匹配单个字符，%匹配任意个字符)
is null / is not null	是null / 不是null

分组查询

语法：
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件]

where 与 having 区别：
执行时机不同：where是分组之前进行过滤，不满足where条件的不参与分组；
而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以在后面跟上聚合函数进行判断
注意：
1.执行顺序：where > 聚合函数 > having
2.分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段无任何意义

排序查询

语法：
select 字段列表 from 表名 order by (字段1 排序方式1，字段2 排序方式2)

排序方式：
asc：升序(默认值)
desc：降序

排序类型：
1.数字排序：对数值类型的数据进行升序或降序排序
2.字符串排序：对字符类型的数据进行字典序的升序或降序排序
3.日期排序：对日期和时间类型的数据进行升序或降序排序
4.布尔值排序：对布尔类型的数据进行升序或降序排序（false被认为小于true）

注意：
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
当排序字段值相同时，默认情况下MySQL会按照记录在表中的物理存储顺序返回结果。

分页查询

语法：
select 字段列表 from 表名 limit 起始索引，查询记录数

注意：
起始索引从 0 开始，起始索引 = （查询页码 - 1）* 每页显示的记录数
分页查询是数据库的方言。不同的数据库有不同的实现，MySQL中是 limit
如果查询的是第一页，起始索引可以省略，直接简写为 limit 10

DCL(数据控制语言)

管理用户

查询用户
- select user ，host from mysql.user;
创建用户
- create user '用户名'@'主机名' identified by '密码';
修改用户密码
- alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
注意：
- 在MySQL 5.7及之前版本中，WITH mysql_native_password 可以省略，系统会默认使用 mysql_native_password 插件。
- 在MySQL 8.0及以上版本中，省略 WITH mysql_native_password 时，系统会默认使用 caching_sha2_password 插件。如果希望使用 mysql_native_password 插件，则需要显式指定。
删除用户
- drop user '用户名'@'主机名';

权限控制

查询权限
- show grants for '用户名'@'主机名';
授予权限
- grant 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
- revoke 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

常用权限

权限	说明
ALL、ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

SQL关键字的编写顺序

select
	字段列表
from
	表名列表
where 
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit 
	分页参数

SQL关键字的执行优先级

在 SQL 查询中，不同的子句按照特定的顺序执行：

FROM: 确定查询的数据来源，通常是表或子查询。
JOIN: 如果存在 JOIN，在此阶段将多个表连接成一个结果集，这是基于 ON 或 USING 子句中指定的条件。
ON/USING: 这是 JOIN 操作的一部分，用于定义表之间连接的条件。
WHERE: 根据 WHERE 子句的条件过滤记录，只有满足条件的记录才会被保留。
GROUP BY: 将结果集按照一个或多个列进行分组。
HAVING: 过滤分组后的结果，只有满足 HAVING 条件的分组才会被包含在最终结果中。
SELECT: 选择最终输出的列或计算表达式。注意，SELECT 通常在逻辑上认为是在 FROM 和 WHERE 之后，但在实际的查询执行计划中，它通常与 DISTINCT 和 ORDER BY 一起考虑。
DISTINCT: 如果存在，DISTINCT 会在所有行被处理之后，SELECT 之前，去除结果集中的重复行。
ORDER BY: 将结果集按照一个或多个列进行排序。
LIMIT: 在某些数据库系统中（如 MySQL），LIMIT 用于限制结果集的行数。在其他数据库系统中（如 SQL Server），可能使用 TOP 或 ROWNUM 等其他子句来实现类似的功能。

函数

函数是指一段可以直接被另一段程序直接调用的程序或代码

字符串函数

函数	功能
concat(S_1_， S_2_，…，S_n_)	字符串拼接，将S_1_， S_2_，…，S_n_拼接成一个字符串
lower(str)	将字符串str全部转为小写
upper(str)	将字符串str全部转为大写
lpad(str，n，pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str，n，pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str，start，len)	返回字符串str从start位置起的len长度的字符串【MySQL的起始索引是1，不是0】

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0~1内的随机小数
round(x,y)	求参数x的四舍五入的值，保留y位小数

日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date，interval expr type)	返回一个日期/时间值增加上一个时间间隔expr后的时间值
datediff(date1，date2)	返回起始时间date1和结束时间date2之间的天数【date1 - date2】

eg：
SELECT DATE_ADD('2024-07-26', INTERVAL 10 DAY) AS new_date;
类似day的时间单位还有：

second：秒
minute：分钟
hour：小时
week：周
month：月
year：年
quarter：季度
microsecond：微秒

流程函数

可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
if(value，t，f)	如果value为true，则返回 t，否则返回 f
ifnull(value1，value2)	如果value1部位空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end	如果val1为true，返回res1，…否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end	如果expr的值等于val1，返回res1，…否则返回default默认值

注意：
不带表达式的case语句可以使用比较运算符等，适用于更灵活的条件判断。
带表达式的case语句不可以使用比较运算符，只能用于具体值匹配。

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段值不能为null	`not null`
唯一约束	保证字段的所有数据都是唯一、不重复的	`unique`
主键约束	主键是一行数据的唯一标识，要求非空且唯一	`primary key`(常搭配`auto_increment`)
默认约束	保存数据时，如果未指定该字段值，则采用默认值(默认值可以用引号括起来)	`default`
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	`foreign key`
检查约束	确保列中的所有值满足特定条件	`check`

外键约束

语法：

create table 表名(
	字段1 字段类型 [约束] [comment 字段1注释],
    ……
    字段n 字段类型 [约束] [comment 字段n注释],
    [constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);

1	alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (主表列名)

注意： 主表是父表，包含外键约束的是子表。

删除/更新行为：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这里要求外键允许取null）
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值(Innodb/MySQL不支持)

注意：
删除和更新操作需要分别单独进行设置，可以根据具体的需求设置这两个选项。
eg：alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

一对多： 在多的一方设置外键，关联一的一方的主键
多对多： 建立中间表，中间表包含两个外键，关联两张表的主键
一对一： 用于表结构拆分，在其中任何一方设置外键(UNIQUE)，关联另一方的主键

多表查询

内连接
- 隐式： select … from 表A，表B where 条件 …
- 显式： select … from 表A [inner] join 表B on 条件 …
外连接
- 左外： select … from 表A left [outer] join 表B on 条件 …
- 右外： select … from 表A right [outer] join 表A on 条件 …
自连接： select … from 表A 别名1 ，表A 别名2 where 条件 …【也可以使用外连接】
子查询： 标量子查询、列子查询、行子查询、表子查询

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

1
2
3

select 字段列表 from 表A …
union [all]
select 字段列表 from 表B …

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all： 会将全部的数据直接合并在一起
union： 会对合并之后的数据去重

子查询

概念：SQL语句中嵌套select语句，称为嵌套语句 ，又称子查询
eg：select * from t1 where column1 = (select column1 form t2);
子查询外部的语句可以是 insert / update / delete / select 的任何一个。

标量子查询

子查询返回的结果是单个值 (数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询
常用的操作符：=、<>、>、>=、<、<=

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询被称为行子查询
常用的操作符：=、<>、in、not in

列子查询

子查询返回的结果是一列(可以是多行)，这种查询被称为列子查询
常用的操作符：in、not in、any、some、all

操作符	描述
in	在指定的集合范围内，多选一
not in	不在指定的集合范围之内
any	子查询返回列表中，有任意一个满足即可
some	与any等同，使用some的地方都可以使用any
all	子查询返回列表的所有值都必须满足

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符：in

事务

主要内容

事务简介：
事务是一组操作的集合，这组操作要么全部执行成功，要么全部执行失败。
事务操作：
- start transaction;：开启事务
- commit / rollback;：提交/回滚事务
事务四大特性(ACID)：
- 原子性(Atomicity)：原子性是指一个事务中的所有操作要么全部执行，要么全部不执行。
- 一致性(Consistency)：一致性确保事务在完成时，数据库从一个一致性状态转换到另一个一致性状态。事务的执行不会违反数据库的完整性约束。
- 隔离性(Isolation)：隔离性确保并发事务的执行不会相互干扰，每个事务的操作对其他事务是隔离的，中间状态对其他事务不可见。
- 持久性(Durability)：持久性确保一旦事务提交，其对数据库的修改将永久保存，即使系统发生故障，数据也不会丢失。
并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已经存在

事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√(会出现)	√	√
Read committed（Oracle默认）	×(不会出现)	√	√
Repeatable Read（MySQL默认）	×	×	√
Serializable	×	×	×

基本操作

开启事务
start transaction或begin
提交事务
commit
回滚事务
rollback
查看事务隔离级别
select @@TRANSACTION_ISOLATION;
设置事务隔离级别
set [session | global] transaction isolation level {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意： 花括号在写的时候不用加

MySQL体系结构

引擎

常用指令

在建表的时，指定存储引擎

create table 表名(
	字段1 字段1类型 [comment 字段1注释],
     …… ,
    字段n 字段n类型 [comment 字段n注释]
) engine = innodb [comment 表注释];

查看当前数据库支持的存储引擎

1	show engines;

InnoDB

介绍：
InnoDB是一种兼顾可靠性和高性能的通用存储引擎，在MySQL 5.5 之后，InnoDB是默认的MySQL存储引擎
特点：
- DML操作遵守ACID模型，支持事务；
- 行级锁，提高并发访问性能；
- 支持外键 foreign key 约束，保证数据的完整性和正确性；
文件：
xxx.ibd：xxx代表的是表名，InnoDB引擎的每一张表都会对应这样一个表空间文件，存储该表的结构(frm，sdi)、数据和索引。
- 参数设置：innodb_file_per_table【MySQL 5.6及以上默认值为’ON‘】
当’innodb_file_per_table’设置为’ON‘时，每一个InnoDB表的数据和索引都会储存在独立的表空间文件中，未必是在共享表空间文件(如’ibdata1‘)中。这些独立的表文件通常以.ibd为后缀,存储在数据库的目录中。
查看状态的SQL语句：show variables like 'innodb_file_per_table';
查看ibd文件的SQL语句：ibd2sdi 文件名.ibd;

MyISAM

介绍：
MyISAM是MySQL早期的默认存储引擎
特点：
- 不支持事务，不支持外键；
- 支持表锁，不支持行锁；
- 访问速度快；
文件：
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引

Memory

介绍：
Memory引擎的表数据存储在内存中，由于受到硬件问题，或断电问题的影响，只能将这些表作为临时表或缓存使用。
特点：
- 内存存放
- hash索引（默认）
文件：
xxx.sdi：存储表结构信息

不同引擎的区别与选择

InnoDB：存储业务系统中对事务、数据完整性要求较高的核心数据。
MyISAM：存储业务系统的非核心事务。

索引

概述

介绍：
索引(index)是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优缺点：

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行insert、update、delete时，效率降低。

结构

思考题

分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个·
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext

在Innodb存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚焦索引(Clustered Index)	将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据	必须有，且有且仅有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个非空唯一（not null unique）索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，Innodb会自动生成一个rowid作为隐藏的聚集索引

语法

创建索引
create [unique | fulltext ] index index_name on table_name (index_col_name,…);
查看索引
show index from table_name;
删除索引
drop index index_name on table_name;

性能分析

查看执行频次
show [session | global] status like 'Com_______';【一共7个下划线】
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启。
- 查看慢查询日志是否开启
  show variables like 'slow_query_log';
- 开启慢查询日志
  需要在MySQL的配置文件（ /etc/my.cnf ）中配置如下信息
1
2
3
4
# 开启MySQL慢查询日志开关
slow_query_log = 1
# 设置慢查询的时间为2s，SQL语句执行时间超过2s，就会视为慢查询，记录慢查询日志
long_query_time = 2
配置完毕后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log，开启慢查询后，会自动创建这个文件夹。

profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都消耗到哪里去了。

通过have_profiling参数，能够看到当前MySQL是否支持profile操作
select @@have_profiling;
默认profiling是关闭的，可以通过set语句在session / global级别开启profiling
set profiling = 1;
常用指令

# 查看每一条SQL的耗时基本情况
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况【query_id是在执行完show profiles会提供的】
show profile for query query_id;

# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划
explain / desc 命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
1
2
# 直接在select语句之间加上关键字 explain / desc
explain select 字段列表 from 表名 where 条件;
- id：select查询的序列号，表示查询中执行select子句或者操作表的顺序( id相同，执行顺序从上到下；id不同，值越大，越先执行)。
- select_type：表示select的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含了子查询）等。
- type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all。
- possible_key：显示可能应用在这张表上的索引，可以是一个或多个。
- key：实际使用到的索引，如果为NULL，表示没有使用索引。
- key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
- rows：MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的。
- filtered：表示返回结果的行数占需要读取行数的百分比，filtered的值越大越好，最大等于100。

使用原则

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则，查询从最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

范围查询

联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效

explain select * from tb_user where profession='软件工程' and age>30 and status='0';

# 注意：>=、<=不会失效
explain select * from tb_user where profession='软件工程' and age>=30 and status='0';【不会失效】

索引失效的情况

索引列运算：不要在索引列上进行运算操作，索引将失效
字符串不加引号：字符串类型字段使用时不加引号，索引将失效
模糊查询：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
or连接的条件：用or分割开的条件，如果or前的条件的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引

SQL提示

# use index:(建议使用)
explain select * from tb_name use index(idx_user_pro) where profession='软件工程';

# ignore index:(忽略索引/不使用)
explain select * from tb_name ignore index(idx_user_pro) where profession='软件工程';

# force index:(强制使用)
explain select * from tb_name force index(idx_user_pro) where profession='软件工程';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在索引中已经全部能够找到，减少select *

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只将字符的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：
create index idx_xxxx on table_name(column(n));
前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性时1，这是最好的索引选择性，性能也是最好的。
1
2
3
select count(distinct email) / count(*) from tb_user;

select count(distinct substring(email,1,5)) / count(*) from tb_user;

单列索引与联合索引

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。

【在业务场景中，如果存在多个查询条件时，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引】

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。
也就是说当你只查询一个条件时，也有可能是用到联合索引。

设计原则

小结

SQL优化

insert优化

批量插入
insert into table_name values (值1，值2……)，(值1，值2……);
手动提交事务

start transaction;
insert into table_name values(值1，值2……)，(值1，值2……),……;
insert into table_name values(值1，值2……)，(值1，值2……),……;
insert into table_name values(值1，值2……)，(值1，值2……),……;
commit;

主键顺序插入

1 2	# 主键乱序插入：8 1 9 21 88 2 4 15 速度慢 # 主键顺序插入：1 2 4 8 9 15 21 88 速度更快

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '数据文件路径' into table `tb_name` fields terminated by '区分一行小数据的符号' lines terminated by '区分每行大数据的符号';

# 查看local_infile状态的语句
select @@local_infile;

主键优化

页分裂

主键顺序插入的话并不会发生页分裂的问题。

主键乱序插入的情况

页合并

页分裂(合并)的危害
- 页分裂和合并涉及大量的数据移动和重组操作。频繁进行这些操作会追加数据库的 I/O 负担和CPU消耗，从而影响数据库的整体性能。
- 分裂和合并可能会导致 B+树索引结构频繁地进行调整，这会影响插入和删除操作的性能。
- 频繁的页分裂和合并还可能导致磁盘上存在较多的空间碎片。新分出的数据页通常会有大量的空闲空间，则会导致数据库表占用更多的磁盘空间，造成资源浪费。

如何避免分页

主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。
- 尽量不要使用UUID做主键或者其它自然主键，如身份证号。
- 业务操作时，避免对主键的修改。

order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引，避免回表。
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)是否和排序规则一致。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认值256k）

group by优化

在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。

# 假设存在索引 idx_user_pro_age

select age, count(*) from tb_user group by age;  
# 不满足最左前缀法则，使用的是临时表

select age, count(*) from tb_user where profession = '软件工程' group by age;
# 满足最左前缀法则，使用的是索引

limit优化

当搜索是limit 20000000，10时，此时需要MySQL排序前20000010记录，仅仅返回20000000 - 20000010的记录，其它记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过建立 覆盖索引 能够比较好的提高性能，可以通过覆盖索引 加 子查询形式进行优化

1	explain select * from tb_sku t , (select id from tb_sku order id limit 2000000,10) a where t.id = a.id;

count优化

count( )是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值
用法：count(*)、count(主键)、count(字段)、count(1)
- count(主键)
  InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行累加（主键不可能为null）。
- count(字段)
  没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务端，服务层判断是否为null，不为null，计数累加。
  有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
- count(1)
  InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个字”1“进去，直接按行进行累加。
  【括号里的也可以是2，3，4……不一定是1】
- count(*)
  InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
按效率排序：
count(字段) < count(主键 id) < count(1) ≈ count(*)，尽量使用 count(*)

1	explain select count(*) from tb_user;

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
InnoDB引擎就比较麻烦，它执行count(*)的时候，需要把数据一个一个地从引擎里面读取出来，然后累积计数。
优化思路：自己计数。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

小结

视图

概念

视图(View) 是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作在于创建这条SQL查询语句上。

基本语法

创建

1	create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded \| local] check option];

如果不定义 列名列表，那么视图中的列名将默认采用 SELECT 语句中所选择的列名。这意味着视图中的每一列将继承 SELECT 语句中的列名。

如果不定义cascaded | local且写了with check option,默认是with cascaded check option

什么都不写
在执行插入、更新、删除等操作时并没有条件约束。
cascaded check option
创建视图时加上了cascaded check option,在执行插入、更新、删除等操作时会递归所有底层视图的约束条件，确保每一层次的条件都满足。
local check option
local check option只检查直接视图的定义条件，而不会检查底层视图的约束条件。
如果视图是基于其它视图创建的
会去查看基于的视图是否被[with[cascaded | local] check option]定义。

查询

# 查看创建视图语句
show create view 视图名称;
# 查看视图数据
select * from 视图名称 ……;

修改

# 方式一
create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option];
# 方式二
alter view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option];

删除

1	drop view [if exists] 视图名称 [,视图名称] …;

更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则视图不可更新：

聚合函数或窗口函数：sum( )、min( )、max( )、count( )等
distinct
group by
having
union 或者 union all

这里的“更新”包括插入操作、更新操作和删除操作。具体来说，当视图包含聚合函数、DISTINCT、GROUP BY、HAVING 或 UNION（UNION ALL）时，视图不仅不可更新，还不可进行插入和删除操作。这是因为这些操作都要求视图中的行能够直接映射回基础表中的行，而上述项使得这种映射无法实现。
为什么不能进行插入操作？
视图是基于基础表定义的虚拟表，它并不实际存储数据，而是动态地从基础表中检索和计算数据。因此，通过视图进行的插入操作实际上需要插入到基础表中。如果视图包含聚合函数、GROUP BY、HAVING 等特性，视图中的数据与基础表中的具体行并不直接一一对应，这导致插入操作无法匹配并插入到基础表中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE sales (
    id INT PRIMARY KEY,
    salesperson_id INT,
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (id, salesperson_id, sales_amount) VALUES
(1, 101, 500.00),
(2, 102, 300.00),
(3, 101, 700.00),
(4, 103, 200.00);

CREATE VIEW sales_summary AS
SELECT salesperson_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY salesperson_id;

INSERT INTO sales_summary (salesperson_id, total_sales) VALUES (104, 400.00);
# 报错：The target table sales_summary of the INSERT is not insertable-into

作用

简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作指定全部的条件。
安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能看到的数据
数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

概念

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

特点

封装、复用
可以接受参数，也可以返回数据
减少网络交流、效率提升

基本语法

创建

create procedure 存储过程名称([参数列表])
begin
	--SQL语句
end;

调用

1	call 名称([参数]);

查看

select * from information_schema.routines where routine_schema='xxx';
# 查询指定数据库的存储过程及状态信息

show create procedure 存储过程名称;
# 查询某个存储过程的定义

删除

1	drop procedure [if exists] 存储过程名称;

注意：
在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。
比如：delimiter $$; ，表明以$$结尾来作为结束语句，默认是;

变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global）、会话变量（session）

查看系统变量

# 查看所有系统变量
show [session | global] variables;

# 可以通过like模糊匹配方式查找变量
show [session | global] variables like '……';

# 查看指定变量的值
select @@[session. | global.]系统变量名;

设置系统变量

1 2	set [session \| global] 系统变量名 = 值; set @@[session. \| global.]系统变量名 = 值;

注意：
如果没有指定 session | global，默认是session，会话变量。
MySQL服务重启之后，所设置的全局参数会失效，想要不失效，可以在 /etc/my.cnf 中配置

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接通过@变量名使用就可以。其作用域为当前连接。

赋值

set @var_name = expr [, @var_name = expr] ……;
set @var_name := expr [, @var_name := expr] ……;
更推荐使用 := , 因为在SQL语言中的等于用的就是 = ,为了让赋值和比较逻辑中的等于区分开来。

select @var_name := expr [, @var_name := expr] ……;
select 字段名 into @var_name from 表名;

使用

1	select @var_name;

注意：
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
1
2
select @abc; -- 事先没有定义过 @abc
# 输出的值为NULL;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内部声明的begin … end块。

声明

1	declare 变量名变量类型 [default …];

变量类型就是数据库字段类型：int、bigint、double、char、varchar、date、time等。

赋值

1
2
3

set 变量名 = 值;
set 变量名 := 值；
select 字段名 into 变量名 from 表名…;

用户定义变量 VS 局部变量

特性	用户定义变量	局部变量
作用范围	会话级别	begin…end 块内
声明方式	无需显式声明，以‘@’符号开头	通过’declare’语句声明
生命周期	会话期间	begin…end 块内
使用场景	跨多个语句使用	存储过程、函数或触发器内
数据类型	自动确定	必须指定数据类型

用户定义变量
- 可以在存储过程中使用，但不推荐在复杂的SQL查询中频繁使用。
局部变量
- 仅在存储过程、函数或触发器中使用，生命周期受限于它们的作用范围。
- 更适合在存储过程或函数内部处理复杂的逻辑。

参数

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

语法：

create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
begin
	--SQL语句
end;

判断语句

if

语法：

if 条件1 then ……
elseif 条件2 then ……	 	-- 可选
else ……		-- 可选
end if;

case

语法1（填准确值）：

case case_value
	when when_value1 then statement_list1
	[when when_value2 then statement_list2]
	……
	[else statement_list]
end case;

语法2（填表达式）：

case
	when search_condition1 then statement_list1
	[when search_condition2 then statement_list2]
	……
	[else statement_list]
end case;

循环语句

while

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。

# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
	SQL逻辑…
	……
end while;

repeat

repeat是有条件的循环2控制语句，当满足条件的时候推出循环。【相当于do…while语句，先执行一次再判断条件】

repeat
	SQL逻辑……
	until 条件
end repeat;

loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环。loop可以配合以下两个语句使用：

leave：配合循环使用，退出循环。
iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

1
2
3

[begin_label:] loop
	SQL逻辑…
end loop [end_label];

1
2
3

leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环
# 搭配if then 语句使用

eg：

create procedure p1(in n int)
begin
	declare total int default 0;
	
	sum: loop
		if n<=0 then leave sum;
		end if;
	end loop sum;
	
end;

游标

游标（cursor）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明，open、fetch和close。

声明游标

1	declare 游标名称 cursor for 查询语句;

打开游标
1
open 游标名称;

获取游标记录

1	fetch 游标名称 into 变量[,变量];

关闭游标
1
close 游标名称;

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

1	declare handler_action handler for condition_value [,condition_value]… statement;

handler_action
- **continue：**继续执行当前程序
- **exit：**终止执行当前程序
condition_value
- **sqlstate sqlstate_value：**状态码，sqlstate_value可以是02000等
- **sqlwarning：**所有以01开头的sqlstate代码的简写
- **not found：**所有以02开头的sqlstate代码的简写
- **sqlexception：**所有没有被 sqlwarning 或 not found 捕获的sqlstate代码的简写

状态码的查询网址：https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

create procedure p2(in uage int)
begin 
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age <= uage;
	declare exit handler for sqlstate '02000' close u_cursor;
	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
    	id int primary key auto_increment,
        name varchar(100),
    	profession varchar(100)
    );
    
    open u_cursor;
    while true do
    	fetch u_cursor into uname,upro;
    	insert into tb_user_pro values (null,uname,upro);
    end while;
    
    close u_cursor;
end;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是 in 类型的。

create function 存储函数的名称([参数列表])
return type [characteristic…]
begin
	…SQL语句
	return…;
end;

type
- int、bigint、double、char、varchar、date、time等。
characteristic
- determinstic：相同的输入参数总是产生相同的结果
- no sql：不含sql语句
- reads sql data：包含读取数据的语句，但不包含写入数据的语句

可以用存储函数的场景都可以使用存储过程。

触发器

介绍
触发器是与表有关的数据库对象，指在 insert / update / delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名 old 和 new 来引用触发器中发生变化的记录内容，这与其它的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW和OLD
insert型触发器	new表示将要或者已经新增的数据
update型触发器	old表示修改之前的数据，new表示将要或已经修改后的数据
delete型触发器	old表示将要或者已经删除的数据

语法

创建

create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row -- 行级触发器
begin
	trigger_stmt;
end;

查看
1
show triggers;

删除

1	drop trigger [schema_name.]trigger_name;-- 如果没有指定schema_name,默认为当前数据库

eg：

create trigger tb_user_update_trigger
	after update on tb_user for each row
begin
	insert into user_logs(id,operation,operate_time,operate_id,operate_params) values
	(null,'insert',now(),new.id,
     concat('插入的数据内容为：id=',new.id,',name=',new.name));
 end;

锁

概念

锁是计算机协调多个进程或者线程并发访问某一资源的机制。在数据库中，除传统计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

MySQL中的锁，按照锁的颗粒度分为以下三类：

全局锁：锁定数据库的所有表。
表级锁：每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据。

全局锁

概念

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。【DQL查询语句可以继续执行】

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

语法

# Step 1：锁定数据库
flush tables with read lock;

# Step 2：打开另一个终端窗口，执行备份[在终端中执行，而不是在MySQL窗口中]
mysqldump -u -p 数据库名 > 备份文件.sql

# Step 3：返回MySQL会话，解锁数据库
unlock tables;

# 在一个窗口中执行的情况---------------------------------------

# Step 1: 登录MySQL并启用全局锁
mysql -u 用户名 -p -e "FLUSH TABLES WITH READ LOCK;"

# Step 2: 在同一个终端中执行备份操作
mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

# Step 3: 解除全局锁
mysql -u 用户名 -p -e "UNLOCK TABLES;"

在InnoDB引擎中，我们可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份
1
mysqldump --single-transaction -u 用户名 -p  数据库名 > 备份文件.sql
备份过程中不会锁定表，因此可以最大限度地减少对数据库的干扰。这对于高并发、需要持续写操作的数据库尤为重要。但在高负载环境中，长时间的备份可能会增加I/O负担，影响数据库性能。

缺点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

如果在主库上备份，那么在备份期间不能执行更新，业务基本上就得停摆。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

表级锁

概念

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InooDB、BDB等存储引擎中。

表级锁主要分为以下三类：

表锁
元数据锁（meta data lock，MDL）
意向锁

表锁

表共享读锁（read lock）：不会阻塞其它客户端的读，但是会阻塞写。
表独占写锁（write lock）：既阻塞其它客户端的读，有阻塞其它客户端的写。

语法：

# 加锁
lock tables 表名… read/write;

# 释放锁
unlock tables; / 客户端断开连接

元数据锁（meta data lock，MDL）

MDL加锁过程是系统自动控制，无需显式使用。在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。

对应SQL	锁类型	说明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select、select … lock in share mode	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert、update、delete、select … for update	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table ……	EXCLUSIVE	与其它的MDL都互斥

查看元数据锁

1	select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

意向锁的主要作用是实现多粒度锁定（即表级和行级锁定）的高效管理。通过在表级别使用意向锁，InnoDB可以快速确定某个事务是否可以安全地在行级别加锁，而不需要检查表中的每一行是否已被其他事务锁定。

意向共享锁（IS）：由语句 select … lock in share mode 添加。
- 与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
意向排他锁（IX）：由insert、update、delete、select … for update添加。
- 与表锁共享锁（read）及排他锁（write）都互斥。意向锁之间不会互斥。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

1	select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

概念

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其它事务对此行进行update和delete。在RC、RR隔离级别下都支持。
间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录，开区间），确保索引记录间隙不变，防止其它事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下支持。

行锁

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排他锁。

SQL语句的行锁类型：

SQL	行锁类型	说明
insert ……	排他锁	自动加锁
update ……	排他锁	自动加锁
delete ……	排他锁	自动加锁
select（正常）	不加任何锁
select……lock in share mode	共享锁	需要手动在select之后加lock in share mode
select……for update	排他锁	需要手动在select之后加for update

默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。
针对唯一索引进行检验索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

间隙锁/临键锁

默认情况下，InnoDB 在 repeatable read 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止。

注意：
间隙锁唯一目的是防止其它事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

小结

全局锁
- 对整个数据库实例加锁，加锁后整个实例就处于只读状态
- 性能较差，数据逻辑备份时使用
表级锁
- 操作锁住整张表，锁定颗粒度大，发生锁冲突的概率高
- 表锁，元数据锁，意向锁
行级锁
- 操作锁住对应的行数据，锁定颗粒度最小，发生锁冲突的概率最低
- 行锁，间隙锁，临键锁

InnoDB引擎

逻辑存储结构

架构

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存结构

Change Buffer的意义是什么？
与聚焦索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了Change Buffer之后，我们可以在缓冲池中合并处理，减少磁盘IO。【针对的是非唯一的二级索引页】

Buffer Pool 和 Change Buffer 的主要区别：

用途不同：Buffer Pool 用于缓存所有类型的数据页和索引页，而 Change Buffer 仅用于缓存非唯一二级索引的修改。
缓存内容：Buffer Pool 缓存的是实际的数据页和索引页，Change Buffer 缓存的是对非唯一二级索引的变更操作。
写入时机：Buffer Pool 中的脏页会在适当的时候异步写回磁盘，而 Change Buffer中的变更会在索引页被访问或后台定期合并时写入磁盘。

Change Buffer缓存的是对应的变更操作

变更信息结构通常包括：

索引页的Page ID：指示要修改的二级索引页在磁盘上的位置。
变更类型：插入（insert）、更新（update）、删除（delete）
索引键值：要变更的索引项的具体键值。
变更的具体数据：包括插入的新索引项、更新后的索引项或删除的索引项。

eg：
假设我们有一个表employees，其非唯一二级索引idx_salary记录了员工的工资信息。
现在插入一条新记录：
1
insert into employees (id,name,salary) values (1,'Alice',5000);
这条插入操作在 Change Buffer 中的变更信息可能类似如下：
Page ID：1234 （假设索引页ID为1234）
变更类型：Insert
索引键值：（salary=5000，id=1）
变更数据：将（salary=5000，id=1）插入索引页
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
{
    "page_id": 1234,
    "change_type": "insert",
    "index_key": {
        "salary": 5000,
        "id": 1
    },
    "change_data": {
        "action": "insert",
        "key": (5000, 1),
        "value": [相关数据]
    }
}

// page_id: 这是需要变更的二级索引页在磁盘上的位置。
// change_type: 表示变更的类型，在这个例子中是插入操作。
// index_key: 要变更的索引项的键值，表示salary和id。
// change_data: 具体的变更数据，这里是插入操作，将键值(salary=5000, id=1)插入索引页。

磁盘结构

后台线程

事务原理

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（DUrability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

redo log(持久性)

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

undo log(原子性)

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC(多版本并发控制)。
undo log 和 redo log记录物理日志不一样，它记录的是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行 roll back 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

undo log的销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
undo log的存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 roll back segment 回滚段中，内部包含1024个undo log segment。

MVCC

概念

当前读
读取的是记录的最新版本，读取时还要保证其它并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是一种当前读。
快照读
简单的select（不加锁）就是快照读，快照读读取的是记录数据的可见版本，有可能是历史数据。不加锁，是非阻塞读。
- Read Committed：每次select，都生成一个快照读。
- Repeatable Read：开启事务后第一个select语句才是快照读的地方。
- Serializable：快照读会退化为当前读。
MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

隐藏字段

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PIR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

undo log 版本链

readView

readView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

readView中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合（所有未提交的事务集合）
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	readView创建者的事务ID

日志

错误日志

错误日志是MySQL中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log 。查看日志位置的SQL语句：

1	show variables like '%log_error%'

二进制日志

介绍
二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。
作用：
```
1. 灾难时的数据恢复；
1. MySQL的主从复制，在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：
```
1
show variables like '%log_bin%';

日志格式

日志格式	含义
STATEMENT	基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW	基于行的日志记录，记录的是每一行的数据变更。（默认）
MIXED	混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。

1
2
3

show variables like '%binlog_format%'; -- 查看

set global/session binlog_format = 'ROW'/'STATEMENT'/……;

日志查看

ROW格式下查看的日志格式：

STATEMENT格式下查看的日志格式：

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量的磁盘空间。可以通过以下几种方式清理日志：

指令	含义
reset master	删除全部binlog日志，删除之后，日志编号将从binlog.000001重新开始。
purge master logs to ‘binlog.******‘	删除 ***** 编号之前的所有日志，不包含该日志。
purge master logs before ‘yyyy-mm-dd hh:mi:ss’	删除日志为”yyyy-mm-dd hh:mi:ss”之前产生的所有日志，这里的hh是24小时制的。

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

1	show variables like '%binlog_expire_logs_seconds%'; -- 默认设置是30天

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：

1 2	# 查看查询日志的开启状态 show variables like '%general%';

修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容：

# 该选项用来开启查询日志，可选值：0或者1；0代表关闭，1代表开启
general_log = 1
# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file = mysql_query.log

文件内容示图：

慢查询

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为10秒，最小为0，精度可以准确到微妙。

# 在/etc/my.cnf配置文件中添加如下内容
# 开启慢查询日志
slow_query_log = 1
# 执行时间参数
long_query_time = 2 -- 设置成2s

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用 log_slow_admin_statements 和log_queries_not_using_indexes 更改此行为。

# 记录执行较慢的管理语句
log_slow_admin_statements = 1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1

查询示图

主从复制

概述

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其它从服务器的主库，实现链状复制。

MySQL 的主从复制（Replication）是一种用于将数据从一个 MySQL 服务器（主服务器）复制到一个或多个 MySQL 服务器（从服务器）的机制。主从复制的主体是整个服务器，而不是单独的数据库或表。

原理

服务器准备

主库配置

修改配置文件 /etc/my.cnf

# mysql服务ID，保证整个集群环境中唯一，取值范围：1 ~ 2^32 -1,默认值为1
server-id = 1
# 是否只读，1代表只读，0代表读写
read-only = 0
# 忽略的数据，指不需要同步的数据库
# binlog-ignore-db = mysql
# 指定同步的数据库
#binlog-do-db=db01

重启MySQL服务器

1	systemctl restart mysqld -- 针对Linux而言

登录MySQL，创建远程连接的账号，并赋予主从复制的权限

# 创建itcast用户，并设置密码，该用户可以在任意主机连接该MySQL服务
create user 'itcast'@'%' indentified with mysql_native_password by 'Root@123456';
# 为'itcast'@'%'用户分配主从复制权限
grant replication slave on *.* to 'itcast'@'%';

通过指令，查看二进制日志坐标

1	show master status;

字段含义说明：

file：从哪个日志文件开始推送日志文件
position：从哪个位置开始推送日志
binlog_ignore_db：指定不需要同步的数据库

从库配置

修改配置文件 /etc/my.cnf

# mysql服务ID，保证在整个集群环境中唯一，取值范围：1 ~ 2^32 -1，和主库不一样即可
server-id = 2
#是否只读，1代表只读，0代表读写
read-only = 1

重启MySQL服务

1	systemctl restart mysqld; -- 针对Linux而言

登录MySQL，设置主库配置

# 8.0.23之后的语法
change replication source to source_host='xxx.xxx', source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

# 8.0.23之前的语法
change master source to master_host='xxx.xxx', master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;

开启同步操作

1 2	start replica; # 8.0.22之后 start slave; # 8.0.22之前

查看主从同步状态

1 2	show replica status; # 8.0.22之后 show slave status; # 8.0.22之前

分库分表

问题分析

拆分方式

垂直拆分

水平拆分

实现技术

Mycat

概念

Mycat是开源的、活跃的，基于Java语言编写的MySQL数据库中间件。可以像使用MySQL一样来使用Mycat，对于开发人员来说根本感觉不到Mycat的存在。【Mycat使用了MySQL的伪装协议】

优势：

性能可靠稳定
强大的技术团队
体系完善
社区活跃

结构示图：

启动与连接

启动服务
切换到Mycat的安装目录，执行如下指令，启动Mycat：
1
2
3
4
# 启动
bin/mycat start
# 停止
bin/mycat stop
Mycat启动之后，占用端口号 8066

连接Mycat

1	mysql -h 主机号 -P 8066[端口号] -u 用户名 -p;

配置

schema.xml

schema.xml作为Mycat中最重要的配置文件之一，涵盖了Mycat的逻辑库、逻辑表、分片规则、分片节点及数据源的配置。

主要包含以下三组标签：

schema标签
datanode标签
datahost标签

schema标签

schema标签用于定义Mycat实例中的逻辑库，一个Mycat实例中，可以有多个逻辑库，可以通过schema标签来划分不同的数据库。

Mycat中逻辑库的概念，等同于MySQL中的database概念，需要操作某个逻辑库下的表时，也需要切换逻辑库（use xxx）。

核心属性：

**name：**指定自定义的逻辑库库名
**checkSQLschema：**在SQL语句操作时指定了数据库名，执行时是否自动去除；true：自动去除，false：不自动去除
**sqlMaxLimit：**如果未指定limit进行查询，列表查询模式最多查询多少条记录

schema标签(table)

table标签定义了Mycat中数据库schema下的逻辑表，所有需要拆分的表都需要在table标签中的定义。

**name：**自定义逻辑表名，在该逻辑库下唯一
**dataNode：**定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
**rule：**分片规则的名字，分片规则名字是在rule.xml中定义的
**primaryKey：**逻辑表对应真实表的主键
**type：**逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global

dataNode标签

dataNode标签中定义了Mycat中的数据节点，也就是我们通常说的数据分片。一个dataNode标签就是一个独立的数据节点。

核心属性：

**name：**定义数据节点名称
**dataHost：**数据库实例主机名称，引用自 dataHost 标签中的name属性
**database：**定义分片所属数据库

dataHost标签

该标签在Mycat逻辑库中作为底层标签存在，直接定义了具体的数据库实例、读写分离、心跳语句。

核心属性：

**name：**唯一标识，供上层标签使用
**maxCon/minCon：**最大连接数/最小连接数
**balance：**负载均衡策略，取值0，1，2，3
**writeType：**写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二个；1：写操作随机分发到配置的writeHost）
**dbDriver：**数据库驱动，支持native、jdbc

小结：

rule.xml

rule.xml中定义所有拆分表的规则，在使用过程中可以灵活的使用分片算法，或者对同一个分片算法使用不同的参数，它让分片过程可配置化。主要包含两类标签：tableRule、Function。

server.xml

server.xml配置文件包含了Mycat的系统配置信息，主要有两个重要的标签：system、user。

system标签

对应的系统配置项及其含义参考下列表格：

属性	取值	含义
charset	utf8	设置Mycat的字符集, 字符集需要与MySQL的字符集保持一致
nonePasswordLogin	0,1	0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户
useHandshakeV10	0,1	使用该选项主要的目的是为了能够兼容高版本的jdbc驱动, 是否采用HandshakeV10Packet来与client进行通信, 1:是, 0:否
useSqlStat	0,1	开启SQL实时统计, 1 为开启 , 0 为关闭 ; 开启之后, MyCat会自动统计SQL语句的执行情况 ; mysql -h 127.0.0.1 -P 9066 -u root -p 查看MyCat执行的SQL, 执行效率比较低的SQL , SQL的整体执行情况、读写比例等 ; show @@sql ; show @@sql.slow ; show @@sql.sum ;
useGlobleTableCheck	0,1	是否开启全局表的一致性检测。1为开启，0为关闭。
sqlExecuteTimeout	1000	SQL语句执行的超时时间 , 单位为 s ;
sequnceHandlerType	0,1,2	用来指定Mycat全局序列类型，0 为本地文件，1 为数据库方式，2 为时间戳列方式，默认使用本地文件方式，文件方式主要用于测试
sequnceHandlerPattern	正则表达式	必须带有MYCATSEQ或者 mycatseq进入序列匹配流程注意MYCATSEQ_有空格的情况
subqueryRelationshipCheck	true,false	子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false
useCompression	0,1	开启mysql压缩协议 , 0 : 关闭, 1 : 开启
fakeMySQLVersion	5.5,5.6	设置模拟的MySQL版本号
defaultSqlParser		由于MyCat的最初版本使用了FoundationDB的SQL解析器, 在MyCat1.3后增加了Druid解析器, 所以要设置defaultSqlParser属性来指定默认的解析器; 解析器有两个 : druidparser 和 fdbparser, 在MyCat1.4之后,默认是druidparser, fdbparser已经废除了
processors	1,2….	指定系统可用的线程数量, 默认值为CPU核心 x 每个核心运行线程数量; processors 会影响processorBufferPool, processorBufferLocalPercent, processorExecutor属性, 所有, 在性能调优时, 可以适当地修改processors值
processorBufferChunk		指定每次分配Socket Direct Buffer默认值为4096字节, 也会影响BufferPool长度, 如果一次性获取字节过多而导致buffer不够用, 则会出现警告, 可以调大该值
processorExecutor		指定NIOProcessor上共享 businessExecutor固定线程池的大小; MyCat把异步任务交给 businessExecutor线程池中, 在新版本的MyCat中这个连接池使用频次不高, 可以适当地把该值调小
packetHeaderSize		指定MySQL协议中的报文头长度, 默认4个字节
maxPacketSize		指定MySQL协议可以携带的数据最大大小, 默认值为16M
idleTimeout	30	指定连接的空闲时间的超时长度;如果超时,将关闭资源并回收, 默认30分钟
txIsolation	1,2,3,4	初始化前端连接的事务隔离级别,默认为 REPEATED_READ , 对应数字为3 READ_UNCOMMITED=1; READ_COMMITTED=2; REPEATED_READ=3; SERIALIZABLE=4;
sqlExecuteTimeout	300	执行SQL的超时时间, 如果SQL语句执行超时,将关闭连接; 默认300秒;
serverPort	8066	定义MyCat的使用端口, 默认8066
managerPort	9066	定义MyCat的管理端口, 默认9066

user标签

分片规则

范围分片

去模分片

一致性hash

枚举分片

应用指定

固定分片hash算法

字符串hash解析

按（天）日期分片

自然月

原理

管理

管理工具

Mycat-eye

Mycat-web（Mycat-eye）是对Mycat-server提供监控服务，功能不局限于对Mycat-server使用。他通过JDBC连接对Mycat、MySQL监控，监控远程服务（目前仅局限于Linux系统）的CPU、内存、网络、磁盘。

Mycat-eye运行过程中需要依赖zookeeper。

读写分离

读写分离，简单地说就是把数据库的读和写操作分开，以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。

通过Mycat即可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。

一主一从读写分离

双主双从读写分离

双主双重介绍

双主双重的搭建

双主双从的读写分离

其它

单引号 vs. 双引号

在 SQL 中，字符串通常是用单引号 (') 包围起来的，这是标准的 SQL 规范。然而，不同的数据库管理系统（如 MySQL、PostgreSQL、SQL Server 等）可能有各自的一些特殊规则和扩展，允许在某些情况下使用双引号，但通常是用于其他目的。以下是一些常见的情况：

标准 SQL：
- 字符串必须用单引号包围。例如：
  1
  SELECT * FROM users WHERE name = 'John';
双引号的用途：
- 在标准 SQL 中，双引号 (") 通常用于标识符（如表名、列名）而非字符串。例如：
  1
  SELECT "column_name" FROM "table_name";
- 双引号可以用于表示区分大小写的标识符（例如 PostgreSQL 中的表或列名）。
使用双引号表示字符串：
- 某些数据库（如 MySQL）可能允许你在 SQL 模式中启用 ANSI_QUOTES 选项，从而将双引号用于字符串。这种情况下，双引号和单引号的作用类似，但这种用法并不符合标准 SQL 规范：
  1
  2
  SET SESSION sql_mode = 'ANSI_QUOTES';
  SELECT * FROM users WHERE name = "John"; -- 这种用法在启用了 ANSI_QUOTES 模式后有效
数据库特定的字符串分隔符：
- 某些数据库还支持自定义分隔符，比如 PostgreSQL 支持用 E'...' 处理带有转义字符的字符串，或用 $$... $$ 表示大文本块（如函数体）。

因此，在标准 SQL 和大多数数据库的默认配置下，字符串应当使用单引号包围。如果需要使用双引号，必须确认数据库的特定配置或扩展支持这一功能。一般情况下，为了确保跨数据库的兼容性，使用单引号是最安全的做法。

Lusy

MySQL基础

SQL基本命令

启动与连接

SQL通用语法

数据类型

DDL(数据定义语言)

数据库操作

表操作

DML(数据操作语言)

插入数据(INSERT)

修改数据(UPDATE)

删除数据(DELETE)

DQL(数据查询语言)

聚合函数

条件查询

分组查询

排序查询

分页查询

DCL(数据控制语言)

管理用户

权限控制

常用权限

SQL关键字的编写顺序

SQL关键字的执行优先级

函数

字符串函数

数值函数

日期函数

流程函数

约束

外键约束

多表查询

多表关系

多表查询

联合查询

子查询

标量子查询

行子查询

列子查询

表子查询

事务

主要内容

基本操作

MySQL体系结构

引擎

常用指令

InnoDB

MyISAM

Memory

不同引擎的区别与选择

索引

概述

结构

分类

语法

性能分析

使用原则

最左前缀法则

范围查询

索引失效的情况

SQL提示

覆盖索引

前缀索引

单列索引与联合索引

设计原则

小结

SQL优化

insert优化

主键优化

order by优化

group by优化

limit优化

count优化

update优化

小结

视图

概念

基本语法

更新