MySQL专题8之MySQL 事务、ALTER命令

鄂破机看

　　1、MySQL 事务
　　-  MySQL事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统上，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员的相关的信息。比如信箱，文章等等，这些数据操作语句就构成了一个事务。
　　-  在MySQL中只有使用Innodb数据库引擎的数据库或者表才支持事务。
　　-  事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
　　-  事务用来管理insert，update，delete语句。
　　-  一般来说，事务是必须满足4个条件（ACID）：Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）。
　　a、事务的原子性：一组事务，要么成功，要么撤回；b、稳定性：有非法数据（外键约束之类），事务撤回；c、隔离性：事务独立运行。一个事务处理后的结果，影响其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度；d、可靠性：软硬件崩溃后，InnoDB数据表会利用日志文件重构修改。可靠性和高速度不可兼得。innodb_flush_log_trx_commit选项决定什么时候把事务保存到日志中。
　　-  在MySQL命令行的默认设置中，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务必须使用命令BEGIN或者START TRANSACTION，或者执行命令SET AUTOCOMMIT = 0，用来禁止使用当前会话的自动提交。
　　-  事务控制语句：
　　a、BEGIN或者START TRANSACTION：显式地开启一个事务。
　　b、COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已经对数据库进行的所有修改称为永久性。
　　c、ROLLBACK：也可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

　　d、SAVEPOINT>
　　e、RELEASE SAVEPOINT>
　　f、ROLLBACK TO>　　g、SET TRANSACTION：用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
　　-  MySQL事务处理有两种方法：
　　a、用BEGIN，ROLLBACK COMMIT来实现：BEGIN为开始一个事务，ROLLBACK事务回滚，COMMIT事务确认。
　　b、直接用SET来改变MySQL的自动提交模式：SET AUTOCOMMIT=0为禁止自动提交；SET AUTOCOMMIT=1为开启自动提交。
　　-  事务测试：
mysql> use RUNOOB;　　
Database changed
　　
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
　　
Query OK, 0 rows affected (0.04 sec)
　　

　　
mysql> select * from runoob_transaction_test;
　　
Empty set (0.01 sec)
　　

　　
mysql> begin;  # 开始事务
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> insert into runoob_transaction_test value(5);
　　
Query OK, 1 rows affected (0.01 sec)
　　
mysql> insert into runoob_transaction_test value(6);
　　
Query OK, 1 rows affected (0.00 sec)
　　

　　
mysql> commit; # 提交事务
　　
Query OK, 0 rows affected (0.01 sec)
　　

　　
mysql>  select * from runoob_transaction_test;
　　
+------+
　　
| id   |
　　
+------+
　　
| 5    |
　　
| 6    |
　　
+------+
　　
2 rows in set (0.01 sec)
　　

　　
mysql> begin;    # 开始事务
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql>  insert into runoob_transaction_test values(7);
　　
Query OK, 1 rows affected (0.00 sec)
　　

　　
mysql> rollback;   # 回滚
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
　　
+------+
　　
| id   |
　　
+------+
　　
| 5    |
　　
| 6    |
　　
+------+
　　
2 rows in set (0.01 sec)
　　2、MySQL>
　　-  当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL>
　　-  在开始之前让我们先创建一张表，表名为：testalter_tbl。
root@host# mysql -u root -p password;　　
Enter password:*******
　　
mysql> use RUNOOB;
　　
Database changed
　　
mysql> create table testalter_tbl
　　
    -> (
　　
    -> i INT,
　　
    -> c CHAR(1)
　　
    -> );
　　
Query OK, 0 rows affected (0.05 sec)
　　
mysql> SHOW COLUMNS FROM testalter_tbl;
　　
+-------+---------+------+-----+---------+-------+
　　
| Field | Type    | Null | Key | Default | Extra |
　　
+-------+---------+------+-----+---------+-------+
　　
| i     | int(11) | YES  |     | NULL    |       |
　　
| c     | char(1) | YES  |     | NULL    |       |
　　
+-------+---------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　-  删除，添加或者修改表字段
　　-  如下命令使用ALTER命令及DROP子句来删除以上创建表的i字段：
mysql> ALTER TABLE testalter_tbl  DROP i;　　-  如果数据表中值剩下一个字段则无法使用DROP来删除字段。
　　-  MySQL中使用ADD子句来向数据表中添加列，如下实例在表testalter_tbl中添加i字段，并定义数据类型：
mysql> ALTER TABLE testalter_tbl ADD i INT;　　-  执行以下命令后，i字段会自动添加到数据表字段的末尾：
mysql> SHOW COLUMNS FROM testalter_tbl;　　
+-------+---------+------+-----+---------+-------+
　　
| Field | Type    | Null | Key | Default | Extra |
　　
+-------+---------+------+-----+---------+-------+
　　
| c     | char(1) | YES  |     | NULL    |       |
　　
| i     | int(11) | YES  |     | NULL    |       |
　　
+-------+---------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　-  如果你需要指定新增字段的位置，可以使用MySQL提供的关键字FRIST（设定为第一列），AFTER 字段名（设定位于某个字段之后）。
　　-  尝试以下ALTER TABLE 语句，在执行成功后，使用SHOW COLUMNS查看表结构的变化：
ALTER TABLE testalter_tbl DROP i;　　
ALTER TABLE testalter_tbl ADD i INT FIRST;
　　
ALTER TABLE testalter_tbl DROP i;
　　
ALTER TABLE testalter_tbl ADD i INT AFTER c;
　　-  FIRST和AFTER关键字只占用于ADD子句，所以如果你想要重置数据表字段的位置就需要先使用DROP删除字段然后使用ADD来添加字段并设置位置。
　　-  修改字段类型以及名称：如果需要修改字段类型以及名称，你可以在ALTER命令中使用MODIFY或者CHANGE子句。
　　-  例如，把字段c的类型从CHAR(1)改为CHAR(10)，可以执行以下命令：
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);　　-  使用CHANGE子句，语法上有很大的不同，在CHANGE关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名以及类型，尝试以下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;mysql> ALTER TABLE testalter_tbl CHANGE j j INT;　　-  ALTER TABLE对 Null值和默认值的影响：当你修改字段时，你可以指定是否包含 NULL或者是否设置默认值。
　　-  以下实例，指定字段j为NOT NULL并且默认值为100：
mysql> ALTER TABLE testalter_tbl　　
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;
　　-  如果你不设置默认值，MySQL会自动设置该字段默认为NULL。
　　-  修改字段默认值：你可以使用ALTER来修改字段的默认值，尝试以下实例：
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;　　
mysql> SHOW COLUMNS FROM testalter_tbl;
　　
+-------+---------+------+-----+---------+-------+
　　
| Field | Type    | Null | Key | Default | Extra |
　　
+-------+---------+------+-----+---------+-------+
　　
| c     | char(1) | YES  |     | NULL    |       |
　　
| i     | int(11) | YES  |     | 1000    |       |
　　
+-------+---------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　-  你也可以使用ALTER命令以及DROP子句来删除字段的默认值，如下实例：
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;　　
mysql> SHOW COLUMNS FROM testalter_tbl;
　　
+-------+---------+------+-----+---------+-------+
　　
| Field | Type    | Null | Key | Default | Extra |
　　
+-------+---------+------+-----+---------+-------+
　　
| c     | char(1) | YES  |     | NULL    |       |
　　
| i     | int(11) | YES  |     | NULL    |       |
　　
+-------+---------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　
Changing a Table Type:
　　-  修改数据表类型，可以使用ALTER命令以及TYPE子句来完成。尝试以下实例，我们将表testalter_tbl的类型修改为MYISAM。
　　-  注意：查看数据表类型可以使用SHOW TABLE STATUS语句。
mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;　　
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
　　
*************************** 1. row ****************
　　
           Name: testalter_tbl
　　
           Type: MyISAM
　　
     Row_format: Fixed
　　
           Rows: 0
　　
Avg_row_length: 0
　　
    Data_length: 0
　　
Max_data_length: 25769803775
　　
   Index_length: 1024
　　
      Data_free: 0
　　
Auto_increment: NULL
　　
    Create_time: 2007-06-03 08:04:36
　　
    Update_time: 2007-06-03 08:04:36
　　
     Check_time: NULL
　　
Create_options:
　　
        Comment:
　　
1 row in set (0.00 sec)
　　-  修改表名：如果需要修改数据表的名称，可以在ALTER TABLE语句中使用RENAME子句来实现。
　　-  尝试以下实例将数据表testalter_tbl重命名为alter_tbl：
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;　　-  ALTER命令还可以用来创建以及删除MySQL数据表的索引，该功能会在接下来的MySQL专题中介绍。
　　3、附加说明：数据库事务四种隔离级别
　　-  在数据库操作中，为了有效保证并发读取数据的正确性，提出了事务隔离级别的概念。
　　-  数据库事务的隔离级别有4个，由低到高依次为READ UNCOMMITED（未授权读取，读未提交）、READ COMMITTED（授权读取，读提交）、REPEATATBLE READ（可重复读取）、Serializable（序列化）。这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
　　-  问题的提出：之所以提出事务隔离级别这个概念，是以为在一个事务执行过程中，可能会出现以下几种情况：
　　a、更新丢失：两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
　　b、脏读：一个事务读取到了另一个事务未提交的数据操作结果。
　　c、不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到不同的结果。包括以下两种情况：虚读，事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读取该数据时得到与前一次不同的值；幻读（Phantom Reads），事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。
　　-  下面介绍这几种书屋隔离级别的区别以及可能会出现的问题：
　　a、READ UNCOMMITTED：如果一个事务已经开始写数据，则另一个事务不允许同时进行写操作，但是允许其他事务读取此行数据，该隔离级别可以通过“排他写锁”实现。
　　-  避免更新丢失，却可能会出现脏读。也就是说事务B读取到了事务A未提交的数据。
　　b、READ COMMITTED：读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务禁止其他事务访问该行。
　　-  该隔离级别避免了脏读，但是却可能会出现不可重复读的问题。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
　　c、Repeatable read（可重复读取）：读取数据的事务将禁止写事务（但是允许读事务），写事务则禁止任何其它事务。
　　-  这避免了不可重复读和脏读，但是有时候会出现幻读的问题。这可以通过“共享读锁”和“排他写锁”来实现。
　　d、Serializable（序列化）：提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但是不能够并发执行。如果仅仅是通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能最低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读、还避免了幻像读。
　　-  总结：隔离级别越高，越能够保证数据的完整性和一致性，但是对并发性能的影响越大。对于大多数的应用程序来说，可以优先考虑吧数据库系统的隔离级别设置为READ COMMITTED。它能够避免脏读，而且具有较好的并发性能。尽管他会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或者乐观锁来控制。
　　-  大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle，而Mysql的默认隔离级别就是Repeatable read。