MySQL的进阶实战篇

http://blog.csdn.net/javazejian/article/details/69857949 出自【zejian的博客】关联文章:MySQL的初次见面礼基础实战篇MySQL的进阶实战篇本篇上一篇博文MySQL的初次见面礼基础实战篇的延续，是MySQL的进阶内容的记录，本篇主要知识点如下：进阶实战篇进

超罗

9538人浏览 · 2017-04-25 09:17:48

超罗 · 2017-04-25 09:17:48 发布

http://blog.csdn.net/javazejian/article/details/69857949

出自【zejian的博客】

关联文章:

MySQL的初次见面礼基础实战篇

MySQL的进阶实战篇

本篇上一篇博文MySQL的初次见面礼基础实战篇的延续，是MySQL的进阶内容的记录，本篇主要知识点如下：

进阶实战篇

进阶实战篇

mysql视图机制

什么是视图

视图是一张虚拟的表，为什么是虚拟呢？因为视图与数据库中存在的表不太一样，前面我们创建的4张表都是包含数据的，如用户信息，订单信息等，而视图则是不包含数据的，下面通过一个例子来演示视图,下面的sql是查询王五的所有订单情况，需要关联到orders表、orderdetail表、items表、user表

mysql> select u.username , o.number , tm.name as itemsName , tm.price , od.items_num from 
    ->  (
    ->  (orders as o inner join orderdetail as od on o.id = od.orders_id ) 
    ->  inner join items as tm on od.items_id = tm.id 
    ->  )
    ->  inner join user as u on o.user_id = u.id
    ->  where username='王五';
+----------+---------+-------------+---------+-----------+
| username | number  | name        | price   | items_num |
+----------+---------+-------------+---------+-----------+
| 王五     | 1000010 | MacBook Air |  8298.9 |         1 |
| 王五     | 1000010 | MacBook Pro | 10982.0 |         3 |
| 王五     | 1000011 | 背包         |   200.0 |         4 |
| 王五     | 1000011 | MacBook Pro | 10982.0 |         3 |
+----------+---------+-------------+---------+-----------+
4 rows in set (0.01 sec)

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

显然数据已如期查询出来了，但是我们发现任何需要这个数据的人都必须了解相关联的表结构，并且需要知道如何创建查询和对表进行联结，为了检索其他用户的相同数据必须修改Where条件并带上一大段关联查询的sql语句。是的，每次这样的操作确实挺麻烦的，假如现在可以把这个除了where条件外的sql查询出来的数据包装成一个名为user_order_data的虚拟表，就可以使用以下方式检索出数据了。

select * from user_order_data where username='王五';
 
 1
 
 1

按这样的方式每次查询不同的用户只需修改where条件即可也不同再写那段看起有点恶心的长sql了，而事实上user_order_data就是一张视图表，也可称为虚拟表，而这就是视图最显著的作用了。

视图创建与使用

了解完什么是视图后，我们先来看看如何创建视图和使用视图,使用以下语法：

CREATE VIEW 视图名(列名,...) AS SELECT 语句
 
 1
 
 1

现在我们使用前面关联查询的orders表、orderdetail表、items表、user表来创建视图user_order_data

-- 创建视图虚拟表user_order_data
mysql>  create view user_order_data(username,number,itemname,price,items_num) as select u.username , o.number , tm.name , tm.price , od.items_num from 
    ->  (
    ->  (orders as o inner join orderdetail as od on o.id = od.orders_id ) 
    ->  inner join items as tm on od.items_id = tm.id 
    ->  )
    ->  inner join user as u on o.user_id = u.id;
Query OK, 0 rows affected (0.01 sec)

-- 使用视图
mysql> select * from user_order_data;
+----------+---------+-------------+---------+-----------+
| username | number  | itemname    | price   | items_num |
+----------+---------+-------------+---------+-----------+
| 王五     | 1000010 | MacBook Air |  8298.9 |         1 |
| 王五     | 1000010 | MacBook Pro | 10982.0 |         3 |
| 王五     | 1000011 | 背包         |   200.0 |         4 |
| 王五     | 1000011 | MacBook Pro | 10982.0 |         3 |
+----------+---------+-------------+---------+-----------+
4 rows in set (0.01 sec)

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

可以看出除了在select语句前面加上create view user_order_data as外，其他几乎没变化。在使用视图user_order_data时，跟使用数据库表没啥区别，因此以后需要查询指定用户或者所有用户的订单情况时，就不用编写长巴巴的一段sql了，还是蛮简洁的。除了上述的方式，还可以将视图虚拟表的字段别名移动到查询字段后面：

 CREATE OR REPLACE VIEW user_order_data
 AS
 SELECT
 u.username as username, 
 o.number as number , 
 tm.name as name , 
 tm.price as price , 
 od.items_num as items_num
 FROM
 (
 (orders as o INNER JOIN orderdetail as od ON o.id = od.orders_id ) 
 INNER JOIN items as tm ON od.items_id = tm.id 
 )
 INNER JOIN user as u ON o.user_id = u.id
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意这里使用了CREATE OR REPLACE VIEW语句，意思就是不存在就创建，存在就替换。如果想删除视图可以使用以下语法：

DROP VIEW 视图名称
 
 1
 
 1

在使用视图的过程还有些需要注意的点，如下

与创建表一样，创建视图的名称必须唯一
创建视图的个数并没限制，但是如果一张视图嵌套或者关联的表过多，同样会引发性能问题，在实际生产环节中部署时务必进行必要的性能检测。
在过滤条件数据时如果在创建视图的sql语句中存在where的条件语句，而在使用该视图的语句中也存在where条件语句时，这两个where条件语句会自动组合
order by 可以在视图中使用，但如果从该视图检索数据的select语句中也含有order by ，那么该视图中的order by 将被覆盖。
视图中不能使用索引，也不能使用触发器(索引和触发器后面会分析)
使用可以和普通的表一起使用，编辑一条联结视图和普通表的sql语句是允许的。

关于使用视图对数据的进行更新(增删改)，因为视图本身并没有数据，所以这些操作都是直接作用到普通表中的，但也并非所有的视图都可以进行更新操作,如视图中存在分组(group by)、联结、子查询、并(unoin)、聚合函数(sum/count等)、计算字段、DISTINCT等都不能对视图进行更新操作，因此我们前面的例子也是不能进行更新操作的，事实上，视图更多的是用于数据检索而更新，因此对于更新也没有必要进行过多阐述。

视图的本质

到此对于视图的创建和使用都比较清晰了，现在准备进一步认识视图的本质，前面我们反复说过，视图是一张虚拟表，是不带任何数据的，每次查询时只是从普通表中动态地获取数据并组合，只不过外表看起来像一张表罢了。其原理通过下图便一目了然：

事实上有些时候视图还会被用于限制用户对普通表的查询操作，对于这类用户只赋予对应视图的select操作权限，仅让他们只能读取特定的行或列的数据。这样我们也就不用直接使用数据库的权限设置限制行列的读取，同时也避免了权限细化的麻烦。

高效索引

使用索引的理由

由于mysql在默认情况下，表中的数据记录是没有顺序可言的，也就是说在数据检索过程中，符合条件的数据存储在哪里，我们是完全不知情的，如果使用select语句进行查询，数据库会从第一条记录开始检索，即使找到第一条符合条件的数据，数据库的搜索也并不会因此而停止，毕竟符合条件的数据可能并不止一条，也就是说此时检索会把表中的数据全部检索一遍才结束，这样的检索方式也称为全表扫描。但假设表中存在上百上千万条数据呢？这样的检索效率就十分低了，为了解决这个问题，索引的概念就诞生了，索引是为检索而存在的。如一些书的末尾一般会提供专门附录索引，指明了某个关键字在正文中的出现的页码位置或章节的位置，这样只要找到对应页面就能找到要搜索的内容了，数据库的索引也是类似这样的原理，通过创建某个字段或者多个字段的索引，在搜索该字段时就可以根据对应的索引进行快速检索出相应内容而无需全表扫描了。

索引的创建及其基本类型

MySQL 索引可以分为单列索引、复合索引、唯一索引、主键索引等。下面分别介绍

单列索引

单列索引，也称为普通索引，单列索引是最基本的索引，它没有任何限制，创建一个单列索引，语法如下:

CREATE INDEX index_name ON tbl_name(index_col_name);
 
 1
 
 1

其中index_name为索引的名称，可以自定义，tbl_name则指明要创建索引的表，而index_col_name指明表中那一个列要创建索引。当然我们也可以通过修改表结构的方式添加索引：

ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name);
 
 1
 
 1

还可在创建表时直接指定：

-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32)  NOT NULL ,
......  -- 其他字段
PRIMARY KEY (`id`),
indexName (name(32))  -- 创建name字段索引
);
 
 1
2
3
4
5
6
7
8
 
 1
2
3
4
5
6
7
8

下面为user表的username字段创建单列索引：

-- 创建username字段的索引名称为index_name，这就是基础的索引创建
mysql> create index index_name on user(username);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看user表存在的索引 \G 代表优化显示方式
mysql> show index from user \G;
*************************** 1. row ***************************
        Table: user     表名称
   Non_unique: 0        索引是否允许重复(1:可重复,0:不可重复)
     Key_name: PRIMARY  索引名称
 Seq_in_index: 1        
  Column_name: id       表中被创建索引的字段名称
    Collation: A        是否排序(A:升序,Null:不排序)
  Cardinality: 9        
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE    索引的类型
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: user
   Non_unique: 1          索引是否允许重复(1:可重复,0:不可重复)
     Key_name: index_name 索引名称
 Seq_in_index: 1
  Column_name: username   表中被创建索引的字段名称
    Collation: A          是否排序(A:升序,Null:不排序)
  Cardinality: 9
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE      索引的类型
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

可见user表中的username字段的索引已被创建，在使用show index from user查看user的索引字段时，我们发现id字段也创建了索引，事实上，当user表被创建时，主键的定义的字段id就会自动创建索引，这是一种特殊的索引，也称为丛生索引，而刚才创建的index_name索引属于单列索引

复合索引

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。创建一个复合索引的语法如下：

-- index_name代表索引名称，而index_col_name1和index_col_name2为列名，可以多个
CREATE INDEX index_name ON tbl_name(index_col_name1,index_col_name2,...);

-- 同样道理，也可以通过修改表结构的方式添加索引，
ALTER TABLE tbl_name ADD INDEX index_name ON (index_col_name1,index_col_name2,...);

-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32)  NOT NULL ,
'pinyin' varchar(32) ,
......  -- 其他字段
PRIMARY KEY (`id`),
indexName (name(32),pinyin(32)) 
);
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

为了方便演示，为user表添加名称拼音字段（pinyin）

-- 添加新字段pinyin
mysql> alter table user add pinyin varchar(32) after username;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 1
2
3
4
 
 1
2
3
4

现在利用username和pinyin 两个字段为user表创建复合索引，先删除之前为username创建的索引，删除索引语法如下：

DROP INDEX 索引名称 ON 表名
 
 1
 
 1

删除username的索引

-- 删除index_name索引
mysql> drop index index_name on user;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 查看user的索引
mysql> show index from user \G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

ok,index_name 索引已被删除，现在联合username和pinyin创建索引如下：

-- 创建新索引多列组成，index_pinyin为复合索引名称
mysql> create index index_pinyin on user(username,pinyin);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 这里省略主键索引
mysql> show index from user \G;
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: index_pinyin   相同的索引名称
 Seq_in_index: 1                   显示列的顺序
  Column_name: username
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: user
   Non_unique: 1
     Key_name: index_pinyin 相同的索引名称
 Seq_in_index: 2                 显示列的顺序
  Column_name: pinyin
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

像这样由两个以上组成的索引称为复合索引，由于是复合索引因此索引的名称都相同，注意Seq_in_index代表索引字段的顺序，前面我们说过在查询条件中使用了复合索引的第一个字段（这里指username），索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

唯一索引

创建唯一索引必须指定关键字UNIQUE，唯一索引和单列索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许有空值。对于多个字段，唯一索引规定列值的组合必须唯一。如创建username为唯一索引，那么username的值是不可以重复的，

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON tbl_name(index_col_name[,...]);

-- 添加（通过修改表结构）
ALTER TABLE tbl_name ADD UNIQUE INDEX index_name ON (index_col_name[,...]);


-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(32)  NOT NULL ,
......  -- 其他字段
PRIMARY KEY (`id`),
UNIQUE indexName (name(32)) 
);
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

下面为user表的username字段创建唯一索引：

-- 仅为演示
create unique index idx_name on user(username);
 
 1
2
 
 1
2

事实上这里讲username设置为唯一索引是不合理的，毕竟用户可能存在相同username，因此在实际生产环节中username是不应该设置为唯一索引的。否则当有相同的名称插入时，数据库表将会报错。

主键索引

主键索引也称丛生索引，是一种特殊的唯一索引，不允许有空值。创建主键索引语法如下：

ALTER TABLE tbl_name ADD PRIMARY KEY(index_col_name);
 
 1
 
 1

一般情况下在创建表时，指明了主键时，主键索引就已自动创建了，因此无需我们手动创建。

-- 创建表时直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
......  -- 其他字段
PRIMARY KEY (`id`), -- 主键索引
);
 
 1
2
3
4
5
6
 
 1
2
3
4
5
6

索引的设计

where子句中的列可能最适合做为索引
不要尝试为性别或者有无这类字段等建立索引(因为类似性别的列，一般只含有“0”和“1”，无论搜索结果如何都会大约得出一半的数据)
如果创建复合索引，要遵守最左前缀法则。即查询从索引的最左前列开始，并且不跳过索引中的列
不要过度使用索引。每一次的更新，删除，插入都会维护该表的索引，更多的索引意味着占用更多的空间
使用InnoDB存储引擎时，记录(行)默认会按照一定的顺序存储，如果已定义主键，则按照主键顺序存储，由于普通索引都会保存主键的键值，因此主键应尽可能的选择较短的数据类型，以便节省存储空间
不要尝试在索引列上使用函数。

ok~，关于索引暂且聊到这，由于索引细说起来内容还是相当多，本篇只对索引的主要知识点进行说明，让我们对索引有个清晰的了解并学会如何去使用索引。

存储过程

为什么需要存储过程

迄今为止，我们所使用的大多数SQL语句都针对一个或多个表的单条语句，当需要通过处理流程来达到预期目标时，单条sql语句就很难做到了，这是因为sql语句无法编写处理流程的语句，所有的sql都只能通过一个个命令执行，比如想循环执行某个SQL语句，对于没有处理流程的sql显然是无法实现的，此时就需要通过存储过程来达到目的了，简单的理解存储过程就是数据库中保存的一系列SQL命令的集合，也就是说通过存储过程就可以编写流程语句，如循环操作语句等，下面看看如何使用存储过程。

存储过程的创建与使用

可以通过以下语法创建存储过程：

CREATE PROCEDURE 存储过程名称(  参数的种类1 参数1 数据类型1
                            [,参数的种类2 参数2 数据类型2])
                            BEGIN
                                处理内容
                            END
 
 1
2
3
4
5
 
 1
2
3
4
5

存储过程的名称可以自由定义，但不可与存在的函数或存储过程名称重复，命名时建议以【sp_】开头，需要处理的内容则编辑在BEGIN和END之间。参数的种类分3种，分别是IN、OUT、INOUT，其中IN为输入参数类型，OUT为输出参数类型，而INOUT既是输入类型又是输出类型，下面我们创建一个存储过程，以达到对user表的用户名称进行模糊查询的目的，存储过程名称为sp_search_user：

-- 改变分隔符
DELIMITER //
-- 创建存储过程
mysql> create procedure sp_search_user (in name varchar(20));
    -> begin
    -> if name is null or name='' then
    ->    select * from user;
    -> else 
    ->    select * from user where username like name;
    -> end if;
    -> end
    -> // -- 执行sql
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ; -- 恢复分隔符
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

其中DELIMITER可以用于改变分隔符，由于存储过程中流程语句需要使用分号结尾与mysql命令行的sql语句结尾的分号冲突，于是改变分隔符为//，执行完存储过程后再恢复为分号即可。从存储过程创建语句中，我们设置一个name的输出参数语句并在begin与end之间编写了流程语句，当名称为空时查询所有用户否则按传入的条件查询。现在可以使用该存储过程了，调用语法如下：

CALL 存储过程名称(参数，....)
 
 1
 
 1

执行已创建存储函数

-- name传入null值，查询所有用户。
mysql> call sp_search_user(null);
+----+-----------+--------------+------------+------+--------------------+
| id | username  | pinyin  | birthday   | sex  | address            |
+----+-----------+--------------+------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL       | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 | 1    | 海南三亚           |
+----+-----------+--------------+------------+------+--------------------+
7 rows in set (0.00 sec)

-- 查询以name以任开头的用户
mysql> call sp_search_user('任%');
+----+-----------+-------------+------------+------+--------------------+
| id | username  | pinyin | birthday   | sex  | address            |
+----+-----------+-------------+------------+------+--------------------+
| 24 | 任传海    | renchuanhai | 1992-03-08 | 1    | 海南三亚           |
| 16 | 任在明    | renzaiming  | 1996-12-01 | 1    | 广东省广州市       |
+----+-----------+-------------+------------+------+--------------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

输入输出参数类型

前面提到了三种输入输出的参数类型，IN输入参数类型，OUT输出参数类型，而INOUT既是输入类型又是输出类型，所谓的IN输入参数类型就是把要传递的参数输入到存储过程的内部以便编写存储过程流程语句时可以使用。上述演示过的例子就是这种类型。关键字OUT则是指明相应参数用来从存储过程传出的一个值,也可以理解为存储过程的返回值，而对于INOUT则是两者结合体。现在我们创建一个存储过程，用于返回商品的最大值、最小值和平均值，命名为sp_item_price

DELIMITER //
-- 创建存储过程
mysql> create procedure sp_item_price(out plow decimal(8,2),
    ->                                out phigh decimal(8,2),
    ->                                out pavg decimal(8,2)
    ->                                )
    -> begin
    ->   select min(price) into plow from items;
    ->   select max(price) into phigh from items;
    ->   select avg(price) into pavg from items;
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)
-- 恢复分隔符
DELIMITER ;
-- 调用存储过程
mysql> call sp_item_price(@pricelow,@pricehigh,@priceavg);
Query OK, 1 row affected, 1 warning (0.00 sec)

-- 查询执行结果
mysql> select @pricelow ;
+-----------+
| @pricelow |
+-----------+
|     78.00 |
+-----------+
1 row in set (0.01 sec)

mysql> select @pricehigh ;
+------------+
| @pricehigh |
+------------+
|   10982.00 |
+------------+
1 row in set (0.00 sec)

mysql> select @priceavg ;
+-----------+
| @priceavg |
+-----------+
|   3773.65 |
+-----------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

正如我们所看到的，创建sp_item_price时，使用了3个out参数，在存储过程内部将会把执行结果分别存入这个三个变量中，存入关键字使用的是into，完成存储过程创建后，使用call sp_item_price(@pricelow,@pricehigh,@priceavg);调用sp_item_price，传入用于存储返回值的3个变量，注意mysql的用户变量必须以@开头，名称可自定义，但不能重复，调用完成后语句并没有显示任何数据，因为这些数据都存入了@pricelow,@pricehigh,@priceavg 三个变量中，我们可以使用select操作符查询这些变量。这里有点要明白的，上述存储过程中使用对输出变量的赋值语句：

SELECT 列名1，... INTO 变量名1，... FROM 表名 WHERE 语句等...
 
 1
 
 1

请注意如果检索出多个列名，与之对应的赋值变量也必须有多个。ok，对输入输出参数类型有了清晰的了解后，为了加深理解，我们创建一个同时存在输入输出参数的存储过程，输入订单号，计算该订单的订单总价，名称为sp_order_sum_price

DELIMITER //
-- 创建存储过程
mysql> create procedure sp_order_sum_price(in number int ,out ptotal decimal(8,2)) 
    -> begin 
    ->  select sum(price * items_num) from items inner join orderdetail as od  on items.id = od.items_id 
    ->  where od.orders_id=number 
    ->  into ptotal; --放到语句后面也可行
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)

-- 恢复分隔符
DELIMITER ;

-- 执行存储过程
mysql> call sp_order_sum_price(3,@priceTotal)；
Query OK, 1 row affected, 1 warning (0.00 sec)

-- 查询结果
mysql> select @priceTotal；
+-------------+
| @priceTotal |
+-------------+
|    41244.90 |
+-------------+
1 row in set (0.00 sec)

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

删除存储过程

如果创建存储过程有误，可以删除后重新创建，当然也可以修改，不过感觉不如删除重建来得更简单，删除存储过程可以使用以下语句

DROP PROCEDURE [IF EXISTS] 存储过程名称;
 
 1
 
 1

查看存储过程的状态

SHOW PROCEDURE STATUS [LIKE 'pattern']
 
 1
 
 1

如查看的sp_order_sum_price状态

-- 查看的sp_order_sum_price状态
mysql> SHOW PROCEDURE STATUS LIKE 'sp_order_sum_price' \G;
*************************** 1. row ***************************
                  Db: webshop
                Name: sp_order_sum_price
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2017-04-17 09:38:52
             Created: 2017-04-17 09:38:52
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看存储过程的创建语句

查看已创建的存储过程，可以使用以下语法

SHOW CREATE PROCEDURE 存储过程名;
 
 1
 
 1

如下查看前面已创建的sp_order_sum_price存储过程：

-- 查看sp_order_sum_price存储过程 \G代表格式输出格式
mysql> show create procedure sp_order_sum_price \G ;
*************************** 1. row ***************************
           Procedure: sp_order_sum_price -- 存储过程名称
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    -- 存储过程创建语句       
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_order_sum_price`(in number int ,out ptotal decimal(8,2))
begin 
 select sum(price * items_num) from items inner join orderdetail as od  on items.id = od.items_id  
 where od.orders_id=number 
 into ptotal;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

存储过程的流程控制语句

以下是存储过程中可以使用的流程控制语句

IF 条件语句

IF 条件语句，事实上与流行编程的条件语句类似，其语法如下：

IF 条件表达式1 THEN
    条件表达式1为true执行
[ELSEIF 条件表达式2 THEN
    条件表达式2为true执行
 ]
 [ELSE
      全部条件为false时执行]
 END IF；
 
 1
2
3
4
5
6
7
8
 
 1
2
3
4
5
6
7
8

简单案例：

mysql> create procedure sp_search_user (in name varchar(20));
    -> begin
    -> if name is null or name='' then
    ->    select * from user;
    -> else 
    ->    select * from user where username like name;
    -> end if;
    -> end
    -> // <------执行sql
 
 1
2
3
4
5
6
7
8
9
 
 1
2
3
4
5
6
7
8
9

多分支条件语句

CASE 表达式1
    WHEN 值1 THEN  表达式=值1时执行命令
    [WHEN 值N THEN  表达式=值N时执行该语句]
    [ELSE 上述值以外执行该语句]
END CASE  
 
 1
2
3
4
5
 
 1
2
3
4
5

简单案例

mysql > DELIMITER // 
mysql > CREATE PROCEDURE sp_insert_user (in parameter int) 
-> begin 
-> declare var int; -- 声明变量
-> set var=parameter+1; -- 设置变量值
-> case var 
-> when 0 then 
-> insert into user values(11,'高玉兰','gaoyulan','1999-01-01',0,null); 
-> when 1 then 
-> insert into user values(11,'高晓龙','gaoxiaolong','1999-01-01',1,null); 
-> else 
-> insert into user values(11,'高余粮','gaoyuliang','1999-01-01',1,null); 
-> end case; 
-> end
-> // 
mysql > DELIMITER ; 
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

repeat 循环控制语句

REPEAT 
   直至条件表达式为True时执行的语句
UNTIL 条件表达式 END REPEAT;
 
 1
2
3
 
 1
2
3

有时需要测试查询性能，需要准备假数据，使用循环语句来插入是个不错的选择，如下向user表中插入2000条数据。

mysql > DELIMITER // 
mysql > CREATE PROCEDURE sp_insert_user_repeat_2000 () 
-> begin 
->  declare n int; -- 声明变量
->  set n=0; -- 设置变量值
->  repeat 
->    insert into user values(30+n,concat('高余粮',n),'gaoyuliang','1999-01-01',1,null); 
->    set n=n+1; 
->  until n>=2000 end repeat; -- 结束条件
-> end
-> // 
Query OK, 0 rows affected (0.01 sec)
mysql > DELIMITER ; 

-- 执行存储过程
mysql> call  sp_insert_user_repeat_2000();
Query OK, 1 row affected (0.01 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

while循环控制语句

while 循环语句与repeat循环控制语句的区别是前者条件不符合一次循环体都不会执行，而后者无论条件是否符合，至少执行一次循环体，这点从前面的案例可以看出来。while循环语句语法如下：

WHILE 条件表达式 DO 
   系列语句
END WHILE
 
 1
2
3
 
 1
2
3

使用while循环语句实现前面的repeat一样的功能。

mysql > DELIMITER // 
mysql > CREATE PROCEDURE sp_insert_user_while_2000 () 
-> begin 
->   declare n int; -- 声明变量
->   set n=0; -- 设置变量值
->   while  n < 2000 do
->     insert into user values(30+n,concat('高余粮',n),'gaoyuliang','1999-01-01',1,null); 
->     set n=n+1; 
->   end while; 
-> end 
-> // 
Query OK, 0 rows affected (0.01 sec)
mysql > DELIMITER ; 
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
 
 1
2
3
4
5
6
7
8
9
10
11
12
13

定义变量

使用DECLARE定义局部变量

在流程语句的分析中，我们在存储过程中使用变量的声明与设置，由于这些变量也只能在存储过程中使用，因此也称为局部变量，变量的声明可以使用以下语法：

DECLARE 变量名[,变量名2...] 数据类型(type) [DEFAULT value];

-- 定义变量num，数据类型为INT型，默认值为10
DECLARE  num  INT  DEFAULT 10 ; 
 
 1
2
3
4
 
 1
2
3
4

其中， DECLARE关键字是用来声明变量的；变量名即变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。声明后，我们就可以在存储过程使用该变量，设置变量值可以使用以下语法：

SET 变量名1 = expr [, 变量名2 = expr] ... 
 
 1
 
 1

其中，SET关键字是用来为变量赋值的；expr参数是赋值表达式或某个值。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。除了这种赋值方式，前面我们还提到过使用SELECT…INTO语句为变量赋值，那也是可行的。

了解其他类型的变量

用户变量：以”@”开始，形式为”@变量名”，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，声明或者定义用户变量使用set语句，如 set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量。
全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.name，对所有客户端生效。只有具有super权限才可以设置全局变量。如下：
```
mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;
   
   1
2
   
   1
2
```

会话变量：只对连接的客户端有效。

mysql> SET SESSION sort_buffer_size=value;
   
   1
   
   1

使用DECLARE 定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法，可以简单理解为异常处理，这种方式可以提前预测可能出现的问题，并提出解决办法，从而增强程序健壮性，避免程序异常停止。MySQL通过DECLARE关键字来定义条件和处理程序。

定义条件

MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

-- 条件定义语法
DECLARE  condition_name  CONDITION  FOR  condition_value  

-- condition_value的定义格式
SQLSTATE [VALUE] sqlstate_value | mysql_error_code 
 
 1
2
3
4
5
 
 1
2
3
4
5

其中，condition_name表示条件的名称，condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。如常见的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146，简单案例如下：

-- 定义主键重复错误
-- ERROR 1062 (23000): Duplicate entry '60' for key 'PRIMARY'

-- 方法一：使用sqlstate_value  
DECLARE  primary_key_duplicate  CONDITION  FOR  SQLSTATE  '23000' ;  

-- 方法二：使用mysql_error_code  
DECLARE primary_key_duplicate CONDITION  FOR  1062 ; 
 
 1
2
3
4
5
6
7
8
 
 1
2
3
4
5
6
7
8

定义处理程序

前面定义的处理条件，可以在定义处理程序中使用,先了解一下定义语法：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement  
 
 1
2
 
 1
2

handler_type 参数的取值有三种：CONTINUE | EXIT | UNDO。

CONTINUE 表示遇到错误不进行处理，继续向下执行；
EXIT 表示遇到错误后马上退出；
UNDO 表示遇到错误后撤回之前的操作，但MySQL中暂时还不支持这种处理方式。

我们需要注意的是，大多数情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。由于MySQL目前并不支持UNDO操作。所以，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么就选择CONTINUE操作。

condition_value 参数指明错误类型，该参数有6个取值。语法如下：

-- condition_value的取值： 
SQLSTATE [VALUE] sqlstate_value |
mysql_error_code                |
condition_name                  | 
SQLWARNING                      | 
SQLEXCEPTION                    | 

 
 1
2
3
4
5
6
7
 
 1
2
3
4
5
6
7

sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。如常见的ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。与条件中参数是一样的。
condition_name是DECLARE定义的条件名称，就前面定义条件语句
NOT FOUND表示所有以02开头的sqlstate_value值。
SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。

sp_statement 参数表示要执行存储过程或函数语句。

以下定义了如何捕获和处理异常的简单例子

-- 捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且设置用户变量info。  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';  

-- 捕获mysql_error_code，如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且设置用户变量info。  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  

-- 先定义条件，然后定义处理程序调用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
-- 定义处理程序，并使用定义的can_not_find条件
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';  

-- SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  

-- NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  

-- SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作。  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR'; 
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

为了加深理解，下面我们编写一个存储过程用于添加用户，借此来了解定义处理程序的作用，如下：

mysql > DELIMITER //
mysql> create procedure sp_insert_user()
    -> begin 
    ->   set @n=1; -- 设置用户变量，用于标识程序运行到哪里一步停止
    ->   insert into user value(60,'小米','xiaomi',null,1,null);
    ->   set @n=2;
    ->   insert into user value(61,'小米2','xiaomi2',null,1,null);
    ->   set @n=3;
    -> end 
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql > DELIMITER ; 
-- 执行存储过程
mysql> call sp_insert_user();
-- 报错，因为主键60的用户已存在
ERROR 1062 (23000): Duplicate entry '60' for key 'PRIMARY'
-- 查询标识，显然在第一步时遇到错误就停止了执行
mysql> select @n ;
+------+
| @n   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

上述程序在执行完set @n=1;后就出错了，因为出现了重复的主键值，也就直接导致后面的程序也无法执行，现在我们编写一个处理程序，使用存储过程中即使出现2300错误也继续执行，如下：

mysql> create procedure insert_user_2()
    -> begin
         -- 定义条件
    ->   DECLARE  primary_key_exist  CONDITION  SQLSTATE '23000'  ;  
         -- 定义处理程序,出现2300错误继续执行，@m用于标识
    ->   DECLARE CONTINUE HANDLER FOR primary_key_exist SET @m = 1000;
    ->   set @n=1;
    ->   insert into user value(60,'小米','xiaomi',null,1,null);
    ->   set @n=2;
    ->   insert into user value(61,'小米2','xiaomi2',null,1,null);
    ->   set @n=3;
    -> end 
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql > DELIMITER ; 
-- 执行，并没有报错
mysql> call insert_user_2();
Query OK, 0 rows affected (0.01 sec)
-- 查询标识
mysql> select @n;
+------+
| @n   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> select @m；
+------+
| @m   |
+------+
| 1000 |
+------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

从程序可以看出即使出现主键重复错误，但由于我们进行捕获并处理使得整个存储过程的程序可以执行完成。

构建复杂的存储过程(案例)

获取一个订单的总价，并判断是否需要营业税收，案例如下：

DELIMITER //
mysql> create procedure sp_ordertotal( in onnumber int,in taxable boolean ,out ototal decimal(8,2) )
    -> begin
    -> -- 定义变量：总价
    -> declare total decimal(8,2);
    -> -- 定义默认税收率
    -> declare taxrate int default 6;
    -> -- 关联查询并计算总价
    -> select sum( price * items_num) from orderdetail  as od 
    ->  inner join items as it  on it.id=od.items_id
    ->  where od.orders_id = onnumber 
    ->  into total; --赋值
    -> -- 判断是否需要营业税收
    -> if taxable then 
    ->  select total + (total/100 * taxrate) into total;
    -> end if;
    -> -- 赋值给输出参数
    -> select total into ototal;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)
DELIMITER ; 

-- 执行存储过程
mysql> call sp_ordertotal(3,false,@total)//
Query OK, 1 row affected (0.00 sec)

-- 查询总价
mysql> select @total //
+----------+
| @total   |
+----------+
| 41244.90 |
+----------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

存储函数

创建存储函数

上一篇中，我们列举不少mysql自带的函数，但是有些时候自带函数并不能很好满足我们的需求，此时就需要自定义存储函数了，存储函数与存储过程有些类似，简单来说就是封装一段sql代码，完成一种特定的功能，并返回结果。其语法如下：

CREATE FUNCTION 函数([参数类型 数据类型[,….]]) RETURNS 返回类型 
　　BEGIN
　　　  SQL语句.....
　　　　RETURN (返回的数据)
　　END
 
 1
2
3
4
5
 
 1
2
3
4
5

与存储过程不同的是，存储函数中不能指定输出参数(OUT)和输入输出参数(INOUT)类型。存储函数只能指定输入类型而且不能带IN。同时存储函数可以通过RETURN命令将处理的结果返回给调用方。注意必须在参数列表后的RETURNS（该值的RETURNS多个S，务必留意）命令中预先指定返回值的类型。如下创建一个计算斐波那契数列的函数

-- 创建存储函数
mysql> create function fn_factorial(num int) returns int
    -> begin
    ->  declare result int default 1;
    ->  while num > 1 do 
    ->    set result = result * num ;
    ->    set num = num -1 ;
    ->  end while;
    ->  return result;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

-- 使用select 执行存储函数 
mysql> select fn_factorial(5);
+-----------------+
| fn_factorial(5) |
+-----------------+
|             120 |
+-----------------+
1 row in set (0.00 sec)

mysql> select fn_factorial(0),fn_factorial(5),fn_factorial(10);
+-----------------+-----------------+------------------+
| fn_factorial(0) | fn_factorial(5) | fn_factorial(10) |
+-----------------+-----------------+------------------+
|               1 |             120 |          3628800 |
+-----------------+-----------------+------------------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

这里命名存储函数时使用了【fn_】作为开头，这样可以更容易区分与【sp_】开头的存储过程，从上述语句可以看出前面在存储过程分析的流程语句也是可以用于存储函数的，同样的，DECLARE声明变量和SET设置变量也可用于存储函数，当然包括定义异常处理语句也是适应的,请注意执行存储函数使用的是select关键字，可同时执行多个存储函数，嗯，存储函数就这样定义，是不是跟存储过程很相似呢？但还是有区别的，这点留到后面分析。ok~，为了进一步熟悉存储函数，下面编写一个用于向user插入用户的存储函数：

-- 创建存储函数fn_insert_user
create function fn_insert_user(name varchar(32),sex char(1)) returns int 
begin   
insert into user (username,pinyin,birthday,sex,address) values(name,null,null,sex,null);  
return LAST_INSERT_ID(); --返回最后插入的ID值
end//
Query OK, 0 rows affected (0.00 sec)
DELIMITER ; 

-- 执行存储函数
mysql> select fn_insert_user('xiaolong',1);
+------------------------------+
| fn_insert_user('xiaolong',1) |
+------------------------------+
|                          101 |
+------------------------------+
1 row in set (0.00 sec)

-- 查询已插入的数据
mysql> select * from user where id = 101;
+-----+----------+-------------+----------+------+---------+
| id  | username | pinyin | birthday | sex  | address |
+-----+----------+-------------+----------+------+---------+
| 101 | xiaolong | NULL        | NULL     | 1    | NULL    |
+-----+----------+-------------+----------+------+---------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

显然数据插入成功了，其中 LAST_INSERT_ID()会返回最后插入的ID值，这里我们仅作为演示，因为实际开发中，我们一般更倾向于使用存储函数执行查询操作或者是数据的处理操作，对于更新插入删除这样的操作，使用较少。

删除存储函数

删除存储函数使用以下语句，如果需要修改存储函数，建议直接删除再重建，直接修改比较麻烦。

DROP FUNCTION [IF EXISTS] fn_name; 
 
 1
 
 1

存储过程与存储函数的区别

关于存储过程与存储函数的区别，主要给出以下几点。

存储过程可以有多个in,out,inout参数，而存储函数只有输入参数类型，而且不能带in
存储过程实现的功能要复杂一些；而存储函数的单一功能性(针对性)更强。
存储过程可以返回多个值；存储函数只能有一个返回值。
存储过程一般独立的来执行；而存储函数可以作为其他SQL语句的组成部分来出现。
存储过程可以调用存储函数。但函数不能调用存储过程。

触发器

触发器的概念及其语法

触发器可以简单理解一种特殊的存储过程，之前存储过程的变量定义及流程语句同样适合触发器，唯一不同的是我们只需要定义触发器，而不用手动调用触发器。从事件触发的角度来说，触发器编写的过程就是触发事件定义的过程，因为触发器定义好后会随着数据库操作命令的执行而触发，这些具体的操作是INSERT/UPDATE/DELETE。比如可以在user表中删除记录执行后，通过定义一个触发器把删除的数据自动添加到历史表中保存以便以后可以进行其他操作。创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name
FOR EACH ROW
BEGIN
trigger_stmt
END

 
 1
2
3
4
5
6
7
 
 1
2
3
4
5
6
7

其中：

trigger_name：触发器名称，用户自行指定；
trigger_time：触发时机，取值为 BEFORE 或 AFTER；
trigger_event：触发事件，取值为 INSERT、UPDATE 或 DELETE；需要注意的是这些操作命令并不一定严格意义上的命令，因为像 LOAD DATA 和 REPLACE 语句也能触发上述事件。LOAD DATA 语句用于将一个文件装入到一个数据表中，是一系列的 INSERT 操作。REPLACE 语句类似INSERT 语句，当表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句会等价于一条INSERT 语句或者一条 DELETE 语句和上一条 INSERT 语句。
tbl_name：表示在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句或者流程语句
FOR EACH ROW ：在mysql中属于固定写法，指明触发器以行作为执行单位，也就是当用户执行删除命令删除3条数据，与删除动作相关的触发器也会被执行3次。

ok，下面了解一下如何在mysql中定义触发器

创建触发器

在日常的数据库开发中，因业务需求，可能需要在插入更新删除时留下数据的日志，这时采用触发器来实现是个非常不错的选择，下面我们定义一个用户删除事件的触发器，当用户被删除后自动把被删除的数据添加到用户历史表user_history，历史用户表结构如下：

-- 为了让字段更简洁，这里我们修改几个字段名称
mysql> alter table user change username name varchar(32);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user change birthday birth datetime;
Query OK, 34 rows affected (0.02 sec)
Records: 34  Duplicates: 0  Warnings: 0
-- user 表结构
mysql> desc user;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(32)  | YES  | MUL | NULL    |                |
| pinyin  | varchar(32)  | YES  |     | NULL    |                |
| birth   | varchar(32)  | YES  |     | NULL    |                |
| sex     | char(1)      | YES  |     | NULL    |                |
| address | varchar(256) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

-- 历史表 user_history 其中updated字段为删除日期
mysql> desc user_history;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| uid     | int(11)      | NO   |     | NULL    |       |
| name    | varchar(32)  | YES  |     | NULL    |       |
| pinyin  | varchar(32)  | YES  |     | NULL    |       |
| birth   | varchar(32)  | YES  |     | NULL    |       |
| sex     |     char(1)  | YES  |     | NULL    |       |
| address | varchar(255) | YES  |     | NULL    |       |
| updated | datetime     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

触发器定义语句如下：

DELIMITER //
-- 创建触发器
mysql> create trigger trg_user_history after delete
    ->   on user for each row
    -> begin
    ->  insert into user_history(uid,name,pinyin,birth,sex,address,updated)
    ->  values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW());
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)
DELIMITER ;
 
 1
2
3
4
5
6
7
8
9
10
11
 
 1
2
3
4
5
6
7
8
9
10
11

上述sql中创建语句的形式与前面的存储过程或者存储函数都很类似，这里有点要注意的是，使用OLD/NEW关键字可以获取数据变更前后的记录，其中OLD用于AFTER时刻，而NEW用于BEFORE时刻的变更。如OLD.name表示从user表删除的记录的名称。INSERT操作一般使用NEW关键字，UPDATE操作一般使用NEW和OLD，而DELETE操作一般使用OLD。现在我们从user表删除一条数据，然后查看user_history表的数据。

-- 删除user中id为60的用户数据
mysql> delete from user where id =60;
Query OK, 1 row affected (0.00 sec)

-- 查看历史表
mysql> select * from user_history;
+----+-----+-------------+------------+---------------------+------+---------+---------------------+
| id | uid | name        | pinyin     | birth               | sex  | address | updated             |
+----+-----+-------------+------------+---------------------+------+---------+---------------------+
|  1 |  60 | 高余粮10    | gaoyuliang | 1999-01-01 00:00:00 | 1    | NULL    | 2017-04-21 09:39:23 |
+----+-----+-------------+------------+---------------------+------+---------+---------------------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
 
 1
2
3
4
5
6
7
8
9
10
11
12

显然我们定义的触发器已生效了。

查看触发器

如果需要查看定义好的触发器可以使用以下语句：

SHOW TRIGGERS [FROM schema_name];
 
 1
 
 1

其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，Schema指定为数据库名即可。

mysql> SHOW TRIGGERS \G;
*************************** 1. row ***************************
             Trigger: trg_user_history --触发器名称
               Event: DELETE  --触发事件
               Table: user --触发器作用的表
           Statement: begin
insert into user_history(uid,name,pinyin,birth,sex,address,updated)
values(OLD.id,OLD.name,OLD.pinyin,OLD.birth,OLD.sex,OLD.address,NOW());
end
              Timing: AFTER
             Created: 2017-04-21 09:27:56.58
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

删除触发器

删除触发器可以使用以下语句

DROP TRIGGER 触发器名称
 
 1
 
 1

游标

在前面的分析中可知sql的检索操作返回的数据几乎都是以整个集合的形式，也就是说sql善于将多条查询记录集中到一起并返回，倘若现在需要一行行地处理查询的结果，这对于sql语句来说确实是个难题，好在存在一种称为游标的技术可以解决这个问题，所谓的游标就就是可以将检索出来的数据集合保存在内存中然后依次取出每条数据进行处理，这样就解决了sql语句无法进行行记录处理的难题，游标的读取图解如下：

其中有个指针的概念，指针指明了当前行记录的信息，在游标的处理过程中通过移动指针进行逐行读取数据。要明白的是，游标一般结合存储过程或存储函数或触发器进行使用，ok~，理解了游标的概念后，看看其定义语法

-- 声明游标
DECLARE cursor_name CURSOR FOR SELECT 语句;

-- 打开游标
OPEN cursor_name;

-- 从游标指针中获取数据
FETCH cursor_name INTO 变量名 [,变量名2,...];

--关闭游标
CLOSE cursor_name
 
 1
2
3
4
5
6
7
8
9
10
11
 
 1
2
3
4
5
6
7
8
9
10
11

在使用游标前需要对其进行声明，其中cursor_name表示游标名，CURSOR FOR是固定写法，SELECT 是检索语句，把检索出来的数据存放到游标中等待处理。下面我们通过一个案例演示并理解游标的使用

mysql> DELIMITER //
-- 创建存储过程
mysql> create procedure sp_cursor(out result text)
    -> begin 
    ->  declare flag bit default 0;--定义标识变量用于判断是否退出循环
    ->  declare tmp varchar(20);-- 定义临时存储变量
    ->  declare cur cursor for select distinct name from user where id < 20;-- 声明游标
    ->  declare continue handler for not found set flag = 1; --异常处理并设置flag=1
    ->  open cur; -- 打开游标
    ->  while flag!=1 do 
    ->    fetch cur into tmp ; --从游标中取值并存放到tmp中
    ->    if flag !=1 then
    ->       set result = concat_ws(',',result,tmp); --拼接每次获取的结果
    ->    end if;
    ->  end while;
    ->  close cur; --关闭游标
    -> end
    -> //

Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
--执行
mysql> call sp_user_cursor(@result);
Query OK, 0 rows affected (0.00 sec)
-- 查询结果
mysql> select @result;
+------------------------------------------------+
| @result                                        |
+------------------------------------------------+
| 王五,张曹宇,李达康,张书记,任在明               |
+------------------------------------------------+
1 row in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

上述的存储过程是用于查询出id小于20的用户名称，并拼接成一个以逗号隔开的字符串输出。我们声明了一个flag的变量用于标识是否结束while循环，同时也声明了tmp变量用于存储每次从游标中获取的行数据，因为我们定义游标是从user表中查询name字段的数据，因此只需要一个tmp变量就行了，如果需要查询user中多个字段，则声明多个tmp字段并在获取数据时以fetch cur into tmp [,tmp2,tmp3,...];形式即可，请注意在使用游标前必须先打开，使用open cur;语句，而且只有在打开游标后前面定义的select语句开正式开始执行。循环获取cur中的数据使用了while流程语句，这里我们还定义了前面分析过的异常处理语句即

 declare continue handler for not found set flag = 1; --异常处理并设置flag=1
 
 1
 
 1

在发生not found 的异常时将flag设置为1，并通过声明为continue而让程序继续执行。这样处理的理由是fetch cur into tmp语句执行时，如果游标的指针无法读取下一行数据时就会抛出NOT FOUND异常，抛出后由已声明的异常程序处理，并设置flag为1，以此来结束循环，注意抛出异常后程序还会继续执行，毕竟声明了continue。所以最后一次判断if flag !=1 then是必要的。最后执行完成，通过close cur 关闭游标，这样整个游标的使用就完成了。

事务处理

事务处理是数据库中的一个大块头，涉及到数据的完整性与一致性问题，由于mysql存在多种数据存储引擎提供给用户选择，但不是所有的引擎都支持事务处理，常见的引擎有：MyISAM和InnoDB，MyISAM是默认高速的引擎并不支持事务功能，InnoDB支持行锁定和事务处理，速度比MyISAM稍慢。事实上前面我们在创建表时都指明存储引擎为InnoDB，本篇中我们也将采用InnoDB引擎进行分析，毕竟InnoDB是支持事务功能的。

事务的概念

先看一个经典银行转账案例，A向B的银行卡转账1000元，这里分两个主要事件，一个是A向B转账1000，那么A的银行卡转账成功后必须在原来的数额上扣掉1000元，另一个是B收到了A的转款，B的银行卡上数额必须增加1000元，这两个步骤是必须都成功才算转账成功，总不能A转账B后，A的数额没有变化而B增加了1000元吧？这样银行不得亏死了？因此两个步骤只要有一个失败，此次转账的结果就是失败。但我们在执行sql语句时，两个动作是分两个语句执行的，万一执行完一个突然没电了另外一个没有执行，那岂不出问题了？此时就需要事务来解决这个问题了，所谓的事物就是保证以上的两个步骤在同一个环境中执行，只要其中一个失败，事务就会撤销之前的操作，回滚的没转账前的状态，如果两个都执行成功，那么事务就认为转成成功了。这就是事务的作用。

对事务有了初步理解后，进一步了解事务的官方概念，事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）

原子性（Autmic）：一个原子事务要么完整执行，要么干脆不执行。也就是说，工作单元中的每项任务都必须正确执行，如果有任一任务执行失败，则整个事务就会被终止并且此前对数据所作的任何修改都将被撤销。如果所有任务都被成功执行，事务就会被提交，那么对数据所作的修改将会是永久性的
一致性（Consistency）：一致性代表了底层数据存储的完整性。它是由事务系统和应用开发人员共同来保证。事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求; 应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且工作单元中所实现的业务逻辑不会导致数据的不一致(数据预期所表达的现实业务情况不相一致)。例如，在刚才的AB转账过程中，从A账户中扣除的金额必须与B账户中存入的金额相等。
隔离性（Isolation）：隔离性是指事务必须在不干扰其他事务的前提下独立执行，也就是说，在事务执行完毕之前，其所访问的数据不能受系统其他部分的影响。
持久性（Durability）：持久性指明当系统或介质发生故障时，确保已提交事务的更新数据不能丢失，也就意味着一旦事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障，持久性可以通过数据库备份和恢复来保证。

事务控制流程实战

在使用事务处理可能涉及到以下命令：

-- 声明事务的开始
BEGIN(或START TRANSACTION);

-- 提交整个事务
COMMIT;

-- 回滚到事务初始状态
ROLLBACK;
 
 1
2
3
4
5
6
7
8
 
 1
2
3
4
5
6
7
8

下面通过删除user表中的用户数据，然后再回滚来演示上述命令的作用：

-- 先查看user表中的数据
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec);

-- 开始事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec);
-- 删除ID为24的用户
mysql> delete from user where id =24;
Query OK, 1 row affected (0.00 sec);

-- 删除完成后再次查看user表数据，显然ID为24的数据已被删除
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
+----+-----------+--------------+---------------------+------+--------------------+
6 rows in set (0.00 sec);

-- 执行回滚操作rollback
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 再次查看数据，可见ID为24的用户数据已恢复
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

从上述一系列操作中，从启动事务到删除用户数据，再到回滚数据，体现了事务控制的过程，这里我们还没使用COMMIT，如果刚才把rollback改成commit，那么事务就提交了，数据也就真的删除了。下面我们再次来演示删除数据的过程，并且这次使用commit提交事务。

-- 先添加一条要删除数据
mysql> insert into user values(30,'要被删除的数据',null,null,1,null);
Query OK, 1 row affected (0.01 sec);
-- 查看数据
mysql> select * from user;
+----+-----------------------+--------------+---------------------+------+--------------------+
| id | name                  | pinyin       | birth               | sex  | address            |
+----+-----------------------+--------------+---------------------+------+--------------------+
|  1 | 王五                  | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇                | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康                | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记                | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明                | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明                | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海                | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
| 30 | 要被删除的数据        | NULL         | NULL                | 1    | NULL               |
+----+-----------------------+--------------+---------------------+------+--------------------+
8 rows in set (0.00 sec);

-- 开启新事务
mysql> begin;
Query OK, 1 row affected (0.00 sec);
-- 删除数据
mysql> delete from user where id =30;
Query OK, 1 row affected (0.00 sec);
-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec);
-- 回滚数据
mysql> rollback;
Query OK, 0 rows affected (0.00 sec);
-- 查看数据
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec)
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

可以发现当删除完数据后，使用commit提交了事务，此时数据就会被真正更新到数据库了，即使使用rollback回滚也是没有办法恢复数据的。ok~，这就是事务控制最简化的流程，事实上除了上述的回滚到事务的初始状态外，还可以进行部分回滚，也就是我们可以自己控制事务发生错误时回滚到某个点，这需要利用以下命令来执行：

-- 定义保存点（回滚点）
SAVEPOINT savepoint_name（名称）;

--回滚到指定保存点
ROLLBACK TO SAVEPOINT savepoint_name（名称）;
 
 1
2
3
4
5
 
 1
2
3
4
5

演示案例如下：

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

mysql> insert into user values(31,'保存点1',null,null,1,null);
Query OK, 1 row affected (0.00 sec);
-- 创建保存点
mysql> savepoint sp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(32,'保存点2',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(33,'保存点3',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(34,'保存点4',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------------+--------------+---------------------+------+--------------------+
| id | name       | pinyin       | birth               | sex  | address            |
+----+------------+--------------+---------------------+------+--------------------+
|  1 | 王五       | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇     | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康     | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记     | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明     | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明     | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海     | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
| 31 | 保存点1    | NULL         | NULL                | 1    | NULL               |
| 32 | 保存点2    | NULL         | NULL                | 1    | NULL               |
| 33 | 保存点3    | NULL         | NULL                | 1    | NULL               |
| 34 | 保存点4    | NULL         | NULL                | 1    | NULL               |
+----+------------+--------------+---------------------+------+--------------------+
11 rows in set (0.00 sec);
-- 回滚到保存点
mysql> rollback to savepoint sp;
Query OK, 0 rows affected (0.00 sec);
-- 查看数据
mysql> select * from user;
+----+------------+--------------+---------------------+------+--------------------+
| id | name       | pinyin       | birth               | sex  | address            |
+----+------------+--------------+---------------------+------+--------------------+
|  1 | 王五       | wangwu       | NULL                | 1    | 北京市朝阳区       |
|  2 | 张曹宇     | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 广东省汕头市       |
|  3 | 李达康     | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝阳           |
| 10 | 张书记     | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝阳区       |
| 16 | 任在明     | renzaiming   | 1996-12-01 00:00:00 | 1    | 广东省广州市       |
| 22 | 陈小明     | chenxiaoming | 1995-05-10 00:00:00 | 0    | 广东省深圳市       |
| 24 | 任传海     | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亚           |
| 31 | 保存点1    | NULL         | NULL                | 1    | NULL               |
+----+------------+--------------+---------------------+------+--------------------+
8 rows in set (0.00 sec);
-- 提交事务
mysql> commit ;
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

关于commit有点需要知道的，在mysql中每条sql命令都会被自动commit，这种功能称为自动提交功能，是默认开启的。前面我们在执行事务使用了begin命令开启了事务，这时自动提交在事务中就关闭了直到事务被手动commit。当然我们也可以手动控制开启或者关闭此功能，语法如下：

-- 关闭自动提交功能
SET AUTOCOMMIT=0;
-- 开启自动提交功能
SET AUTOCOMMIT=1;
 
 1
2
3
4
 
 1
2
3
4

锁以及事务处理分离水平(隔离级别)

了解悲观锁和乐观锁的概念

悲观锁：假设会发生并发冲突，回避一切可能违反数据完整性的操作。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性，注意乐观锁并不能解决脏读的问题(关于脏读稍后解析)。

在一般情况下，悲观锁依靠数据库的锁机制实现，以保证操作最大程度的排他性和独占性，因而会导致数据库性能的大量开销和并发性很低，特别是对长事务而言，这种开销往往过于巨大而无法承受。为了解决这样的问题，乐观锁机制便出现了。乐观锁，大多情况下是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则给予更新，否则认为是过期数据。ok~，关于悲观锁和乐观锁的简单概念就先了解到这。

mysql中的共享锁与排他锁

在mysql中，为了保证数据一致性和防止数据处理冲突，引入了加锁和解锁的技术，这样可以使数据库中特定的数据在使用时不让其他用户(进程或事务)操作而为该数据加锁，直到该数据被处理完成后再进行解锁。根据使用目的不同把锁分为共享锁定(也称为读取锁定)和排他锁定(写入锁定)。

共享锁定：将对象数据变为只读形式的锁定，这样就允许多方同时读取一个数据，此时数据将无法修改。
排他锁定：在对数据进行insert/update/delete时进行锁定，在此时其他用户(进程或事务)一律不能读取数据，从而也保证数据完整性。

以上两种锁都属于悲观锁的应用，还有一点，根据锁定粒度的不同，可分为行锁定(共享锁和排他锁使用应用的就是行锁定)，表锁定，数据库锁定，可见粒度的不同将影响用户(进程或事务)对数据操作的并发性，目前mysql支持行锁定和表锁定。

事务处理分离水平

事实上，锁的出现更多的是为了在多个用户(进程或事务)同时执行更新操作时保证数据的完整性和一致性，但随之而来的问题是当数据的锁定时间越长，数据同时运行性也会随之降低。也就意味着当一个用户(进程或事务)对数据保存锁定时，其他用户(进程或事务)只能等待锁定解锁，这样也就导致并发访问该数据的同时性较低。所以在多用户(进程或事务)对数据进行更新或者访问的同时如何保证数据的完整性和一致性，这样的情况下需要有一个相对折中的妥协，因为并不是频繁锁定数据或者极致提供同时运行性就是合理的，为了描述这个问题数据库中引入分离水平(有些地方称为隔离级别)的概念来确定事务处理之间的相互影响程度。其规则描述：分离水平越高，数据的完整性也就越高，但同时运行性下降，相反如果分离水平越低数据完整性越低，同时运行性也就提高了。在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务，并发虽然是常见的，但可能会导致不同分离水平下发生不同的数据读取情况，4种分离水平以及可能导致的情景如下：

分离水平	读未提交	不可重复读	幻读
READ UNCOMMITTED	会	会	会
READ COMMITTED	不	会	会
REPEATABLE READ	不	不	会
SERIALIZABLE	不	不	不

四种分离水平(隔离级别)

READ_UNCOMMITTED：这是事务最低的分离水平(隔离级别)，它充许别外一个事务可以看到这个事务未提交的数据，会出现脏读、不可重复读、幻读（分离水平最低，并发性能高）
READ_COMMITTED：保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行，mysql默认隔离级别）
REPEATABLE_READ：可以防止脏读、不可重复读，但会出幻读（锁定所读取的所有行）
SERIALIZABLE：这是花费最高代价但是最可靠的事务分离水平(隔离级别)，事务被处理为顺序执行。保证所有的情况不会发生（锁表,并发性及其低）

读未提交、不可重复读，幻读

读未提交，也称脏读，脏读发生在一个事务读取了另一个事务改写但尚未提交的数据时。如果改写在稍后被回滚了，那么第一个事务获取的数据就是无效的。
不可重复读：不可重复读发生在一个事务执行相同的查询两次或两次以上，但是每次都得到不同的数据时。这通常是因为另一个并发事务在两次查询期间进行了更新。请注意，不可重复读重点是修改数据导致的(修改数据时排他读);，例如：在事务1中，客户管理人员在读取了张曹宇的生日为1990-08-05,操作并没有完成
```
select birth from user where name ='张曹宇' ;
   
   1
   
   1
```
在事务2中，这时张曹宇自己修改生日为1990-06-05,并提交了事务.
```
begin;
-- 其他操作省略
update user set birth='1990-06-05' where  name ='张曹宇' ;
commit;
   
   1
2
3
4
   
   1
2
3
4
```
在事务1中，客户管理人员再次读取了张曹宇的生日时，生日变为1990-06-05,从而导致在一个事务中前后两次读取的结果并不一致，导致了不可重复读。
幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录。请注意，幻读重点是插入或者删除数据导致的（对满足条件的数据行集进行锁定），同样的道理，在事务1中，客户管理查询所有用户生日在1990-06-05的人只有20个，操作并没有完成，此时事务2中，刚好有一个新注册的用户，其生日也1990-06-05，在事务2中插入新用户并提交了事务，此时在事务1中再次查询时，所有用户生日在1990-06-05的人变为21个了，从也就导致了幻读。

ok~，在理解了读未提交、不可重复的、幻读后，再次看回表格，小结一下，可以发现在分离水平为READ UNCOMMITTED时，将会导致3种情况的出现，因此这样的分离水平一般是不建议使用的。在分离水平为READ COMMITTED时，不会导致脏读，但会导致不可重复读和幻读，要回避这样的现象，必须采用分离水平为REPEATABLE READ，这样就只会导致幻读，而当分离水平为SERIALIZABLE时，3种现象都不复存在。但请注意这并不意味着所有情况下采用分离水平为SERIALIZABLE都是合理的，就如前面所分析的分离水平越高，数据的完整性也就越高，但同时运行性下降。在大多数情况下，我们会在根据应用的实际情景选择分离水平为REPEATABLE READ或者READ COMMITTED(MySQL默认的事务分离水平为REPEATABLE READ)，这样既能一定程度上保证数据的完整性也同时提供了数据的同时运行性，在mysql中我们可以使用以下语法设置事务分离水平

-- 设置当前连接的事务分离水平
SET SESSION TRANSACTION ISOLATION LEVEL 事务分离水平;

--设置全部连接（包括新连接）的事务分离水平
SET GLOBAL TRANSACTION ISOLATION LEVEL 事务分离水平;
 
 1
2
3
4
5
 
 1
2
3
4
5

事务、分离水平、锁之间的关系

通过上述的分析，我们也理解了事务、锁和分离水平的概念，但锁和事务以及分离水平关系如何呢？实际上，事务是解决多条sql执行执行过程的原子性、一致性、隔离性、持久性的整体解决方案，而事务分离水平则是并发控制的整体解决方案，其实际是综合利用各种类型的锁来解决并发问题。锁是数据库并发控制的内部基础机制。对应用开发人员来说，只有当事务分离水平无法解决并发问题和需求时，才有必要在语句中手动设置锁。关于锁的锁定，对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。请注意InnoDB行锁是通过给索引上的索引项加锁来实现的，也就是说，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

-- 共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

-- 排他锁（X)
SELECT * FROM table_name WHERE ... FOR UPDATE;
 
 1
2
3
4
5
 
 1
2
3
4
5

事务原理概要

最后我们来简单了解一下事务内部实现的原理概要，事实上事务的处理机制是通过记录更新日志而实现的，其中与事务处理相关的日志是UNDO日志和REDO日志。

UNDO日志亦称为回滚端，在进行数据插入、更新、删除的情景下，保存变更前的数据，原理图如下：

在表中保存了指向UNDO日志的指针，rollback执行时根据这个指针来获取旧数据并覆盖到表中，rollback执行完成后或者commit后UNDO日志将被删除。UNDO还有另外一种作用，当A用户正在更新数据时，还没提交，而B用户也需要使用该数据，这时不可能让B读取未提交的数据，因此会将存在UNDO表中的数据提供给B用户。这就是事务回滚的简单模型。
REDO日志主要是事务提交后由于错误或者断电停机等原因使数据无法更新到数据库中时，REDO日志将提供数据恢复作用。其原理是通过数据库中的一段缓冲的数据先实时更新到REDO日志再更新到数据库，也就是说平常的更新操作并非一步执行到位的，而是首选更新到REDO日志中，再更新到数据库文件的。所以REDO日志才能用户故障数据的恢复。

ok~，关于事务的原理先关注这么多，到此本篇也就完结。

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

byzer plugin install log

Byzer 白泽

函数实现越通用越好？来看看 Byzer-LLM 的 Function Implementation 带来的编程思想大变化...

前言Function Calling 是 OpenAI 首先提出来的一个非常有用的功能，实现了大模型对函数的调用能力。Byzer-LLM 给开源模型也带来了 Function Calling 实现。在这个基础上，我们还拓展了 Respond With Class 功能，允许大模型输出标准的Python对象，进步控制了大模型的输出能力。这两个能力参看：给开源大模型带来Function Callin.