MySQL(二)：特性详解

一、事务隔离

事务隔离（isolation）定义了数据库系统中一个操作产生的影响什么时候以哪种方式可以对其他并发操作可见。隔离是事务ACID (原子性、一致性性、隔离性、持久性)四大属性中的一个重要属性。

并发控制(Concurrency control)

并发控制描述了数据库处理隔离以保证数据正确性的机制。为了保证并行事务执行的准确执行数据库和存储引擎在设计的时候着重强调了这一点。典型的事务相关机制限制数据的访问顺序(执行调度)以满足可序列化和可恢复性。限制数据访问意味着降低了执行的性能，并发控制机制就是要保证在满足这些限制的前提下提供尽可能高的性能。经常在不损害正确性的情况下，为了达到更好的性能，可序列化的的要求会减低一些，但是为了避免数据一致性的破坏，可恢复性必须保证。

两阶段锁是关系数据库中最常见的提供了可序列化 和可恢复性的并发控制机制，为了访问一个数据库对象，事务首先要获得这个对象的锁。对于不同的访问类型（如对对象的读写操作）和锁的类型，如果另外一个事务正持有这个对象的锁，获得锁的过程会被阻塞或者延迟。

隔离级别(Isolation levels)

• 串行化(Serializable)：基于锁机制并发控制的DBMS实现可序列化要求在选定对象上的读锁和写锁保持直到事务结束后才能释放。在SELECT的查询中使用一个“WHERE”子句来描述一个范围时应该获得一个“范围锁(range-locks)”。这种机制可以避免“幻读(phantom reads)”现象。

• 可重读(Repeatable reads)：基于锁机制并发控制的DBMS需要对选定对象的读锁(read locks)和写锁(write locks)一直保持到事务结束，但不要求“范围锁(range-locks)”，因此可能会发生“幻读(phantom reads)”

• 读提交(Read committed)：基于锁机制并发控制的DBMS需要对选定对象的写锁(write locks)一直保持到事务结束，但是读锁(read locks)在SELECT操作完成后马上释放(因此“不可重复读”现象可能会发生，见下面描述)。不要求“范围锁(range-locks)”。

• 读未提交(Read uncommitted)：是最低的隔离级别。允许脏读(dirty reads)，事务可以看到其他事务“尚未提交”的修改。

读现象(Read phenomena)

• 脏读(Dirty reads)：当一个事务允许读取另外一个事务修改但未提交的数据时，就可能发生脏读(dirty reads)。

• 不可重复读(non-repeatable read)：在一次事务中，当一行数据获取两遍得到不同的结果表示发生了“不可重复读(non-repeatable read)”。

• 幻读(phantom read)：在事务执行过程中，当两个完全相同的查询语句执行得到不同的结果集。这种现象称为“幻读(phantom read)”。

隔离级别对应的读现象

二、数据类型修饰符

char,varchar和text几个字符型常用的属性修饰符：

• NOT NULL：非空约束

• NULL：允许为空

• DEFAULT 'string'：默认值；不适用text类型

• CHARACTER SET '字符集'：

  mysql> show variables like '%char%';

  mysql> show character set;

• COLLATION 'string'：排序规则

  mysql> show collation;

binary,varbinary和blob几种字符型常用的属性修符：

• NOT NULL

• NULL

• DEFAULT：不适用于blob

整型的常用属性修饰符：

• AUTO_INCREMENT：自动增长

   前提：非空且惟一；支持索引；非负值；

• UNSIGNED：无符号

• NULL

• NOT NULL

• DEFAULT

浮点型常用修饰符：

• NOT NULL

• NULL

• DEFAULT

• UNSIGNED

日期时间型的修饰符：

• NOT NULL

• NULL

• DEFAULT

SET和ENUM的修饰符：

• NOT NULL

• NULL

• DEFAULT

MySQL中的字符大小写：

• SQL关键字及函数名不区分字符大小写；

• 数据库、表和视图名称的大小写是否区分取决于底层OS及FS

• 存储过程、存储函数及事件调度器的名称不区分大小写；但触发器区分；

• 表的别名区分大小写；

• 对于字段中的数据；如果字段类型为binary类型；则区分大小写；非binary类型不区分大小写；

注意：为了提高命中率以提升效率；建议使用过程使用统一风格；要么大写；要么小写。

Examples:

mysql支持的sql_mode模式：ANSI、TRADITIONAL、STRICT_ALL_TABLES和STRICT_TRANS_TABLES。 

ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。

TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。

STRICT_ALL_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。

设定服务器变量的值：(仅用于支持动态的变量)

支持修改的服务器变量：

• 动态变量：可以MySQL运行时修改；

• 静态变量：于配置文件中修改其值；并重启后方能生效；

服务器变量冲其生效范围来讲；有两类：

• 全局变量：服务器级别；修改之后仅对新建立的会话生效；

• 会话变量：会话级别；仅对当前会话生效；退出失效；

  会话建立时；从全局继承各变量；

查看服务器变量：

mysql> show [{global|session}] variables [like ''];

mysql> select @@{global|seesion}.variable_name;

mysql> select * from information_schema.{global|session}_variables where variable_name='some_variable_name';

修改变量：

前提：默认修改全局变量仅管理员有权限

mysql> set {global|session} bariable_name='value';

三、创建库和表

库创建和删除：

表创建：

MyISAM表：每表有三个文件；都位于数据库目录中；

• tb_name.frm：表结构定义

• tb_name.MYD：数据文件

• tb_name.MYI：索引文件

InnoDB表：有两种存储方式innodb_file_per_table = ON

1、默认；每表有一个独立文件和一个多表共享的文件

• tb_name.frm：表结构的定义；位于数据库目录中

• ibdata#：共享的表空间文件；默认位于数据目录(datadir指向的位置)中；

2、独立的表空间：(建议使用该项)

• tb_name.frm:每表有一个表结构文件

• tb_name.ibd:一个特有的表空间文件

表修改：

四、实例

Examples

新建如下表（包括结构和内容）：

ID    Name          Age     Gender     Course

1     Ling Huchong   24      Male       Hamogong

2     Huang Rong    19      Female     Chilian Shenzhang

3     Lu Wushaung   18      Female     Jiuyang Shenggong

4     Zhu Ziliu     52      Male       Pixie Jianfa

5     Chen Jialuo   22      Male       Xianglong Shiba Zhang

6  Ou Yangfeng   70      Male       Shenxiang Bannuo Gong

1、新增字段：Class 字段定义自行选择；放置于Name字段后；

2、将ID字段名称修改为TID;

3、将Age字段放置最后；

此篇到此结束；后续继续更新。

本文转自Mr_陈 51CTO博客，原文链接：http://blog.51cto.com/chenpipi/1393601，如需转载请自行联系原作者