MYSQL 第九章 视图、索引、存储过程和触发器

MySQL创建视图（CREATE VIEW）

可以使用 CREATE VIEW 语句来创建视图。

语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

语法说明如下。

• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
• <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

• 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• SELECT 语句不能引用系统或用户变量。
• SELECT 语句不能包含 FROM 子句中的子查询。
• SELECT 语句不能引用预处理语句参数

mysql> create view view_employees as 
    -> select * from employees limit 10;
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
| view_employees       |
+----------------------+
9 rows in set (0.00 sec)

mysql> select * from view_employees;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

mysql> 

 

mysql> create view view_emp ( v_emp_no,v_first_name,v_last_name) as select emp_no,first_name,last_name from employees limit 10;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_emp;
+----------+--------------+-------------+
| v_emp_no | v_first_name | v_last_name |
+----------+--------------+-------------+
|    10001 | Georgi       | Facello     |
|    10002 | Bezalel      | Simmel      |
|    10003 | Parto        | Bamford     |
|    10004 | Chirstian    | Koblick     |
|    10005 | Kyoichi      | Maliniak    |
|    10006 | Anneke       | Preusig     |
|    10007 | Tzvetan      | Zielinski   |
|    10008 | Saniya       | Kalloufi    |
|    10009 | Sumant       | Peac        |
|    10010 | Duangkaew    | Piveteau    |
+----------+--------------+-------------+
10 rows in set (0.00 sec)

mysql> desc view_emp;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| v_emp_no     | int(11)     | NO   |     | NULL    |       |
| v_first_name | varchar(14) | NO   |     | NULL    |       |
| v_last_name  | varchar(16) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

MySQL索引简介

为什么要使用索引

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

索引的分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。

1) B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

• 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
• 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
• 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

• 查询必须从索引的最左边的列开始。
• 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
• 存储引擎不能使用索引中范围条件右边的列。

2) 哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。
• 不能使用 HASH 索引排序。
• HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1) 普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

2) 唯一性索引

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。

创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

3) 主键索引

主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。

注意：主键是数据库考察的重点。注意每个表只能有一个主键。

4) 空间索引

空间索引主要用于地理空间数据类型 GEOMETRY。

5) 全文索引

全文索引只能在 VARCHAR 或 TEXT 类型的列上创建，并且只能在 MyISAM 表中创建。

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

• 单列索引就是索引只包含原表的一个列。
• 组合索引也称为复合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

为了提高索引的应用性能，MySQL中的索引可以根据具体应用采用不同的索引策略。这些索引策略所对应的索引类型有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引等。

索引的使用原则和注意事项

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

注意：索引可以在一些情况下加速查询，但是在某些情况下，会降低效率。

索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则：

• 在经常需要搜索的列上建立索引，可以加快搜索的速度。
• 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
• 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
• 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
• 在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。

与此对应，在某些应用场合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上还带来负面效应，降低了数据库的工作效率，一般来说不适合创建索引的环境如下：

• 对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
• 对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
• 对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
• 当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

MySQL创建索引（CREATE INDEX）

MySQL 提供了三种创建索引的方法：

1) 使用 CREATE INDEX 语句

可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。

语法格式：

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

语法说明如下：

• <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
• <表名>：指定要创建索引的表名。
• <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
• <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
• ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

2) 使用 CREATE TABLE 语句

索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。语法格式：

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。

语法格式：

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。

语法格式：

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

语法格式：

FOREIGN KEY <索引名> <列名>

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。

在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现。

3) 使用 ALTER TABLE 语句

CREATE INDEX 语句可以在一个已有的表上创建索引，ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。

语法格式：

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。

语法格式：

ADD PRIMARY KEY [<索引类型>] (<列名>,…)

在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。

语法格式：

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

语法格式：

ADD FOREIGN KEY [<索引名>] (<列名>,…)

在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。

创建普通索引

创建普通索引时，通常使用 INDEX 关键字。

mysql> create table t_1 ( id int not null, name char(45) default null, dept_id int default null, age int not null, height int default null, index(height) );
Query OK, 0 rows affected (0.09 sec)

mysql> show create table t_1 \G
*************************** 1. row ***************************
       Table: t_1
Create Table: CREATE TABLE `t_1` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `height` int(11) DEFAULT NULL,
  KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

mysql> 

创建唯一索引

创建唯一索引，通常使用 UNIQUE 参数。

mysql> create table t_2 ( id int not null, name char(45) default null, dept_id int default null, age int not null, height int default null, unique index(height) );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t_2\G
*************************** 1. row ***************************
       Table: t_2
Create Table: CREATE TABLE `t_2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) NOT NULL,
  `height` int(11) DEFAULT NULL,
  UNIQUE KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MySQL查看索引（SHOW INDEX）

索引创建完成后，可以利用 SQL 语句查看已经存在的索引。在 MySQL 中，可以使用 SHOW INDEX 语句查看表中创建的索引。

查看索引的语法格式如下：

SHOW INDEX FROM <表名> [ FROM <数据库名>]

mysql> show index from t_1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_1   |          1 | height   |            1 | height      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> 

其中各主要参数说明如下：

 

参数	说明
Table	表示创建索引的数据表名，这里是 tb_stu_info2 数据表。
Non_unique	表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
Key_name	表示索引的名称。
Seq_in_index	表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name	表示定义索引的列字段。
Collation	表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality	索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part	表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed	指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null	用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Index_type	显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment	显示评注。

 

 

MySQL修改和删除索引（DROP INDEX）

 删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

基本语法

当不再需要索引时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句来对索引进行删除。

1) 使用 DROP INDEX 语句

语法格式：

DROP INDEX <索引名> ON <表名>

语法说明如下：

• <索引名>：要删除的索引名。
• <表名>：指定该索引所在的表名。

2) 使用 ALTER TABLE 语句

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

• DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
• DROP INDEX index_name：表示删除名称为 index_name 的索引。
• DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

 

mysql> drop index height on t_1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

MySQL索引的设计原则

 

 索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。本节将介绍一些索引的设计原则。

1. 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2. 为经常需要排序、分组和联合操作的字段建立索引

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3. 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

4. 限制索引的数目

索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。

如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

5. 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

6. 数据量小的表最好不要使用索引

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

7. 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8. 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

总结

选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能只拘泥于上面的准则。应该在学习和工作中不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。

 

MySQL存储过程是什么？

 我们前面所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。

例如，为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表，此时就需要使用多条 SQL 语句来针对这几个数据表完成处理要求。

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

MySQL 5.0 版本以前并不支持存储过程，这使 MySQL 在应用上大打折扣。MySQL 从 5.0 版本开始支持存储过程，既提高了数据库的处理速度，同时也提高了数据库编程的灵活性

存储过程是数据库中的一个重要功能，存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成功，就可以随时被调用，完成指定的功能操作。

使用存储过程不仅可以提高数据库的访问效率，同时也可以提高数据库使用的安全性。

对于调用者来说，存储过程封装了 SQL 语句，调用者无需考虑逻辑功能的具体实现过程。只是简单调用即可，它可以被 Java 和 C# 等编程语言调用。

编写存储过程对开发者要求稍微高一些，但这并不影响存储过程的普遍使用，因为存储过程有如下优点：

1) 封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

2) 可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

3) 可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

4) 高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能。

5) 提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

6) 使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

MySQL创建存储过程（CREATE PROCEDURE）

基本语法

可以使用 CREATE PROCEDURE 语句创建存储过程。

语法格式如下：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

语法说明如下：

1) 过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2) 过程参数

存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。

3) 过程体

存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

在存储过程的创建中，经常会用到一个十分重要的 MySQL 命令，即 DELIMITER 命令，特别是对于通过命令行的方式来操作 MySQL 数据库的使用者，更是要学会使用该命令。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下：

DELIMITER $$

语法说明如下：

• $$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
• 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下 SQL 语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时，必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程，若要查看某个存储过程的具体信息，则可以使用 SHOW CREATE PROCEDURE <存储过程名>。

 

创建不带参数的存储过程

mysql> delimiter #
mysql> create procedure ShowStu()
    -> begin
    -> select * from titles limit 10;
    -> end #
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter;delimiter' at line 1
mysql> delimiter ;
mysql> #
mysql> delimiter;
ERROR: 
DELIMITER must be followed by a 'delimiter' character or string
mysql> call ShowStu;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  10004 | Engineer        | 1986-12-01 | 1995-12-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
|  10005 | Senior Staff    | 1996-09-12 | 9999-01-01 |
|  10005 | Staff           | 1989-09-12 | 1996-09-12 |
|  10006 | Senior Engineer | 1990-08-05 | 9999-01-01 |
|  10007 | Senior Staff    | 1996-02-11 | 9999-01-01 |
|  10007 | Staff           | 1989-02-10 | 1996-02-11 |
+--------+-----------------+------------+------------+
10 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> 

 

 

创建带参数的存储过程

mysql> delimiter #
mysql> create procedure GetStu
    -> (IN name varchar(30))
    -> begin
    -> select emp_no from titles where title='Staff' limit 10;
    -> end #
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call GetStu('Staff')
    -> ;
+--------+
| emp_no |
+--------+
|  10002 |
|  10005 |
|  10007 |
|  10011 |
|  10016 |
|  10017 |
|  10019 |
|  10034 |
|  10038 |
|  10039 |
+--------+
10 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

MySQL查看存储过程

查看存储过程的状态

MySQL 中可以通过 SHOW STATUS 语句查看存储过程的状态，其基本语法形式如下：

SHOW PROCEDURE STATUS LIKE 存储过程名;

LIKE 存储过程名用来匹配存储过程的名称，LIKE 不能省略。

mysql> show procedure status like 'GetStu' \G
*************************** 1. row ***************************
                  Db: employees
                Name: GetStu
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-06-22 17:05:17
             Created: 2020-06-22 17:05:17
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.05 sec)

mysql> 

查看存储过程的定义

mysql> show create procedure GetStu \G
*************************** 1. row ***************************
           Procedure: GetStu
            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 `GetStu`(IN name varchar(30))
begin
select emp_no from titles where title='Staff' limit 10;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> 

 

拓展阅读

存储过程的信息都存储在 information_schema 数据库下的 Routines 表中，可以通过查询该表的记录来查询存储过程的信息，SQL 语句如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;

在 information_schema 数据库下的 routines 表中，存储着所有存储过程的定义。所以，使用 SELECT 语句查询 routines 表中的存储过程和函数的定义时，一定要使用 routine_name 字段指定存储过程的名称，否则，将查询出所有的存储过程的定义。

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='GetStu' \G
*************************** 1. row ***************************
           SPECIFIC_NAME: GetStu
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: employees
            ROUTINE_NAME: GetStu
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: begin
select emp_no from titles where title='Staff' limit 10;
end
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-06-22 17:05:17
            LAST_ALTERED: 2020-06-22 17:05:17
                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
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> 

 

MySQL修改存储过程（ALTER PROCEDURE）

MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。本节将详细讲解修改存储过程的方法。

MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
• NO SQL 表示子程序中不包含 SQL 语句。
• READS SQL DATA 表示子程序中包含读数据的语句。
• MODIFIES SQL DATA 表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
• DEFINER 表示只有定义者自己才能够执行。
• INVOKER 表示调用者可以执行。
• COMMENT 'string' 表示注释信息。

mysql> alter procedure GetStu modifies sql data sql security invoker;
Query OK, 0 rows affected (0.05 sec)

mysql> show create procedure GetStu \G
*************************** 1. row ***************************
           Procedure: GetStu
            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 `GetStu`(IN name varchar(30))
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
begin
select emp_no from titles where title='Staff' limit 10;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> 

MySQL删除存储过程（DROP PROCEDURE）

MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下：

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

语法说明如下：

• 过程名：指定要删除的存储过程的名称。
• IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。

注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

mysql> drop procedure GetStu;
Query OK, 0 rows affected (0.03 sec)

mysql> 

MySQL触发器到底是什么？

触发器的优缺点

触发器的优点如下：

• 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。
• 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
• 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

触发器的缺点如下：

• 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。
• 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性，
• 如果需要变动的数据量较大时，触发器的执行效率会非常低。

MySQL 支持的触发器

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

1) INSERT 触发器

在 INSERT 语句执行之前或之后响应的触发器。

使用 INSERT 触发器需要注意以下几点：

• 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
• 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

2) UPDATE 触发器

在 UPDATE 语句执行之前或之后响应的触发器。

使用 UPDATE 触发器需要注意以下几点：

• 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
• 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
• 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
• OLD 中的值全部是只读的，不能被更新。

注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。

3) DELETE 触发器

在 DELETE 语句执行之前或之后响应的触发器。

使用 DELETE 触发器需要注意以下几点：

• 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
• OLD 中的值全部是只读的，不能被更新。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

MySQL创建触发器（CREATE TRIGGER）

基本语法

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。

语法格式如下：

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

语法说明如下。

1) 触发器名

触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。

2) INSERT | UPDATE | DELETE

触发事件，用于指定激活触发器的语句的种类。

注意：三种触发器的执行时间如下。

• INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
• DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
• UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。

3) BEFORE | AFTER

BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

4) 表名

与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

5) 触发器主体

触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。

6) FOR EACH ROW

一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGERS 语句。

创建 BEFORE 类型触发器

mysql> CREATE TRIGGER SumOfSalary
    -> BEFORE INSERT ON tb_emp8
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.salary;
Query OK, 0 rows affected (0.35 sec)
触发器 SumOfSalary 创建完成之后，向表 tb_emp8 中插入记录时，定义的 sum 值由 0 变成了 1500，即插入值 1000 和 500 的和，如下所示。
SET @sum=0;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 1500 |
+------+
1 row in set (0.03 sec)

创建 AFTER 类型触发器

【实例 2】创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。输入的 SQL 语句和执行过程如下所示。
mysql> CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
Query OK, 0 rows affected (0.25 sec)
触发器 double_salary 创建完成之后，向表 tb_emp6 中插入记录时，同时向表 tb_emp7 中插入相同的记录，并且 salary 字段为 tb_emp6 中 salary 字段值的 2 倍，如下所示。
mysql> INSERT INTO tb_emp6
    -> VALUES (1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb_emp6;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   1000 |
|  2 | B    |      1 |    500 |
+----+------+--------+--------+
3 rows in set (0.04 sec)
mysql> SELECT * FROM tb_emp7;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   2000 |
|  2 | B    |      1 |   1000 |
+----+------+--------+--------+
2 rows in set (0.06 sec)

MySQL查看触发器

在 MySQL 中，可以通过 SHOW TRIGGERS 语句来查看触发器的基本信息，语法格式如下：

SHOW TRIGGERS;

MySQL修改和删除触发器（DROP TRIGGER）

基本语法

与其他 MySQL 数据库对象一样，可以使用 DROP 语句将触发器从数据库中删除。

语法格式如下：

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

语法说明如下：

1) 触发器名

要删除的触发器名称。

2) 数据库名

可选项。指定触发器所在的数据库的名称。若没有指定，则为当前默认的数据库。

3) 权限

执行 DROP TRIGGER 语句需要 SUPER 权限。

4) IF EXISTS

可选项。避免在没有触发器的情况下删除触发器。

注意：删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

 

MySQL Event事件（定时任务）是什么？

查看事件是否开启

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

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

mysql> 

开启事件

开启事件主要通过以下两种方式实现。 

1）通过设置全局参数修改

可以使用 SET GLOBAL 命令设定全局变量 event_scheduler 的值，开启或关闭事件。将 event_scheduler 参数的值设置为 ON，表示开启事件；设置为 OFF，则关闭事件。

例如，要开启事件可以在命令行窗口中输入以下命令。

mysql> set global event_scheduler = ON;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> 

MySQL创建事件（CREATE EVENT）

在 MySQL 中，可以通过 CREATE EVENT 语句来创建事件，其语法格式如下：

CREATE EVENT [IF NOT EXISTS] event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

从上面的语法可以看出，CRATE EVENT 语句由多个子句组成，各子句的详细说明如下表所示。

子句	说明
DEFINER	可选 用于定义事件执行时检查权限的用户
IF NOT EXISTS	可选 用于判断要创建的事件是否存在
EVENT event_name	必选 用于指定事件名称，event_name 的最大长度为 64 个字符 如果未指定 event_name，则默认为当前的 MySQL 用户名（不区分大小写）
ON SCHEDULE schedule	必选 用于定义执行的时间和时间间隔 schedule 表示触发点
ON COMPLETION [NOT] PRESERVE	可选 用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
ENABLE | DISABLE | DISABLE ON SLAVE	可选，用于指定事件的一种属性。 其中，关键字 ENABLE 表示该事件是活动的，即调度器检查事件是否必须调用； 关键字 DISABLE 表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该调用； 关键字 DISABLE ON SLAVE 表示事件在从机中是关闭的。 如果不指定以上 3 个选项中的任何一个，默认为 ENABLE
COMMENT 'comment'	可选，用于定义事件的注释
DO event_body	必选 用于指定事件启动时所要执行的代码，可以是任何有效的 SQL 语句、存储过程或者一个计划执行的事件。 如果包含多条语句，则可以使用 BEGIN..END 复合结构

在 ON SCHEDULE 子句中，参数 schedule 的值为一个 AT 子句，用于指定事件在某个时刻发生，其语法格式如下：

AT timestamp [+ INTERVAL interval]...
    | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp[+ INTERVAL interval]...]

参数说明如下：

• timestamp：一般用于只执行一次，表示一个具体的时间点，后面加上一个时间间隔，表示在这个时间间隔后事件发生。
• EVERY 子句：用于事件在指定时间区间内每隔多长时间发生一次，其中 STARTS 子句用于指定开始时间；ENDS 子句用于指定结束时间。
• interval：一般用于周期性执行，表示一个从现在开始的时间，其值由一个数值和单位构成。例如，使用“4 WEEK”表示 4 周，使用“'1:10'HOUR_MINUTE”表示 1 小时 10 分钟。间隔的长短用 DATE_ADD() 函数支配。

interval 参数可以是以下值：

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
    WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
    DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

一般情况下，不建议使用不标准（以上未加粗关键字）的时间单位。

mysql> create event if not  exists t_event_name  on schedule every 5 second on completion preserve do inser
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t_enent;
ERROR 1146 (42S02): Table 'employees.t_enent' doesn't exist
mysql> select * from t_event;
+----+-------+------------+
| id | name  | s_date     |
+----+-------+------------+
|  1 | MYSQL | 2020-06-23 |
|  2 | MYSQL | 2020-06-23 |
|  3 | MYSQL | 2020-06-23 |
|  4 | MYSQL | 2020-06-23 |
|  5 | MYSQL | 2020-06-23 |
+----+-------+------------+
5 rows in set (0.01 sec)

 

 

MySQL查看事件

创建好事件后，用户可以通过以下 3 种方式来查看事件的状态信息：

1. 查看 mysql.event
2. 查看 information_schema.events
3. 切换到相应的数据库后执行 SHOW EVENTS;

以上方式的运行结果基本一致，这里就不一一演示了。下面查看 information_schema.events 表中的事件状态信息。SQL 语句和运行结果如下

mysql> select * from information_schema.events \G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: employees
          EVENT_NAME: t_event_name
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: insert into t_event(name,s_date) values('MYSQL',NOW())
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 5
      INTERVAL_FIELD: SECOND
            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
              STARTS: 2020-06-23 15:38:48
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: PRESERVE
             CREATED: 2020-06-23 15:38:48
        LAST_ALTERED: 2020-06-23 15:38:48
       LAST_EXECUTED: 2020-06-23 15:57:38
       EVENT_COMMENT: 
          ORIGINATOR: 0
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

以上参数说明如下表所示：

 

参数名	说明
EVENT_CATALOG	事件存放目录，一般情况下，值为 def，不建议修改
EVENT_SCHEMA	事件所在的数据库
EVENT_NAME	事件名称
DEFINER	事件的定义者
TIME_ZONE	事件使用的时区，默认是 SYSTEM，不建议修改
EVENT_BODY	一般情况下，值为 SQL，不建议修改
EVENT_DEFINITION	该事件的内容，可以是具体的 INSERT 等 SQL，也可以是一个调用的存储过程
EVENT_TYPE	事件类型，这个参数比较重要，在定义时指定 有两个值：RECURRING 和 ONE TIME RECURRING 表示只要符合条件就会重复执行，RECURRING 类型的事件一般为 NULL，表示该事件的预计执行时间 ONE TIME 只会调用 EXECUTE_AT，针对 one-time 类型的事件有效
INTERVAL_VALUE	针对 RECURRING 类型的事件有效，表示执行间隔长度
INTERVAL_FIELD	针对 RECURRING 类型的事件有效，表示执行间隔的单位，一般是 SECOND，DAY 等值，可参考创建语法
SQL_MODE	当前事件采用的 SQL_MODE
STARTS	针对 RECURRING 类型的事件有效，表示一个事件从哪个时间点开始执行，和 one-time 的 EXECUTE_AT 功能类似。 为 NULL 时表示一符合条件就开始执行
ENDS	针对 RECURRING 类型的事件有效，表示一个事件到了哪个时间点后不再执行，如果为 NULL 就是永不停止
STATUS	一般有三个值，ENABLED、DISABLED 和 SLAVESIDE_DISABLED
ON_COMPLETION	只有两个值，PRESERVE 和 NOT PRESERVE
CREATED	事件的创建时间
LAST_ALTERED	事件最近一次被修改的时间
LAST_EXECUTED	事件最近一次执行的时间，如果为 NULL 表示从未执行过
EVENT_COMMENT	事件的注释信息
ORIGINATOR	当前事件创建时的 server-id，用于主从上的处理，比如 SLAVESIDE_DISABLED
CHARACTER_SET_CLIENT	事件创建时的客户端字符集
COLLATION_CONNECTION	事件创建时的连接字符校验规则
DATABASE_COLLATION	事件创建时的数据库字符集校验规则

 

MySQL修改和删除事件（ALTER/DROP EVENT）

 

修改事件

在 MySQL 中，事件创建之后，可以使用 ALTER EVENT 语句修改其定义和相关属性。

修改事件的语法格式如下：

ALTER EVENT event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

删除事件

 

mysql> drop event if exists t_event_name;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.events \G
Empty set (0.00 sec)

mysql>