炸裂!MySQL 82 张图带你飞!(二)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 这篇文章我们来了解一下 MySQL 中的高级内容。

事务表和非事务表

事务表故名思义就是支持事务的表,支不支持事务和 MySQL 的存储类型有关,一般情况下,InnoDB 存储引擎的表是支持事务的,关于 InnoDB 的知识,我们会在后面详细介绍。

非事务表相应的就是不支持事务的表,在 MySQL 中,存储引擎 MyISAM 是不支持事务的,非事务表的特点是不支持回滚。

对于回滚的话,还要讲一点就是 SAVEPOINT,它能指定事务回滚的一部分,但是不能指定事务提交的一部分。SAVEPOINT 可以指定多个,在满足不同条件的同时,回滚不同的 SAVEPOINT。需要注意的是,如果定义了两个相同名称的 SAVEPOINT,则后面定义的 SAVEPOINT 会覆盖之前的定义。如果 SAVEPOINT 不再需要的话,可以通过 RELEASE SAVEPOINT 来进行删除。删除后的 SAVEPOINT 不能再执行 ROLLBACK TO SAVEPOINT 命令。

我们通过一个示例来进行模拟不同的 SAVEPOINT

首先先启动一个事务 ,向 cxuan005 中插入一条数据,然后进行查询,那么是可以查询到这条记录的

start transaction;
insert into cxuan005(id,info) values(666,'cxuan666');
select * from cxuan005 where id = 666;

查询之后的记录如下

微信图片_20220418192858.png

然后我们定义一个 SAVEPOINT,如下所示

savepoint test;

然后继续插入一条记录

insert into cxuan005(id,info) values(777,'cxuan777');

此时就可以查询到两条新增记录了,id 是 666 和 777 的记录。

select * from cxuan005 where id = 777;

微信图片_20220418192902.png

那么我们可以回滚到刚刚定义的 SAVEPOINT

rollback to savepoint test;

再次查询 cxuan005 这个表,可以看到,只有 id=666 的这条记录插入进来了,说明 id=777 这条记录已经被回滚了。

微信图片_20220418192905.png

此时我们看到的都是 mysql01 中事务还没有提交前的状态,所以这时候 mysql02 中执行查询操作是看不到 666 这条记录的。

然后我们在 mysql01 中执行 commit 操作,那么此时在 mysql02 中就可以查询到这条记录了。

SQL 安全问题

SQL 安全问题应该是我们程序员比较忽视的一个地方了。日常开发中,我们一般只会关心 SQL 能不能解决我们的业务问题,能不能把数据查出来,而对于 SQL 问题,我们一般都认为这是 DBA 的活,其实我们 CRUD 程序员也应该了解一下 SQL 的安全问题。

SQL 注入简介

SQL 注入就是利用某些数据库的外部接口将用户数据插入到实际的 SQL 中,从而达到入侵数据库的目的。SQL 注入是一种常见的网络攻击的方式,它不是利用操作系统的 BUG 来实现攻击的。SQL 主要是针对程序员编写时的疏忽来入侵的。

SQL 注入攻击有很大的危害,攻击者可以利用它读取、修改或者删除数据库内的数据,获取数据库中的用户名和密码,甚至获得数据库管理员的权限。并且 SQL 注入一般比较难以防范。

SQL Mode

MySQL 可以运行在不同的 SQL Mode 模式下,不同的 SQL Mode 定义了不同的 SQL 语法,数据校验规则,这样就能够在不同的环境中使用 MySQL ,下面我们就来介绍一下 SQL Mode。

SQL Mode 解决问题

SQL Mode 可以解决下面这几种问题

  • 通过设置 SQL Mode,可以完成不同严格程度的数据校验,有效保障数据的准确性。
  • 设置 SQL Mode 为 ANSI 模式,来保证大多数 SQL 符合标准的 SQL 语法,这样应用在不同数据库的迁移中,不需要对 SQL 进行较大的改变
  • 数据在不同数据库的迁移中,通过改变 SQL Mode 能够更方便的进行迁移。

下面我们就通过示例来演示一下 SQL Mode 用法

我们可以通过

select @@sql_mode;

来查看默认的 SQL Mode,如下是我的数据库所支持的 SQL Mode

微信图片_20220418192911.png

涉及到很多 SQL Mode,下面是这些 SQL Mode 的解释

ONLY_FULL_GROUP_BY:这个模式会对 GROUP BY 进行合法性检查,对于 GROUP BY 操作,如果在SELECT 中的列,没有在 GROUP BY 中出现,那么将认为这个 SQL 是不合法的,因为列不在 GROUP BY 从句中

同样举个例子,我们现在查询一下 cxuan005 的 id 和 info 字段。

select id,info from cxuan005;

这样是可以运行的

微信图片_20220418192915.png

然后我们使用 GROUP BY 字句进行分组,这里只对 info 进行分组,我们看一下会出现什么情况

select id,info from cxuan005 group by info;

微信图片_20220418193123.png

我们可以从错误原因中看到,这条 SQL 语句是不符合 ONLY_FULL_GROUP_BY 的这条 SQL Mode 的。因为我们只对 info 进行分组了,没有对 id 进行分组,我们把 SQL 语句改成如下形式

select id,info from cxuan005 group by id,info;

微信图片_20220418192919.png

这样 SQL 就能正确执行了。

当然,我们也可以删除 sql_mode = ONLY_FULL_GROUP_BY 的这条 Mode,可以使用

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

来进行删除,删除后我们使用分组语句就可以放飞自我了。

select id,info from cxuan005 group by info;

微信图片_20220418192926.png

但是这种做法只是暂时的修改,我们可以修改配置文件 my.ini 中的 sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES:这就是严格模式,在这个模式下会对数据进行严格的校验,错误数据不能插入,报error 错误。如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。

当使用 innodb 存储引擎表时,考虑使用 innodb_strict_mode 模式的 sql_mode,它能增量额外的错误检测功能。

NO_ZERO_IN_DATE:这个模式影响着日期中的月份和天数是否可以为 0(注意年份是非 0 的),这个模式也取决于严格模式是否被启用。如果这个模式未启用,那么日期中的零部分被允许并且插入没有警告。如果这个模式启用,那么日期中的零部分插入被作为 0000-00-00 并且产生一个警告。

这个模式需要注意下,如果启用的话,需要 STRICT_TRANS_TABLESNO_ZERO_IN_DATE 同时启用,否则不起作用,也就是

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE';

然后我们换表了,使用 cxuan003 这张表,表结构如下

微信图片_20220418192930.png

我们主要测试日期的使用,在 cxuan003 中插入一条日期为 0000-00-00 的数据

insert into cxuan003 values(111,'study','0000-00-00');

发现能够执行成功,但是把年月日各自变为 0 之后再进行插入,则会插入失败。

insert into cxuan003 values(111,'study','2021-00-00');

微信图片_20220418192937.png

insert into cxuan003 values(111,'study','2021-01-00');

微信图片_20220418192940.png

这些组合有很多,我这里就不再细致演示了,读者可以自行测试。

如果要插入 0000-00-00 这样的数据,必须设置 NO_ZERO_IN_DATENO_ZERO_DATE

ERROR_FOR_DIVISION_BY_ZERO:如果这个模式未启用,那么零除操作将会插入空值并且不会产生警告;如果这个模式启用,零除操作插入空值并产生警告;如果这个模式和严格模式都启用,零除从操作将会产生一个错误。

NO_AUTO_CREATE_USER:禁止使用 grant 语句自动创建用户,除非认证信息被指定。

NO_ENGINE_SUBSTITUTION:此模式指定当执行 create 语句或者 alter 语句指定的存储引擎没有启用或者没有编译时,控制默认存储引擎的自动切换。默认是启用状态的。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
人工智能 关系型数据库 MySQL
细节爆炸!腾讯用13个案例实战讲明白MySQL,没想到这么全
MySQL MySQL的数据类型有很多种,选择正确的数据类型对于获得高性能特别地重要。MySQL作为应用最广泛、最流行的开源数据库之一,MySQL数据库软件已经广为人知了,MySQL的衍生技术百花齐放,拓展架构异彩纷呈。尤其是在性能优化及高可用架构两方面,很多从业多年的DBA限于生产环境的固定体系,往往都是盲人摸象,难窥全局! 当前很火的Facebook、腾讯、淘宝等大型网站都在使用MySQL的数据库。在最近的二十年,我们也目睹了云计算、大数据、物联网、区块链、5G、人工智能、数字化转型等都有多种浪潮的冲击。而且一些技术随着热潮的褪去也降低了热度,;另一些技术在多次冲刷与洗礼中屹立不倒,
809 5
|
11月前
|
SQL 存储 关系型数据库
认真学习MySQL中游标与实例分析
认真学习MySQL中游标与实例分析
79 0
|
SQL 关系型数据库 MySQL
深聊MySQL,从入门到入坟之:如何让order by、group by查询速度飞起来
深聊MySQL,从入门到入坟之:如何让order by、group by查询速度飞起来
123 0
|
SQL 关系型数据库 MySQL
MySQL之程序猿得“爱恨情仇“--(增删查改等MySQL基本操作)(上)
MySQL之程序猿得“爱恨情仇“--(增删查改等MySQL基本操作)(上)
|
SQL 关系型数据库 MySQL
MySQL之程序猿得“爱恨情仇“--(增删查改等MySQL基本操作)(下)
MySQL之程序猿得“爱恨情仇“--(增删查改等MySQL基本操作)(下)
|
SQL 存储 关系型数据库
42 张图带你撸完 MySQL 优化 上
42 张图带你撸完 MySQL 优化 上
93 0
42 张图带你撸完 MySQL 优化  上
|
SQL 存储 自然语言处理
42 张图带你撸完 MySQL 优化 下
42 张图带你撸完 MySQL 优化 下
118 0
42 张图带你撸完 MySQL 优化  下
|
SQL 存储 关系型数据库
42 张图带你撸完 MySQL 优化 中
42 张图带你撸完 MySQL 优化 中
127 0
42 张图带你撸完 MySQL 优化  中
|
SQL 关系型数据库 MySQL
深聊MySQL,从入门到入坟之:如何优化数据导入?
深聊MySQL,从入门到入坟之:如何优化数据导入?
204 0
深聊MySQL,从入门到入坟之:如何优化数据导入?
|
SQL 存储 关系型数据库
炸裂!MySQL 82 张图带你飞!(一)
这篇文章我们来了解一下 MySQL 中的高级内容。
93 0
炸裂!MySQL 82 张图带你飞!(一)