MySQL事务、DCL(Data Control Language)语句

事务

事务执行是一个整体，所有的 SQL 语句都必须执行成功。

MYSQL 中可以有两种方式进行事务的操作：

1. 手动提交事务

2. 自动提交事务

手动提交事务

功能	SQL语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务。

取消自动提交

-- 查看 MySQL 是否开启自动提交事务
select @@autocommit; -- @@表示全局变量，1 表示开启，0 表示关闭

-- 取消自动提交事务
set @@autocommit = 0;

事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

事务的步骤：

1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件

2. 开启事务以后，所有的操作都会先写入到临时日志文件中

3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回

4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

事务的隔离级别

事务的四大特性 ACID

事务特性	含义
原子性（Atomicity）	每个事务都是一个整体，不可再拆分，事务中所有的SQL语句要么都执行成功，要么都失败。
一致性(Consistency)	事务在执行前数据库的状态与执行后数据库的状态保持一致。
隔离性(Isolation)	事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性(Durability)	一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

事务的隔离级别

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

MySQL 数据库有四种隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

隔离级别越高，性能越差，安全性越高。

MySQL 事务隔离级别相关的命令

-- 查询隔离级别
select @@tx_isolation;

-- 设置隔离级别
set global transaction isolation level 级别字符串;

set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升，read committed 的方式可以避免脏读的发生。

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

解决不可重复读的问题：将全局的隔离级别进行提升为：repeatable read，同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别

在 MySQL 中无法看到幻读的效果。

但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读。

DCL(Data Control Language)

• DDL： create / alter / drop
• DML：insert /update/delete
• DQL ：select /show
• DCL ：grant /revoke

mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

用户名****'@'****主机名GRANT…ON…TO：-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 用户名：将创建的用户名
-- 主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符% 
-- 密码：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

-- 给用户授权
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
-- GRANT…ON…TO：授权关键字
-- 权限：授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL
-- 数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
-- '用户名'@'主机名'：给哪个用户授权，注：有 2 对单引号

-- 撤销授权
REVOKE 权限 1, 权限 2... ON 数据库.表名 FROM '用户名'@'主机名';
-- REVOKE…ON…FROM：撤销授权的关键字
-- 权限：用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权限则使用 ALL
-- 数据库名.表名：对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用*表示，如*.*
-- '用户名'@'主机名'：给哪个用户撤销

-- 查看权限
SHOW GRANTS FOR '用户名'@'主机名';

-- 删除用户
DROP USER '用户名'@'主机名';

-- 修改管理员密码
mysqladmin -uroot -p password 新密码
-- 需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。

-- 修改普通用户密码
set password for '用户名'@'主机名' = password('新密码');
-- 需要在登陆 MySQL 的情况下操作，新密码要加单引号。