mysql版本概念_MySQL基本概念

1、数据类型

整型及浮点型

TINYINT 1字节

SMALLINT 2字节

MEDIUM 3字节

INT 4字节

BIGINT 8字节

FLOAT 4字节

DOUBLE 8字节

日期时间型

YEAR 1字节 年份

DATE 3字节 年月日

TIME 3字节 时分秒

DATETIME 8字节 年月日时分秒

TIMESTAMP 8字节 年月日时分秒

字符型

CHAR(M) 最多包含255个字节，固定长度，M个字符，浪费空间

VARCHAR(M) 最多包含65535个字节，可变长度，实际分配空间小于等于M个字符，容易导致碎片。此外，对于定义长度不同的类型，即使实际分配空间相同，消耗的内存页不同，所以不能任意定义长度。

UTF-8编码一个汉字占3个字节，GBK编码一个汉字占2个字节

2、事务

定义

事务由一组SQL语句组成，且具有原子性，事务中的语句要么全部执行成功，要么全部执行失败。

特点

事务具有四个特性，即ACID，包括原子性、一致性、隔离性、持久性。

●原子性(atom)

事务中的所有操作，要么全部执行成功，要么全部失败回滚。

●一致性(consistency)

数据库总是从一个一致性状态转移到另一个一致性状态，不存在中间态，事务只有在提交后，所做的修改才会保存到数据库。

●隔离性(isolation)

通常来说，事务在提交之前，对数据所做的修改对其他事务是不可见的。

●持久性(durabilty)

事务一旦提交，所做的修改则会永久保存在数据库中

使用

START TRANSCATION; //开启事务

SQL语句

COMMIT;//提交事务

MySQL中默认采用自动提交模式，即：如果不是显示的开始一个事务，则每个查询都会被当做一个事务执行提交操作。可以通过SHOW VARIABLES LIKE ‘AUTOCOMMIT’;查询自动提交是否开启。通过SET AUTOCOMMIT=1|0开启或关闭自动提交。当关闭自动提交后，只有显式的执行COMMIT或ROLLBACK，事务才结束。

3、隔离级别

READ UNCOMMITTED(未提交读)

事务中对记录做的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的修改会导致脏读。实际中很少用到。

READ COMMITED(提交读)

事务只有在提交后，所做的修改才对其他事务可见，会导致不可重复读。在一次事务中，多次读取同一数据，由于其他事务对数据的修改，读到的结果可能不同，这就是不可重复读。

REPEATABLE READ(可重复读)

在一次事务中，多次读取同一数据，读到的结果相同，但不能解决幻读的问题。幻读即：当某个事务查询某一范围的记录时，由于另一个事务让同时也在向此范围插入记录，导致事务中查询到了不存在的记录，出现幻觉。可重复读是MySQL的默认隔离级别。

SERIALIZABLE(可串行化)

可串行化是最高隔离级别，通过在读取每行数据时加锁，强制事务串行执行，解决了幻读的问题，但同时会导致时间上的浪费。

总结

隔离级别 脏读 不可重复读 幻读 加锁读

READ UNCOMMITTED 是 是 是 否

READ COMMITED 否 是 是 否

REPEATABLE READ 否 否 是 否

SERIALIZABLE 否 否 否 是

注意：InnoDB支持多版本并发控制(MVCC)，默认级别是可重复读，但通过间隙锁避免了幻读的出现。

SELECT @@global.tx_isolation;或SELECT @@session.tx_isolation;分别查询全局和当前会话隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};或通过修改my.ini配置文件中

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

修改隔离级别

4、乐观锁与悲观锁

悲观锁

定义：在数据处理过程中，使数据处于锁定状态。悲观锁的实现需要数据库提供锁机制。

流程

●在修改任意记录之前，为记录加上排它锁。

●如果加锁失败，则进入等待状态或剖出异常。

●如果加锁成功，则可以对记录进行修改，事务完成后释放锁。

●如果期间有其他事务修改记录，则需要等待解锁或抛出异常。

优点

增加数据处理安全性

缺点

●处理加锁机制，增加系统开销，提高死锁的几率

●一旦锁定了某条记录，其他相应事务必须等待，降低并发

●无论是行锁还是表锁，加锁时间长，降低并发

乐观锁

定义：修改数据前不会加锁，只有在事务提交时才会加锁并检查修改是否发生冲突，如果发生冲突，则会提示错误信息。乐观锁不会使用数据库提供的锁机制，通过系统实现，通过记录版本号或时间戳的方式实现。

流程

在读取数据时同时获得数据的时间戳或版本号，在事务提交时检查此时的版本号或时间戳是否与刚开始的相同，如果相同，则事务提交成功，同时更新数据的时间戳或版本号，如果不同，则事务提交失败。

优点

并发性能好

5、死锁

定义

当多个事务运行时，事务占用彼此需要的资源，导致所有事务无法完成。

处理方法

InnoDB引擎会选择持有最少行级排它锁的事务进行部分或完全回滚。

数据库中事务分为加锁阶段和解锁阶段。

加锁阶段：在对数据进行读操作之前获取共享锁(其他事务可以继续加共享锁，但不能加排它锁)，在写操作之前获取排它锁(其他事务不能在获得任何锁)。如果加锁不成功，事务则进入等待状态，直到获得锁才继续执行。

解锁阶段：在事务提交时，释放占用的锁。

6、日志分类

mysql中主要包含四中日志，分别是：错误日志、慢查询日志、二进制日志、查询日志。

错误日志：记录数据库发生错误的信息

慢查询日志：记录执行时间超过指定阈值的SQL语句

二进制日志：记录数据发生更改的信息

查询日志：记录对数据库执行的一切操作

事务日志：存储引擎修改数据时只需要修改内存中数据拷贝，然后把修改过程写到磁盘中的事务日志，日志持久化以后，内存中修改的数据在写入磁盘。由于事务日志采用的是追加的方式，所以写事务日志非常快。

7、触发器

定义：当触发器所在的表发生指定事件时，触发触发器执行相应的操作。

创建触发器

CREATE TRIGGER trigger_name

trigger_time

trigger_event ON tbl_name

FOR EACH ROW

trigger_stmt

trigger_name:触发器的名字

trigger_time：何时触发触发器，取值为 BEFORE 或 AFTER；

trigger_event：触发器所在的表发生什么事件时，触发触发器，值可以是INSERT、UPDATE、DELETE

tbl_name：在该表上建立触发器

trigger_stmt:触发器具体执行的语句，其中常常使用NEW和OLD关键字

NEW和OLD表示触发器所在表中触发了触发器的那一行数据，NEW表示新数据，OLD表示旧数据

在INSERT型触发器中，NEW表示新数据

在UPDATE型数据表中，NEW表示新数据，OLD表示旧数据

在DELETE数据表中，OLD表示删除的旧数据

使用方法：NEW.column_name

查看触发器：SHOW TRIGGERS FROM database_name;

删除触发器：DROP TRIGGER trigger_name;

触发器执行顺序

BEFORE触发器执行失败，则相应SQL语句无法成功执行

AFTER触发器执行失败，则SQL语句回滚

SQL语句执行失败，AFTER触发器不会触发

8、视图

定义：视图是基于sql语句查询结果的虚拟表，本身并不存储数据，而是引用其他数据表中的数据。当使用视图时，视图通过引用基本表中的数据提供查询所需数据。视图数据被修改时，基本表中的数据相应也会修改，基本表中的数据被修改后，视图也会发生变化。

视图种类：MERGE、TEMPLATE、UNDEFINED.

MERGE

查询语句会先与视图声明语句合并，然后执行合并后的语句。

TEMPLATE

视图的结果存放在一张临时表中，查询语句使用临时表中的数据。

UNDEFINED

创建视图时，如果没有指定视图类型，mysql优先使用MERGE类型，其次使用TEMPLATE类型

优点

●视图通过隐藏数据，可以增加数据安全性。

●简化复杂的操作，比如经常用到某个子查询，这时可以创建一个视图，将视图用到查询语句中

注意

●不能将视图与触发器关联在一起。

●存储过程中不能修改视图

●视图所对应的表不能是临时表

●不能给视图添加索引

●创建视图时，不能使用用户变量

●TEMPLATE型视图无法进行更新

●视图中的数据必须和基本表中的数据时一一对应时，视图才可以更新

创建视图

CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

视图也是一种表，所以创建时不能与已有表重名！

WITH CHECK OPTION 表示如果更新视图数据后，更新后的数据不再满足视图定义，即不存在在视图中，那么不允许更新视图。

修改视图

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION];

删除视图

DROP VIEW view_name;

9、游标

定义：游标是由sql语句查询得到的结果集，通过fetch可以访问结果集中的每行数据。

注意：游标只能用于存储过程；创建游标后，必须先打开游标后，才能使用游标；使用完游标后必须关闭游标

创建游标

CREATE PROCEDURE procedure_name

BEGIN

DECLARE cursor_name CURSOR FOR select_statament

END

打开游标：OPEN cursor_name;

关闭游标：CLOSE cursor_name;

访问数据：FETCH cursor_name INTO variable_name;FETCH语句访问每一行数据，并自动移动游标中的指针，下一条FETCH语句则访问下一行数据。

eg：

CREATE PROCEDURE procedure_name

BEGIN

DECLARE variable_name variable_class；

DECLARE cursor_name CURSOR FOR select_statament

OPEN cursor_name;

FETCH cursor_name INTO variable_name;

CLOSE cursor_name;

END