MySQL进阶二

发表于 | 分类于

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

. . .

ACID

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（一致性或稳定性）、Isolation（隔离性）、Durability（持久性）

  • A 事务的原子性：一组事务，要么成功, 要么撤回。
  • C 稳定性 ：有非法数据（外键约束之类），事务撤回。
  • I 隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
  • D 持久性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得.

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

  • BEGIN或START TRANSACTION；显式地开启一个事务；
  • COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
  • ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
  • RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO identifier；把事务回滚到标记点；
  • SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

  • 用 BEGIN, ROLLBACK, COMMIT来实现
    • BEGIN 开始一个事务
    • ROLLBACK 事务回滚
    • COMMIT 事务确认
  • 直接用 SET 来改变 MySQL 的自动提交模式:
    • SET AUTOCOMMIT=0 禁止自动提交
    • SET AUTOCOMMIT=1 开启自动提交

事务的4个隔离级别

具体例子参考这篇博客

  • 读未提交(Read Uncommitted)：
    允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

  • 读已提交(Read Committed)：
    只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

  • 可重复读(Repeated Read)：
    可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

  • 串行读(Serializable)：
    完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

相关术语

  • 脏读 :
    脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
  • 不可重复读 :
    是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
  • 幻读 :
    第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

临时表

临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

防SQL注入

防止SQL注入，我们需要注意以下几个要点：

  • 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双-“进行转换等。
  • 永远不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取。
  • 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
  • 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
  • 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
  • SQL注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用SQL注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

可以总结为 :

  • 包装提示 (白)
  • 连接权限 (莲)
  • 校验输入 (教)