MYSQL 8.0

MYSQL 8.0

前言

• 本笔记根据《MySQL 8 DBA基础教程》整理而来，图书在版编目（CIP）数据：MySQL 8 DBA基础教程／孙泽军，刘华贞编著．—北京：清华大学出版社，2020.5
• 如有侵权，联系必删
• 本笔记纯粹是为了学习使用，切勿用作他途
• 由于本人水平有限，如有错误，可在评论指出，互相学习，不吝赐教

安装与配置

1.官网免安装下载

2.在windows安装，配置mysql的bin目录到环境变量PATH

3.切换到mysql的bin目录，执行命令进行安装

mysqld install

4.执行命令进行初始化

mysqld --initialize

5.在mysql的data目录找到err日志，其中有初始化的密码

6.使用命令启动mysql服务，需使用管理员权限

net start mysql

7.登录mysql

mysql -u root -p

8.登录成功后修改密码

alter user user() identified by 'root'

存储引擎

MySQL 8支持9种存储引擎，分别为MEMORY、MRG_MYISAM、CSV、FEDERATED、PERFORMANCE_SCHEMA、MyISAM、InnoDB、BLACKHOLE和ARCHIVE

使用命令查看引擎

查看所有引擎

show engines

查看存储引擎的状态

show engine engine_name status

查看默认的存储引擎

show variables like 'default_storage_engine'

修改默认存储引擎

1.修改my.ini文件中的default-storage-engine的值

2.使用sql命令进行修改

set default_storage_engine=MyISAM

InnoDB存储引擎

• InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全
• MySQL从3.23.34a开始就包含InnoDB存储引擎
• InnoDB是MySQL第一个提供外键约束的表引擎，而且InnoDB对事务处理的能力是MySQL的其他存储引擎所无法比拟的
• InnoDB存储引擎支持自动增长列AUTO_INCREMENT
• InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写效率稍差，占用的数据空间相对比较大

MyISAM存储引擎

• MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默认存储引擎，MySQL 5.6版本之后，除系统数据库之外，默认的存储引擎由MyISAM改为InnoDB
• MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM增加了很多有用的扩展
• MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同，扩展名包括frm、MYD和MYI。其中，frm为扩展名的文件存储表的结构；MYD为扩展名的文件存储数据，其是MYData的缩写；MYI为扩展名的文件存储索引，其是MYIndex的缩写
• 基于MyISAM存储引擎的表支持3种存储格式，包括静态型、动态型和压缩型。其中，静态型为MyISAM存储引擎的默认存储格式，其字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要使用myisampack工具创建，占用的磁盘空间较小
• MyISAM存储引擎的优点在于占用空间小，处理速度快；缺点是不支持事务的完整性和并发性

MEMORY存储引擎

• MEMORY存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表，而且所有数据都放在内存中
• 每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm，该文件中只存储表的结构，而其数据文件都存储在内存中。这样有利于对数据的快速处理，提高整个表的处理效率
• MEMORY存储引擎默认使用哈希（HASH）索引，其速度要比使用B型树（BTREE）索引快
• MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。因此，由于其存在于内存中的特性，这类表的处理速度非常快。但是，其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心

存储引擎对比

• InnoDB存储引擎支持事务处理，支持外键，同时支持崩溃修复能力和并发控制。如果对事务的完整性要求比较高，要求实现并发控制，那么选择InnoDB存储引擎有很大的优势。需要频繁地进行更新、删除操作的数据库也可以选择InnoDB存储引擎，因为这类存储引擎可以实现事务的提交（Commit）和回滚（Rollback）
• MyISAM存储引擎出入数据快，空间和内存使用比较低。如果表主要用于插入新记录和读出记录，那么选择MyISAM存储引擎能实现处理的高效率。如果应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎
• MEMORY存储引擎的所有数据都在内存中，数据的处理速度快，但安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，就可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求，不能建立太大的表。所以，这类数据库使用相对较小的数据库表

数据库操作

数据库创建

create database mydb

选择数据库

use mydb

删除数据库

drop database mydb

查看数据库

show databases

表操作

设计

• 第一范式（1NF），确保每列保持原子性：数据库的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项
• 第二范式（2NF），确保每列都和主键相关：要满足第二范式（2NF）必须先满足第一范式（1NF），第二范式要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与元实体之间是一对多的关系
• 第三范式（3NF），确保每列都和主键列直接相关，而不是间接相关：要满足第三范式（3NF）必须先满足第二范式，要求一个关系中不包含已在其他关系中包含的非主关键字信息

创建表

create table t_class(
	classno int,
    cname varchar(20),
    loc varchar(40),
    stucount int
);

查看表

查看数据库中的表

show tables [from schema_name like 'pattern']

查看表信息

describe t_class

desc t_class

查看表创建语句

show create table t_class

删除表

drop table t_class

修改表

修改表名

alter table t_class rename tab_class

增加字段

alter table t_class add advisor varchar(20)

增加字段，在表第一个位置增加

alter table t_class add advisor varchar(20) first

增加字段，在表的指定字段之后增加

alter table t_class add advisor varchar(20) after cname

删除字段

alter table t_class drop cname

修改字段，修改数据类型

alter table t_class modify classno varchar(20)

修改字段，修改字段名称

alter table t_class change classno classid int

修改字段，同时修改字段名称和类型

alter table t_class change classno classid varchar(40)

修改字段，修改字段顺序

alter table t_class modify classno int after cname

操作表的约束

• PRIMARY KEY：标识该属性为该表的主键，可以唯一的标识对应的元祖
• FOREIGN KEY：标识该属性为该表的外键，是与之联系的某表的主键
• NOT NULL：标识该属性不能为空
• UNIQUE：标识该属性的值是唯一的
• AUTO_INCREMENT：标识该属性的值自动增加，这是MySQL语句的特色
• DEFAULT：为该属性设置默认值

建表时增加约束

create table tabname1(
    propName1 propType1,
	propName2 propType2,
    constraint fk_prop foreign key(propName1)
    references tabname2(propName2_1)
)

数据操作

在MySQL软件中，关于数据的操作（CRUD）包含插入数据记录（CREATE）、查询数据记录（READ）、更新数据记录（UPDATE）和删除数据记录（DELETE）

插入

插入完整数据记录

insert into tablename values(value1,value2,value3)

插入部分数据记录

insert into tablename(field1,field2,field3) values(value1,value2,value3)

插入多条数据记录

insert into tablename(field1,field2,field3) values(value11,value12,value13),
(value21,value22,value23)

插入查询结果

insert into tablename(field1,field2,field3) select (field1,field2,field3) from tablename2 [where condition]

更新

更新特定记录

update tablename set field1 = value1,field2 = value2,field3 = value3 [where condition]

删除

删除特定的记录

delete from tablename [where condition]

MySQL数据类型

整形类型

浮点数类型和定点数类型

FLOAT、DOUBLE类型存储数据时存储的是近似值，DECIMAL类型存储的是字符串，因此提供了更高的精度。在金融系统中，表示货币金额的时候会优先考虑DECIMAL类型，一般的价格体系中，比如购物平台中货品的标价一般选择FLOAT类型就可以。

日期和时间类型

字符串类型

CHAR和VARCHAR

• CHAR类型的长度是固定的，在创建表时就指定了，其长度可以是0~255的任意值
• VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间

TEXT类型

ENUM类型

• ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式指定了
• ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值
• 如果ENUM类型加上了NOT NULL属性，其默认值就为取值列表的第一个元素；如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值

field_name ENUM('value1','value2',..,'valuen')

SET类型

• 在创建表时，SET类型的取值范围就以列表的形式指定了
• 其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除
• SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开
• SET类型的值最多只能是由64个元素构成的组合

field_name SET('value1','value2',...,'valuen')

二进制类型

运算符

算数运算符

比较运算符

REGEXP表达式

逻辑运算符

位运算符

运算符的优先级

单表查询

基础语法

select field1,field2,...,fieldn
from tablename
[where condition]
[group by fieldm [having condition2]]
[order by fieldn [asc|desc]]

去重查询

select distinct field1,field2 from tablename

连接字符串

select concat(field1,',',field2) from tablename

带like的模糊查询

排序

• 如果存在一条记录字段的值为空（NULL），按升序排序时，这条记录将显示为第一条记录，因为按升序排序时，含空值的记录将最先显示，可以理解为空值是该字段的最小值，而按降序排序时，字段为空值的记录将最后显示

统计函数

• COUNT()函数：实现统计表中记录的条数。COUNT(*)使用方式：这种方式可以实现对表中的记录进行统计，无论表字段中包含的是NULL值还是非NULL值，COUNT(field)使用方式：这种方式可以实现对指定字段的记录进行统计，在具体统计时将忽略NULL值
• AVG()函数：实现计算字段值的平均值。SUM()函数：实现计算字段值的总和。MAX()函数：实现查询字段值的最大值。MIN()函数：实现查询字段值的最小值

分组

分组一般语法

select function(field) from table name 
where condition 
group by field1,field2...fieldn 
having condition

LIMIT限制

LIMIT一般语法

select field1,field2 from tablename where condition limit offset_start,row_count

• 其中参数OFFSET_START表示数据记录的起始偏移量，参数ROW_COUNT表示显示的行数
• 如果不指定初始位置，默认值就为0

正则表达式

MySQL使用关键字regexp指定正则表达式的字符匹配模式

多表查询

关系数据操作

• 并（union）：并就是把具有相同字段数目和字段类型的表合并到一起
• 笛卡尔积：笛卡尔积就是没有连接条件的表关系返回的结果

内连接查询

在MySQL中可以通过两种语法形式来实现连接查询：一种是在FROM子句中利用逗号区分多个表，在WHERE子句中通过逻辑表达式来实现匹配条件，从而实现表的连接，这是早期MySQL连接的语法形式；另一种是ANSI连接语法形式，在FROM子句中使用“JOIN…ON”关键字，连接条件写在关键字ON子句中。推荐使用ANSI语法形式的连接

select field1,field2 from tablename1 [inner] join tablename2 on condition

• 自连接：指表与其自身进行连接
• 等值连接：是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件
• 不等连接：是在关键字ON后的匹配条件中除了等于关系运算符来实现不等条件外，还可以使用包含“>”“>=”“<”“<=”和“!=”等关系运算符

外连接

外连接查询会返回所操作的表中至少一个表的所有数据记录

select field1,field2 from tablename1 left|right|full [outer] join tablename2 on condition

• 左外连接：左外连接就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表，如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值

• 右外连接：右外连接是指新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表就返回空值

• 全外连接：全连接是指新关系中执行匹配条件时，将取左连接的结果与右连接的结果合并取并集。MySQL不支持全外连接，但是可以通过左连接和右连接拼凑出全外连接的结果

复合条件连接查询

复合条件连接查询中，通过添加过滤条件限制查询的结果，使查询的结果更加准确

合并查询数据记录

合并查询基本语法

select field1,field2,field3 from tablename1 union|union all select field1,field2,field3 from tablename2

• union：合并结果集，同时去掉了重复的数据记录，使新关系中没有任何重复的数据记录
• union all：合并结果集后的数据记录没有去掉重复数据，而是把两个表中的所有数据合并显示

子查询

使用多表进行关联查询时首先会进行笛卡尔积的操作，然后选择匹配条件的数据。进行笛卡尔积数据操作时，会产生两个表数据量乘积条数量的数据记录，如果两个表的数据记录比较大，在进行笛卡尔积的时候容易出现宕机，此时可以使用子查询进行优化。

子查询，是指在一个查询中嵌套了其他若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句称为子查询，也被称为嵌套查询

索引

• 索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式

• 在MySQL中，所有的数据类型都可以被索引，这些索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等

• 索引有两种存储类型，即B型树（BTREE）索引和哈希（HASH）索引。InnoDB和MyISAM存储引擎支持BTREE索引；MEMOY存储引擎支持HASH索引和BTREE索引，默认为前者

• 索引的优点：是可以提高检索数据的速度，这是创建索引的主要原因；对于有依赖关系的子表和父表之间的联合查询，可以提高查询速度；使用分组和排序子句进行数据查询时，可以显著节省查询中分组和排序的时间

• 索引的缺点：创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低

普通索引

在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引后，查询时可以通过索引进行查询

唯一性索引

在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引后，查询时可以通过索引进行查询

全文索引

使用参数FULLTEXT可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引

单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引，只要保证该索引只对应一个字段即可

多列索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询

空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值

隐藏索引

使用参数INVISIBLE可以创建隐藏索引。隐藏索引不会被优化器使用，可以用来测试去掉索引对查询性能的影响

降序索引

创建降序索引的SQL语句与创建多列索引的语法相同，使用DESC关键字标识索引为降序索引，主要应用于多列索引中不同字段排序方式不同的场景，这对查询效率的提高意义重大

索引的设计原则

• 选择唯一性索引：唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录

• 为经常需要排序、分组和联合操作的字段建立索引：经常需要进行ORDER BY、GROUP BY、DISTINCT和UNINON等操作的字段，排序操作会浪费很多时间，如果为其建立索引，就可以有效地加快排序操作

• 为常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引可以提高整个表的查询速度

• 限制索引的数目：索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦

• 尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多

• 尽量使用前缀来索引：如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段进行全文检索会很浪费时间，如果只检索字段前面的若干字符，就可以提高检索速度

• 删除不再使用或者很少使用的索引：表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。删除时可以利用隐藏索引的功能先将索引隐藏，确定这些索引对性能无影响后，再将其删除

创建和查看索引

在创建表时直接创建

create table tablename
(propname1 type1,
 propname2 type2,
 ...
 propnamen typen,
 [unique|fulltext|spatial] index|key
 [indexname](propname1 [(length)] [asc|desc]) [invisible]
);

在已经存在的表上创建

create [unique|fulltext|spatial] index indexname on table (propname [(length)] [asc|desc]) [invisible]

通过alter table语句创建

alter table tablename add index|key indexname (propname [(length)] [asc|desc]) [invisible]

show index查看索引

show index from table_name

show keys from table_name

删除索引

drop index语法

drop index indexname on tablename

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户可以不用看到整个数据库表中的数据，而只需要关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性

视图的特点

• 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系
• 视图是由基本表（实表）产生的表（虚表）
• 视图的建立和删除不影响基本表
• 对视图内容的更新（添加、删除和修改）直接影响基本表
• 当视图来自多个基本表时，不允许添加和删除数据

创建视图

基本语法

create [or replace][algorithm=[undefined|merge|template]] view viewname
[columnlist] as select statement [with [cascaded|local] check option]

• ALGORITHM的取值有3个，分别是UNDEFINED、MERGE和TEMPTABLE。UNDEFINED表示MySQL将自动选择算法：MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；TEMPTABLE表示将视图的结果存入临时表，然后用临时表来执行语句
• CASCADED与LOCAL为可选参数：CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可

查看视图

查看视图基本信息

desc|describe viewname

查看所有视图和表

show tables;

查找相关视图，并查看状态信息

show table status [from dbname] [like 'pattern']

查看创建视图的语句

show create view|table viewname

在views表中查看视图的详细信息

select * from information_schema.views where table_name='viewname'

修改视图

使用创建视图的语法进行修改

create [or replace][algorithm=[undefined|merge|template]] view viewname
[columnlist] as select statement [with [cascaded|local] check option]

使用alter语法进行修改

alter [algorithm={undefined|merge|termplate}] view viewname [{columnlist}]
as select_statement [with [cascaded|local] check option]

更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，通过视图更新时，都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据，超出了范围就不能更新

更新基本表，视图自动更新

不可更新视图

• 视图中包含SUM()、COUNT()、MAX()和MIN()等函数
• 视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字
• 常量视图
• 视图的SELECT中包含子查询
• 由不可更新的视图导出的视图
• 创建时视图时，ALGORITHM为TEMPTABLE类型的
• 视图对应的表存在没有默认值的列，而且该列没有包含在视图里
• WITH[CASCADED|LOCAL]CHECK OPTION也将决定视图能否更新

删除视图

删除视图语法形式

drop view viewname1,viewname2,...viewnamen

存储过程与函数

创建存储过程

create procedure procedure_name([proc_parm[,...]])
[characteristic ...] routine_body

• 参数procedure_name表示所要创建的存储过程的名字，参数proc_param表示存储过程的参数，参数characteristic表示存储过程的特性，参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束
• proc_param中每个参数的语法形式如下

[in|out|inout] parm_name type

• 参数charateristic指定存储过程的特性：LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。[NOT]DETERMINISTIC：指明存储过程执行的结果是否正确。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，就默认为NOT DETERMINISTIC。
• {CONTAINS SQL | NOSQL | READS SQL DATA | MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。CONTAINS SQL表名子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。
• SQL SECURITY{DEFINER | INVOKER}：指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
• COMMENT‘string’：注释信息，可以用来描述存储过程或函数

具体sql语句

delimiter $$
create procedure proc_cart()
comment '存储过程'
begin
select * from shoppingcart;
end;
$$
delimiter ;

MySQL中默认的语句结束符为分号（;）。存储过程中的SQL语句需要分号来结束。为了避免冲突，首先用“DELIMITER$$”将MySQL的结束符设置为$$，然后用“DELIMITER;”来将结束符恢复成分号

创建存储函数

创建存储函数基本语法

create function func_name([func_parm[,...]])
returns type
option
[characteristic...] routine_body

• 在上述语句中，参数func_name表示所要创建的函数的名字；参数func_param表示函数的参数；参数characteristic表示函数的特性，该参数的取值与存储过程中的取值相同；参数routine_body表示函数的SQL语句代码，可以用BEGIN…END来表示SQL语句的开始和结束

delimiter $$
create function func_cart(input_name varchar(30))
returns double
READS SQL DATA
begin
return (select price from shoppingcart where name = input_name);
end;
$$
delimiter ;

• 创建函数时，如果报错ERROR 1418 (HY000)则说明开启了bin-log就必须为function指定一个参数：DETERMINISTIC 不确定的、NO SQL 没有SQl语句，当然也不会修改数据、READS SQL DATA 只是读取数据，当然也不会修改数据、MODIFIES SQL DATA 要修改数据、CONTAINS SQL 包含了SQL语句。或者修改参数SET GLOBAL log_bin_trust_function_creators = 1;

存储过程、存储函数、触发器等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同

定义变量

declare var_name[,...] type [default value]

为变量赋值

set var_name=expr[,var_name=expr]...

定义条件

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止

declare condition_name condition for 
sqlstate[value] sqlstate_value|mysql_err_code

例如，定义ERROR 1146(42S02)这个错误，名称为can_not_find，可以用两种不同的方法来定义

/* 方法1: */
declare can_not_find condition for sqlstate '42S02';
/* 方法2: */
declare can_not_find condition for 1146

定义处理程序

declare handler_type handler for condition_value[,...] proc_statement

• 其中，参数handler_type指明错误的处理方式，该参数有3个取值，分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式
• condition_value表示错误类型，可以有以下取值：SQLSTATE[VALUE]sqlstate_value：包含5个字符的字符串错误值；condition_name：表示DECLARE CONDITION定义的错误条件名称；SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；mysql_error_code：匹配数值类型错误代码
• 参数proc_statement为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数

通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作

定义处理程序的几种方式

/* 方法一：捕获sqlstate_value */
declare continue handler for sqlstate '42S02' set @info='NOT FOUND';
/* 方法二：使用mysql_error_code */
declare	continue handler for 1146 set @info='NOT FOUND';
/* 方法三：先定义条件，然后调用 */
declare not_found condition for 1146;
declare continue handler for not_found set @info='NOT FOUND';
/* 方法四：使用sqlwarning */
declare exit handler for sqlwarning set @info='ERROR';
/* 方法五：使用NOT FOUND*/
declare exit handler for NOT FOUND set @info='NOT FOUND';
/* 方法六：使用sqlexception */
declare exit handler for sqlexception set @info='ERROR';

光标使用

• 查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。有些书上将光标称为游标。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后

光标操作语法

/* 声明光标 */
declare cursor_name CURSOR
for select_statement;
/* 打开光标 */
open cursor_name;
/* 使用光标 */
fetch cursor_name into var_name[,var_name...];
/* 关闭光标 */
close cursor_name;

if条件语句

if srarch_condition then statement_list
[elseif search_condition then statement_list]...
[else statement_list]
end if

case条件语句

case case_value when when_value then statement_list
[when_value then statement_list]...
[else statement_list]
end case

loop语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有停止循环，必须遇到LEAVE语句等才能停止循环

[begin_label:] loop
statement_list
end loop [end_label]

leave语句

leave语句主要用于跳出循环控制

level label

iterate语句

ITERATE语句也是用来跳出循环的语句，但是ITERATE语句是跳出本次循环，然后直接进入下一次循环

interate label

repeat语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句

[begin_label:]repeat
statement_list
until search_condition
end repeat [end_label]

while语句

WHILE语句也是有条件控制的循环语句，但WHILE语句和REPEAT语句是不一样的。WHILE语句是当满足条件时，执行循环内的语句

[begin_label:] while search_condition do
statement_list
end while [end lable]

调用存储过程和函数

• 存储过程和存储函数都是存储在服务器端的SQL语句的集合。要使用这些已经定义好的存储过程和存储函数，就必须通过调用的方式来实现。存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的
• 执行存储过程和存储函数需要拥有EXECUTE权限。EXECUTE权限的信息存储在information_schema数据库下的USER_PRIVILEGES表中

调用存储过程

call proc_name([parameter[,...]])

查看存储过程和函数

• 存储过程和函数创建以后，用户可以通过SHOW STATUS语句来查看存储过程和函数的状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息
• 使用show status语句查看存储过程和函数状态

show {procedure|function} status {like 'pattern'}

使用show create语句查看存储过程和函数的定义

show create {procedure|function} proc_name

从information_schema.routine表中查看

select * from information_schema.routine where routine_name='proc_name'

修改存储过程和函数

修改存储过程和函数语法

alter {procedure|function} proc_name
{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definder|invoker}
|comment 'string'

• 参数proc_name表示存储过程或函数的名称；参数characteristic指定存储函数的特性。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。SQLSECURITY{DEFINER|INVOKER}指明谁有权限来执行。DEFINDER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。COMMENT 'string’是注释信息

删除存储过程和函数

删除存储过程

drop procedure proc_name;

删除函数

drop function func_name;

查看函数状态

show {procedure|function} status {like 'pattern'}

查看存储过程和函数的定义

show create {procedure|function} proc_name

从information_schema.routine表中查看

select * from information_schema.routine where routine_name='proc_name'

修改存储过程和函数

修改存储过程和函数语法

alter {procedure|function} proc_name
{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definder|invoker}
|comment 'string'

• 参数proc_name表示存储过程或函数的名称；参数characteristic指定存储函数的特性。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。SQLSECURITY{DEFINER|INVOKER}指明谁有权限来执行。DEFINDER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。COMMENT 'string’是注释信息

删除存储过程和函数

删除存储过程

drop procedure proc_name;

删除函数

drop function func_name;

触发器

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器
触发器是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不是手工启动的，只要一个预定义的时间发生时，就会被MySQL自动调用

创建触发器

创建触发器基本语法

create trigger trigger_name before|after trigger_event 
on table_name for each row 
-- 在begin和end中间是所要执行语句的内容
begin
trigger_stat
end

• 参数trigger_name表示要创建的触发器名；
• 参数BEFORE和AFTER指定了触发器执行的时间，前者在触发器事件之前执行触发器语句，后者在触发器事件之后执行触发器语句；
• 参数trigger_EVENT表示触发事件，即触发器执行条件，包含DELETE、INSERT和UPDATE语句；
• 参数TABLE_NAME表示触发事件的操作表名；
• 参数FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器；
• 参数trigger_STMT表示激活触发器后被执行的语句

查看触发器

在MySQL软件中，不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件

查看触发器基本语法

show triggers

通过information_schema.triggers表查看

select * from information_schema.triggers where trigger_name = 'trigger_name'

删除触发器

删除触发器基础语法

drop trigger trigger_name

事务和锁

MySQL提供了多种存储引擎支持事务，支持事务的存储引擎有InnoDB和BDB。InnoDB支持ACID事务、行级锁和高并发，InnoDB存储引擎事务主要通过UNDO日志和REDO日志实现，MyISAM和MEMORY存储引擎则不支持事务

事务特性

• 原子性（Atomicity）：事务中所有的操作视为一个原子单元，即对事务所进行的数据修改等操作只能是完全提交或者完全回滚
• 一致性（Consistency）：事务在完成时，必须使所有的数据从一种一致性状态变更为另一种一致性状态，所有的变更都必须应用于事务的修改，以确保数据的完整性
• 隔离性（Isolation）：一个事务中的操作语句所做的修改必须与其他事务所做的修改相隔离。在查看事务数据时，数据所处的状态要么是被另一并发事务修改之前的状态，要么是被另一并发事务修改之后的状态，即当前事务不会查看由另一个并发事务正在修改的数据。这种特性通过锁机制实现
• 持久性（Durability）：事务完成之后，所做的修改对数据的影响是永久的，即使系统重启或者出现系统故障，数据仍可恢复

事务日志

• REDO日志：事务执行时需要将执行的事务日志写入日志文件里，对应的文件为REDO日志。当每条SQL语句进行数据更新操作时，首先将REDO日志写进日志缓冲区。当客户端执行COMMIT命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式或者时间间隔可以通过参数innodb_flush_log_at_trx_commit控制。REDO日志对应磁盘上的ib_logifleN文件，该文件默认为5MB，建议设置为512MB以便容纳较大的事务。在MySQL崩溃恢复时会重新执行REDO日志中的记录
• UNDO日志：与REDO日志相反，UNDO日志主要用于事务异常时的数据回滚，具体内容就是复制事务前的数据库内容到UNDO缓冲区，然后在合适的时间将内容刷新到磁盘。与REDO日志不同的是，磁盘上不存在单独的UNDO日志文件，所有的UNDO日志均存放在表空间对应的.ibd数据文件中，即使MySQL服务启动了独立表空间，依然如此。UNDO日志又被称为回滚段

MySQL事务控制语句

MySQL中可以使用BEGIN开始事务，使用COMMIT结束事务，中间可以使用ROLLBACK回滚事务。MySQL通过SET AUTOCOMMIT、STARTTRANSACTION、COMMIT和ROLLBACK等语句支持本地事务

-- 开始事务
start transaction | begin [work]
-- 结束事务
commit [work] [and] [no] chain [[no] release]
-- 回滚事务
rollback [work] [and] [no] [chain] [[no] release]
-- 设置事务自动提交
set autocommit = {0|1}

MySQL事务隔离级别

SQL标准定义了4种隔离级别，指定了事务中哪些数据改变其他事务可见，哪些数据改变其他事务不可见。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少

# 未提交读
set global transaction isolation level uncommitted
# 提交读
set global transaction isolation level committed
# 可重复读
set global transaction isolation level repeatable read
# 可串行化
set global transaction isolation level serializable

查看系统级的事务隔离级别

select @@global.transaction_isolation

设置会话级的事务隔离级别

# 未提交读
set session transaction isolation level uncommitted
# 提交读
set session transaction isolation level committed
# 可重复读
set session transaction isolation level repeatable read
# 可串行化
set session transaction isolation level serializable

查看会话级的事务隔离级别

select @@transaction_isolation

show variables like 'transaction_isolation'

READ-UNCOMMITED（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。因为其性能不比其他级别高很多，所以该隔离级别在实际应用中一般很少使用，读取未提交的数据被称为脏读（Dirty Read）

READ-COMMITED（读取提交内容）

在该事务级别，事务只能看见已经提交的事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的多个实例在处理时，会有其他事务进行提交，所以同一查询可能返回不同结果

REPEATABLE-READ（可重读）

这是MySQL默认的事务隔离级别，能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，理论上会导致另一问题：幻读（Phontom Read）。在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集

InnoDB和Falcon存储引擎通过多版本并发控制（Multi-Version Concurrency Control，MVCC）机制解决了该问题。InnoDB通过为每个数据行增加两个隐含值的方式来实现，这两个隐含值记录了行的创建时间和过期时间。每一行存储时间发生时的系统版本号，每个查询根据事务的版本号来查询结果

SERIALIZABLE（可串行化）

这是最高的隔离级别，通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，是在每个读的数据行上加上共享锁实现。在这个级别，可能会导致大量的超时现象和锁竞争，一般不推荐使用

InnoDB锁机制

为了解决数据库并发控制问题，如在同一时刻，客户端对于同一表进行更新或者查询操作，为了保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时为实现MySQL的各个隔离级别，锁机制为其提供了保证

锁的类型

• 锁的类型主要有共享锁、排他锁和意向锁三种
• 共享锁：共享锁的代号是S，是Share的缩写，共享锁的粒度是行或者元组（多个行）。一个事务获取了共享锁之后，可以对锁定范围内的数据执行读操作。有两个事务A和B，如果事务A获取了一个元组的共享锁，那么事务B可以立即获取这个元组的共享锁，但不能立即获取这个元组的排他锁，必须等到事务A释放共享锁之后
• 排他锁：排他锁的代码是X，是exclusive的缩写，排他锁的粒度与共享锁相同，也是行或者元组。一个事务获取了排他锁之后，可以对锁定范围内的数据执行写操作。如果事务A获取了一个元组的排他锁，事务B不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到事务A释放排他锁之后
• 意向锁：意向锁是一种表锁，锁定的粒度是整个表，分为意向共享锁（IS）和意向排他锁（IX）两类。意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意”表示事务想执行操作但还没有真正执行

锁的相容与互斥

• 锁和锁之间的关系要么是相容的，要么是互斥的
• 锁a和锁b相容是指：操作同样一组数据时，如果事务t1获取了锁a，另一个事务t2还可以获取锁b
• 锁a和锁b互斥是指：操作同样一组数据时，如果事务t1获取了锁a，另一个事务t2在t1释放锁a之前无法获取锁b
• MySQL锁兼容情况
  

锁的粒度

锁的粒度主要分为表锁和行锁

• 表锁管理锁的开销最小，同时允许的并发量也是最小的锁机制。MyISAM存储引擎使用该锁机制。当要写入数据时，整个表记录被锁，此时其他读/写动作一律等待。同时，一些特定的动作，如ALTER TABLE执行时使用的也是表锁
• 行锁可以支持最大的并发。InnoDB存储引擎使用该锁机制。如果要支持并发读/写，建议采用InnoDB存储引擎，因为采用行级锁可以获得更多的更新性能

添加共享锁

select * from table_name lock in share mode

添加排它锁

select * from table_name for update

在需要加到一个区间值域时，InnoDB引擎会自动给会话事务中的共享锁、更新锁以及排他锁再加上一个间隙锁（或称为范围锁），对不存在的数据也锁住，防止出现幻写。以上语句描述的情况与MySQL设置的事务隔离级别有较大关系

用户安全管理

MySQL是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限
MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。用户管理包括管理用户账户、权限等

权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库中，由mysql_install_db脚本初始化，MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限

user表

user表是MySQL中非常重要的一个权限表，有49个字段，这些字段可以分成4类，分别是范围列、权限列、安全列和资源控制列

• 范围列：user表的范围列包括Host和User，分别表示主机名和用户名。其中，User和Host为User表的联合主键。Host指明允许访问的IP或主机范围，User指明允许访问的用户名
• 权限列：权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作，包括查询权限、修改权限等普通权限，还包括关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。如果要修改权限，可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。Grant_priv字段表示是否拥有GRANT权限；Shutdown_priv字段表示是否拥有停止MySQL服务的权限；Super_priv字段表示是否拥有超级权限；Execute_priv字段表示是否拥有EXECUTE权限，拥有EXECUTE权限可以执行存储过程和函数
• 安全列：安全列有12个字段，其中两个是ssl相关的，两个是x509相关的，另外8个是授权插件和密码相关的。ssl用于加密；X509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，该字段不能为空。如果该字段为空，服务器将会向错误日志写入信息并且禁止该用户访问。读者可以通过SHOW VARIABLES LIKE’have_openssl’语句来查询服务器是否支持ssl功能
• 资源控制列：资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：max_questions表示用户每小时允许执行的查询操作次数；max_updates表示用户每小时允许执行的更新操作次数；max_connections表示用户每小时允许执行的连接操作次数；max_user_connections表示用户允许同时建立连接的次数。一个小时内用户查询或者连接的数量超过资源控制限制，用户将被锁定。直到下一个小时，才可以再次执行对应的操作。可以使用GRANT语句更新这些字段的值

db表

db表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库

• 用户列：db表的用户列有3个字段，分别是Host、Db和User。这3个字段分别表示主机名、数据库名和用户名。host表的用户列有两个字段，分别是Host和Db。这两个字段分别表示主机名和数据库名
• 权限列：db表中的权限是针对单个数据库的，用户先根据user表的内容获取权限，再根据db表的内容获取权限

tables_priv表

tables_priv表用来对表设置操作权限

• tables_priv表有8个字段，Host、Db、User和Table_name四个字段分别表示主机名、数据库名、用户名和表名Grantor表示修改该记录的用户
• Timestamp字段表示修改该记录的时间
• Table_priv表示对象的操作权限，包括Select、Insert、Update、Delete、Create、Drop、Grant、References、Index和Alter
• Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References

columns_priv表

columns_priv表用来对表的某一列设置权限

• columns_priv表只有7个字段，分别是Host、Db、User、Table_name、Column_name、Timestamp、Column_priv
• Column_name用来指定对哪些数据列具有操作权限

procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限

• Host、Db和User字段分别表示主机名、数据库名和用户名
• Routine_name表示存储过程或函数的名称。Routine_type表示存储过程或函数的类型
• Routine_type字段有两个值，分别是FUNTION和PROCEDURE。FUNCTION表示这是一个函数：PROCEDURE表示这是一个存储过程
• Grantor是插入或修改该记录的用户
• Proc_pric表示拥有的权限，包括Execute、Alter Routine、Grant三种
• Timestamp表示记录更新时间

账户管理

登录和退出MySQL

mysql -h hostname|host_ip -P post -u username -p database_name -e "sql_statement"

新建普通用户

create user 'user_name'@'host_name' [identified by 'password']
[, user_name [identified by 'password']]...
[with resource_option [resource_option]...]

• user参数表示新建用户的账户，user由用户（User）和主机名（Host）构成
• INDENTIFIED BY关键字用来设置用户的密码
• password参数表示用户的密码
• resource_option表示账号资源限制，该参数有以下4个选项：MAX_QUERIES_PER_HOUR count：设置每个小时允许执行count次查询。MAX_UPDATES_PER_HOUR count：设置每个小时允许执行count次更新。MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。MAX_USER_CONNECTIONS count：设置单个用户可以同时具有count个连接数。

CREATE USER语句可以同时创建多个用户，新用户可以没有初始密码

删除普通用户

用drop语句来删除普通用户

drop user 'user_name'@'host_name' [, 'user_name'@'host_name']...

使用delete语句来删除用户

delete from mysql.user where host='host_name' and user='user_name'

root用户修改自己的密码

root用户拥有很高的权限，因此必须保证root用户的密码的安全。root用户可以通过多种方式来修改密码，使用ALTER USER命令修改用户密码是MySQL官方推荐的方式。此外，也可通过SET语句修改密码

由于MySQL 8中已移除了PASSWORD()函数，因此不再使用UPDATE语句直接操作用户表修改密码

使用mysqladmin修改root用户的密码

mysqladmin -u user_name -p password "new_password"

• 上面语法中的password为关键字，而不是指旧密码，而且新密码（new_password）必须用双引号引起来。使用单引号会出现错误，这一点要特别注意，如果使用单引号，可能会造成修改后的密码不是你想要修改的

root用户登录，使用alter user命令修改root用户的密码

alter user user() identified by 'new_password'

root用户登录，使用SET语句来修改root用户的密码

set password = 'new_password'

root用户修改普通用户的密码

root用户不仅可以修改自己的密码，还可以修改普通用户的密码，root用户登录MySQL服务器后，可以通过SET语句和ALTER语句来修改普通用户的密码。由于PASSWORD()函数已移除，因此使用UPDATE直接操作用户表的方式已不再使用

使用SET命令来修改普通用户的密码

set password for 'user_name'@'host_name' = 'new_password'

使用alter语句修改普通用户的密码

alter user 'user_name'@'host_name' [identified by 'password'] [, identified by 'password']...

普通用户登录，使用set命令修改

set password = 'new_password'

使用mysqladmin命令修改普通用户密码

mysqladmin -u user_name -pold_password password 'new_password'

root用户密码丢失的解决方法（windows下）

1. 停止mysql服务

net stop mysql

2. 进入mysql安全模式

mysqld --console --skip-grant-tables --shared-memory

3. 在另一个窗口打开，登录root用户

mysql -u root

4. 先使用update语句将root用户的密码置空，如果root用户的密码不为空，则无法直接修改

update mysql.user set authentication_string='' where user='root'

5. 密码置空后刷新权限

flsuh privileges

6. 使用alter修改用户的密码

alter user 'root'@localhost identified by 'new_password'

权限管理

权限管理主要是对登录到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患，MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限

MySQL的各种权限

权限账户信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存

grant和revoke可以操作的权限：

不同权限下mysqladmin可以使用的命令

• reload命令告诉服务器将授权表重新读入内存：flush-privileges是reload的同义词；refresh命令清空所有表并关闭/打开记录文件；其他flush-xxx命令执行类似refresh的功能，但是范围更有限，并且在某些情况下可能更好用。例如，如果只是想清空记录文件，flush-logs是比refresh更好的选择
• shutdown命令用于关闭服务器。只能从mysqladmin发出命令
• processlist命令用于显示在服务器内执行的线程的信息（其他账户相关的客户端执行的语句）。kill命令用于杀死服务器线程。用户总是能显示或杀死自己的线程，但是需要PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。kill命令用来中止其他用户或更改服务器的操作方式。总的来说，只授权限给需要他们的那些用户

授权

授权就是为某个用户或者角色赋予某些权限。例如，可以为新建的用户赋予查询所有数据库和表的权限。合理的授权能够保证数据库的安全。不合理的授权会使数据库存在安全隐患。MySQL中使用GRANT关键字来为用户或角色设置权限

• 全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限
• 数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name和REVOKE ALL ON db_name.*只授予和撤销数据库权限
• 表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限
• 列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列

grant基础语法

grant priv_type[(column_list)] on database.table to user_name@host_name [,user_name@host_name] [with with_option[with_option]...]

• 其中，priv_type参数表示权限的类型；column_list参数表示权限作用于哪些列上，没有该参数时作用于整个表上；user参数由用户名和主机名构成，形式是’username’@‘hostname’，WITH关键字后面带有一个GRANT OPTION参数，该参数表示被授权的用户可以将这些权限赋予给别的用户
• 除了直接给用户授权外，在MySQL 8版本以后，当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色

create role 'role_name'[@'host_name'] [,'role'[@'host_name']]...

给角色赋予权限

grant privileages on table_name to 'role_name'[@'host_name']

给用户添加角色

grant role [,role2...] to 'user_name'@'localhost' [,'user_name2'@'localhost'...]

添加角色之后，如果角色处于未激活状态，需要先将用户对应的角色激活，才能拥有对应的权限。激活角色使用SET语句

set role default

查看权限

查看user表

select * from mysql.user

使用show grant查看用户权限

show grants for 'user_name'@'host_name'

使用show grant查看角色权限

show grants for role_name

收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、table_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存

revoke基础语法

revoke priv_type [(column_list)] ... on database_name.table_name from 'user_name'@'host_name' [,'user_name'@'host_name']

收回所有的权限

revoke all privileges,grant option from 'user_name'@'host_name' [,'user_name'@'host_name']...

撤销用户对应的角色

revoke role_name from 'user_name'@'host_name'

访问控制

正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许

MySQL的访问控制分为两个阶段：连接核实阶段和请求核实阶段

• 连接核实阶段：客户端用户会在连接请求中提供用户名、主机地址和用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供的信息
• 请求核实阶段：对此连接上进来的每个请求，服务器会检查该请求要执行什么操作，是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。
• 请求核实阶段确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授予，MySQL就会继续检查db表，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据。如果在该层级没有找到限定的权限，MySQL就继续检查tables_priv表和columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败
  

数据库备份与恢复

为了有效防止数据丢失，并将损失降到最低，用户应定期对MySQL数据库服务器进行维护。如果数据库中的数据丢失或者出现错误，就可以使用备份的数据进行恢复，这样会尽可能地降低意外原因导致的损失。数据库的维护包括数据备份、恢复、导出和导入操作

数据备份

mysqldump命令的工作原理很简单。首先，查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。还原数据时可以使用其中的CREATE语句来创建表，使用其中的INSERT语句来还原数据

mysqldump备份数据库基础语法

mysqldump -u user_name -p[password] db_name > backup_name.sql

mysqldump备份多个数据库

mysqldump -u user_name -p[password] -databases [db_name1 [,db_name2...]]

mysqldump备份所有数据库

mysqldump -u user_name -p[password] --all-databases > backup_name.sql

mysqldump备份表的基础语法

mysqldump -u user_name -p[password] db_name table_name1 table_name2... > backup_name.sql

提示：如果运行MySQLdump没有–quick或–opt选项，MySQLdump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用–skip-opt禁用。如果使用最新版本的MySQLdump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，就不要使用–opt或-e选项

直接复制整个数据库目录：这是一种简单、快速、有效的备份方式，但不是最好的备份方法。因为，实际情况可能不允许停止MySQL服务器或者锁住表，而且这种方法对InnoDB存储引擎的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便。但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同

数据恢复

数据库管理员的操作失误和计算机的软硬件故障都会破坏数据库文件。当数据库遭到丢失和破坏后，可以通过数据备份文件将数据恢复到备份时的状态。这样可以将损失尽可能地降低到最小

使用mysql命令进行恢复

mysql -u user_name -p[password] [db_name] < backup_name.sql 

如果已经登录MySQL服务器，还可以使用source命令导入SQL文件

source backup_file.sql

直接复制到数据库目录：只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。而且，这种方式对MyISAM类型的表比较有效。对于InnoDB类型的表则不可用。因为InnoDB表的表空间不能直接复制

数据迁移

数据库迁移就是指将数据库从一个系统移动到另一个系统上。数据库迁移的原因是多样的，可能是计算机系统升级，也有可能是部署新的开发系统、MySQL数据库升级或者换成其他类型的数据库

相同版本的MySQL数据库之间的迁移

mysqldump -h host_name -u user_name -p[password] --all-databases|mysql -h host_name2 -u user_name2 -p[password]

不同版本的MySQL数据库之间的迁移：高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。对于MyISAM类型的表可以直接复制，但是InnoDB类型的表不可以使用这种方法。常用的办法是使用mysqldump命令来进行备份，然后通过mysql命令将备份文件导入目标MySQL数据库中

MySQL官方提供的工具MySQLMigration Toolkit也可以在不同数据之间进行数据迁移

表的导入与导出

使用select into导出文本文件

select into导出基础语法

select columnlist from table_name where condition into outfile 'file_name' [option]

• [OPTIONS]为可选参数选项，OPTIONS部分的语法包括FIELDS和LINES子句，其可能的取值有：
• FIELDS TERMINATED BY ‘value’：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符“\t”
• FIELDS [OPTIONALL] ENCLOSED BY ‘value’：设置字段的包围字符，只能为单个字符，如果使用了OPTIONALLY，就只有CHAR和VARCHAR等字符数据字段被包括
• FIELDS ESCAPED BY ‘value’：设置如何写入或读取特殊字符，只能为单个字符，即设置转移字符，默认值为’’
• LINES STARTING BY ‘value’：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符
• LINES TERMINATED BY ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”
• FIELDS和LINES两个子句都是自选的，但是如果两个都被指定了，FIELDS就必须位于LINES的前面
• select into语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件，就不能使用。在这种情况下，应该是客户主机上使用类似“mysql –e “SELECT …”> filename”的命令来生成文件

MySQL默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作，设置变量secure_file_priv的值

查询secure_file_priv值

show variables like '%secure%'

使用mysqldump命令导出文件

mysqldump导出数据基础语法

mysqldump -u user_name -p[password] -T path db_name [tables] [options]

• [OPTIONS]为可选参数选项，这些选项需要结合-T选项使用。使用OPTIONS常见的取值有：
• –fields-terminated-by=values：设置字段之间的分隔字符可以为单个或多个字符，默认情况下为制表符“\t”
• –fields-enclosed-by=value：设置字段的包围字符
• –fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，只能包括CHAR和VARCHAR等字符数据字段
• –fileds-escaped-by=value：控制如何写入或读取特殊字符，只能为单个字符，即设置转移字符，默认值为反斜线“\”
• –line-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”

与SELECT…INTO OUTFILE语句中的OPTIONS各个参数设置不同，这里OPTIONS各个选项等号后面的value值不要用引号引起来

使用mysql命令导出文件

mysql -u root -p[password] -e "select statement" dbname > filename.txt

使用load data infile方式导入文本文件

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中，文件名称必须为文字字符串

load data [local] infile file_name into table table_name [option] [ignore number lines]

• OPTIONS部分的语法包括FIELDS和LINES语句，其可能的取值有：
• FIELDS TERMINATED BY ‘value’：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符“\t”
• FIELDS [OPTIONALLY] ENCLOSED BY ‘value’：设置字段的包围字符，只能为单个字符，只能包括CHAR和VARCHAR等字符数据字段
• FIELDS ESCAPED BY ‘value’：控制如何写入或读取特殊字符，只能为单个字符，即设置转移字符，默认值为反斜线“\”
• LINES STARTING BY ‘value’：设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符
• LINES TERMINATED BY ‘value’：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”
• IGNORE number LINES选项表示忽略文件开始处的行数，number表示忽略的行数。执行LOAD DATA语句需要FILE权限

使用mysqlimport方式导入文本文件

使用MySQLimport可以导入文本文件，并且不需要登录MySQL客户端。MySQLimport命令提供许多与LOAD DATA INFILE语句相同的功能，大多数选项直接对应LOAD DATA INFILE子句

mysqlimport -u user_name -p[password] db_name file_name [options]

• [OPTIONS]为可选参数项，其常见的取值有：
• –fields-terminated-by=values：设置字段之间的分隔字符，可以为单个或多个字符，默认情况下为制表符“\t”
• –fields-enclosed-by=value：设置字段的包围字符
• –fields-optionally-enclosed-by=value：设置字段的包围字符，只能为单个字符，只能包括CHAR和VARCHAR等字符数据字段
• –line-terminated-by=value：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”
• –ignore-lines=n：忽视数据文件的前n行

日志管理

MySQL 8之前的版本有不同类型的日志文件：二进制日志、错误日志、通用查询日志和慢查询日志。MySQL 8又新增了两种支持的日志：中继日志和数据定义语句日志。分析这些日志，可以查询到MySQL数据库的运行情况、用户操作、错误信息等，可以为MySQL管理和优化提供必要的信息

• 二进制日志：记录所有更改数据的语句，可以用于用户数据复制
• 错误日志：记录MySQL服务启动、运行或停止时出现的问题
• 通用查询日志：记录建立的客户端连接和执行的语句
• 慢查询日志：记录执行时间超过long_query_time的所有查询或不适用索引的查询
• 中继日志：记录复制时从主服务器收到的数据改变
• 数据定义语句日志：记录数据定义语句执行的元数据操作

除了二进制日志外，其他日志都是文本格式，其他日志都是文本文件。默认情况下，所有日志创建于MySQL数据目录中。通过刷新日志可以强制MySQL关闭和重新打开日志文件，当执行一个FLUSH LOGS语句或执行mysqladmin flush-logs和mysqladmin refresh时，将刷新日志

默认情况下只启动错误日志的功能，其他3类日志都需要数据库管理员进行设置

二进制日志

• 二进制日志也叫作变更日志（Update Log），主要用于记录数据库的变化情况。通过二进制日志可以查询MySQL数据库中进行了哪些改变。二进制日志以一种有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的语句。语句以“事件”的形式保存，描述数据更改
• 二进制日志还包含关于每个更新数据库的语句的执行时间信息，它不包含没有修改任何数据的语句。如果想要记录所有语句（例如，为了识别有问题的查询），就使用一般查询日志。使用二进制日志的主要目的是最大可能地恢复数据库，因为二进制日志包含备份后进行的所有更新

启动二进制日志

• 在默认情况下，MySQL 8中的二进制文件是开启的，可以通过以下SQL语句来查询MySQL系统中的二进制日志开关

show variables like '%log_bin%'

• 数据库文件文件最好不要与日志文件放在同一个磁盘上。这样，当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据

查看二进制日志

MySQL创建二进制日志文件时，首先创建一个以filename为名称、以.index为后缀的文件；再创建一个以filename为名称、以.000001为后缀的文件。当MySQL服务重新启动一次，以.000001为后缀的文件会增加一个，并且后缀名以1递增；如果日志长度超过了max_binlog_size的上限（默认是1GB），就会创建一个新的日志文件

查看二进制日志的个数和大小

show binary logs

MySQL二进制日志并不能直接查看，如果要查看日志内容，可以通过mysqlbinlog命令查看

mysqlbinlog "file_name"

使用二进制日志恢复数据

日志恢复基础语法

mysqlbinlog [options] file_name|mysql -u root -p[password]

• option是一些可选的选项：
• –start-date和–stop-tate可以指定恢复数据库的起始时间点和结束时间点
• –start-position和–stop-position可以指定恢复数据的开始位置和结束位置

使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如rlog.000001必须在rlog.000002之前恢复

暂停二进制日志

在配置文件中设置了log-bin选项以后，MySQL服务将会一直开启二进制日志功能。删除该选项后就可以停止二进制日志功能。如果需要再次启动这个功能，就需要重新添加log-bin选项。MySQL中提供了暂时停止二进制日志功能的语句

-- 暂停二进制日志
set sql_log_bin = 0
-- 恢复二进制日志
set sql_log_bin = 1

删除二进制日志

MySQL的二进制文件可以配置自动删除。同时，MySQL提供了安全的手动删除二进制文件的方法：PURGE MASTER LOGS只删除部分二进制日志文件；RESET MASTER删除所有的二进制日志文件

使用PURGE MASTER LOGS语句删除指定日志文件

purge {master|binary} logs to "log_name"
purge {master|binary} logs before "date"

• mysql删除创建时间比log_name日志早的所有日志

使用reset master删除所有二进制日志文件

reset master

• 执行完该语句后，所有二进制日志将被删除，MySQL会重新创建二进制文件，新的日志文件扩展名将重新从000001开始编号

操作错误日志

启动错误日志

在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件夹下。错误日志文件的名称默认为hostname.err
如果需要指定文件名，就需要在my.cnf或者my.ini中进行如下配置

查看错误日志

错误日志中记录着开启和关闭MySQL服务的时间，以及服务运行过程中出现哪些异常等信息，通过错误日志可以查看系统的运行状态，便于及时发现故障、修复故障

如果不知道日志文件的存储路径，可以使用SHOW VARIABLES语句查询错误日志的存储路径

show variables like 'log_err%'

删除错误日志

数据库管理员可以删除很长时间以前的错误日志，以保证MySQL服务器上的硬盘空间，MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除

在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件。flush_logs在重新加载日志的时候，如果文件不存在，就会自动创建。所以在删除错误日志之后，如果需要重建日志文件，就需要在服务器端执行以下命令

mysqladmin -u root -p[password] flush-logs

或者登录后，执行

flush logs

通用查询日志

通用查询日志是用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句和查询语句等

启动停止通用查询日志

MySQL服务器默认情况下并没有开启通用查询日志。如果需要开启通用查询日志，可以通过修改my.cnf或者my.ini配置文件来开启

[mysqld]
general_log=ON
general_log_path=[path[filename]]

从MySQL 5.1.6版开始，general query log和slow query log开始支持写到文件或者数据库表两种方式，并且日志的开启和输出方式的修改都可以在Global级别动态修改，默认文件名为host_name.log

set global general_log=ON
set global general_log=OFF
show variables like 'general_log'

删除通用查询日志

1. 删除日志文件
2. 使用mysqladmin重新生成查询日志

mysqladmin -u root -p[password] flush-logs

慢查询日志

慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志可以查找出哪些查询语句执行时间较长、执行效率较低，以便进行优化

启动慢查询日志

修改my.cnf或者my.ini

[mysqld]
long_query_time=n
slow_query_log=ON
slow_query_log_file=[path[filename]]

查看慢查询日志配置

show variables like '%slow%'
show variables like '%long_query_time%'

通过set语句开启慢查询日志

set global slow_query_log = ON
set global long_query_time = 2
set session long_query_time = 2

查看和分析慢查询日志

查看慢查询日志所在目录

show variables like '%slow_query_log_file%'

停止慢查询日志

修改配置文件my.cnf或my.ini

[mysqld]
slow_query_log = OFF

使用set修改环境变量

set global slow_query_log = OFF

删除慢查询日志

1. 手工删除慢查询日志
2. 使用mysqladmin刷新日志，生成新日志文件

mysqladmin -u root -p[password] flush-logs