MySQL 学习笔记-第三篇-索引、存储过程和函数、视图、触发器

74 阅读 0 评论 49 点赞

我是靠谱客的博主甜甜白羊，最近开发中收集的这篇文章主要介绍MySQL 学习笔记-第三篇-索引、存储过程和函数、视图、触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1 索引

1.1 索引简介

1.2 创建索引

1.3 删除索引

1.4 MySQL 8.0 的新特性 1 -支持降序索引

1.5 MySQL 8.0 的新特性 2 -统计直方图

2 存储过程和函数

2.1 创建存储过程

2.2 创建存储函数

2.3 变量的使用

2.4 定义条件和处理程序

2.5 光标的使用

2.6 流程控制的使用

2.7 调用存储过程和函数

2.8 查看存储过程和函数

2.9 修改存储过程和函数

2.10 删除存储过程和函数

2.11 MySQL 8.0 新特性-全局变量的持久化

3 视图

3.1 视图的含义

3.2 视图的作用

3.3 创建视图

3.4 查看视图

3.5 修改视图

3.6 更新视图

3.7 删除视图

4 MySQL 触发器

4.1 创建触发器

4.2 查看触发器

4.3 触发器的使用

4.4 删除触发器

1 索引

内容导航》

了解什么是索引
掌握创建索引的方法和技巧
熟悉如何删除索引
熟悉操作索引的常见问题

1.1 索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。

1.1.1 索引的含义和特点

索引是一个单独的、存储在磁盘上的数据结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有 MySQL 列类型都可以被索引，对香港列使用索引是提高查询速度的最佳途径。

索引是在存储引擎层实现的，因此，每种存储引擎的索引都不一定完全相同，MySQL 中索引的存储类型有两种：BETREE 和 HASH ，具体和表的存储引擎有关，MyISAM 和 InnoDB 只支持 BETREE 索引；MEMORY 和 HEAP 存储引擎可以支持 HASH 和 BETREE 索引。

索引的优点主要有以下几点：

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引得分主要原因。
在实现数据参照完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

增加索引也有很多不利的方面，主要表现在以下几个方面：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引占用磁盘空间，如果有大量的索引，索引文件可能比数据文件更快的到达最大文件尺寸。
增、删、改数据时相应的索引也要动态的维护，降低了数据的维护速度。

1.1.2 索引的分类

（1）普通索引和唯一索引

普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引要求索引列的值必须唯一，允许有且仅能有一个空值，如果是唯一索引是组合索引，则列值的组合必须唯一；主键索引是一种特殊的唯一索引，不允许有空值。

（2）单列索引和组合索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引，组合索引满足最左前缀法则，即在查询条件中使用了组合索引的左边字段时，索引就会被使用。

（3）全文索引

全文索引类型位 FULLTEXT ，在定义索引的列上支持的全文查找，允许插入重复值和空值。全文索引可以用在 CHAR、VARCHAR 或 TEXT 类型的列上创建，MySQL 中只有 MyISAM 支持全文索引。

（4）空间索引

这个有兴趣读者可以自己去了解。

1.1.3 索引设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高校的索引对于获得良好的查询性能非常重要。设计索引时，有如下几点原则：

索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，还会影响 INSERT、DELETE 和 UPDATE 等语句的性能，因为表中数据更改时，索引也会相应的调整和更新。
避免对经常更新的表设计过多的索引，索引的列尽量少，用在经常查询的字段上。
数据量小的表最好不要使用索引，由于数据少，全表扫描的时间可能都比走索引的时间快。
在条件表达式中经常用到的不同值较多的列上建立索引，不同值较少的不要建立索引。
当唯一性是某种数据本身的特征时，建立唯一索引。
在频繁进行排序或分组的列上建立索引。

1.2 创建索引

1.2.1 创建表的时候创建索引

使用 CREATE TABLE 创建表时，可以定义主键约束、外键约束或者唯一性约束，无论创建哪种约束，都相当于在该列上创建了索引。

CREATE TABLE table_name 
(

[col_name1 data_type],
[col_name2 data_type],
......
[col_namen data_type],

[UNIQUE | FULLTEXT | SPETIAL] [INDEX | KEY] [index_name](col_namen[(col_length)]) [ASC | DESC]

);

INDEX 和 KEY 是同义词，作用相同，用来指定创建索引；col_name 为需要创建索引的列名；index_name 为索引的名字，如果不填默认和字段名保持一致；col_length 为索引长度，只有在字符串类型的字段才能指定索引长度；ASC 或 DESC 指定以升序或降序的方式存储索引值。

（1）创建普通索引

mysql> CREATE TABLE book
    -> (
    -> bookid  INT NOT NULL,
    -> bookname  VARCHAR(255) NOT NULL,
    -> authors  VARCHAR(255) NOT NULL,
    -> info  VARCHAR(255) NULL,
    -> comment  VARCHAR(255) NULL,
    -> year_publication  YEAR NOT NULL,
    -> INDEX(year_publication)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW CREATE TABLE book G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

使用 EXPLAIN 查看索引是否正在使用：

mysql> EXPLAIN SELECT * FROM book WHERE year_publication = 1997;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | book  | NULL       | ref  | year_publication | year_publication | 1       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT * FROM book WHERE year_publication = 1997 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

这里讲两个参数，在 MySQL 中，SQL 语句后跟上 g 的作用和默认分隔符 ; 作用一样，代表语句结束，而 G 则表示将查询出的表格结果纵向输出，也就是每一行中的每个字段当作一行输出。

EXPLAIN 语句输出结果的各个行解释如下：

id 当前语句序列号
select_type 指定所使用的 SELECT 查询类型，可选值有 SIMPEL、PRIMARY、UNION、SUBQUERY 等。
table指数据表的名字，按被读取的先后顺序排列。
type 指定本数据表与其它数据表之间的关系，可取值有 system、const、eq_ref、ref、range、index 和 ALL。
possible_key 指定了 MySQL 在搜索数据时可选用的各个索引。
key 表示 MySQL 实际选用的索引。
key_len 索引的字节长。
ref 关联关系中另一个表里的数据列名。
rows 预计会读出的行数。
Extra 提供与关联操作有关的额外信息。

（2）创建唯一索引

有效减少查询索引列操作的索引时间。

mysql> CREATE TABLE t1
    -> (
    -> id  INT NOT NULL,
    -> name  CHAR(30) NOT NULL,
    -> UNIQUE KEY unique_idx(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE t1 G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `unique_idx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

（3）创建单列索引

mysql> CREATE TABLE t2
    -> (
    -> id  INT NOT NULL,
    -> name  CHAR(50) NULL,
    -> INDEX  name_idx(name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW CREATE TABLE t2 G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

（4）创建组合索引

mysql> CREATE TABLE t3
    -> (
    -> id  INT NOT NULL,
    -> name  CHAR(30) NOT NULL,
    -> age  INT(11) NOT NULL,
    -> info  VARCHAR(255),
    -> INDEX id_name_age(id, name, age)
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t3 G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `age` int NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

注意：组合索引满足最左前缀原则，可以起到几个索引的作用，但只能利用最左边的列集来匹配。

（5）创建全文索引

只有 MyISAM 支持全文索引 FULLTEXT ，并且只为 CHAR、VARCHAR 和 TEXT 列创建索引，索引总是对整个列进行，不支持局部索引。

mysql> CREATE TABLE t54
    -> (
    -> id    INT NOT NULL,
    -> name  CHAR(31) NOT NULL,
    -> age   INT NOT NULL,
    -> info  VARCHAR(255),
    -> FULLTEXT INDEX info_idx(info)
    -> )ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE t54 G;
*************************** 1. row ***************************
       Table: t54
Create Table: CREATE TABLE `t54` (
  `id` int NOT NULL,
  `name` char(31) NOT NULL,
  `age` int NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `info_idx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

1.2.2 在已存在的表上创建索引

（1）使用 ALTER TABLE 语句创建索引

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPETIAL] [INDEX | KEY] [index_name](col_name [col_length]) [ASC | DESC]

查看我们之前建的 book 表的索引：

mysql> SHOW INDEX FROM book G;
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

ERROR:
No query specified

Table: 索引所属的表。
Non_unique: 索引是否唯一，1 代表是非唯一索引，0 代表是唯一索引。
Key_name: 索引的名字。
Seq_in_index: 表示该字段在索引中的位置，单列索引为 1，组合索引为每个字段在索引定义中的顺序。
Column_name: 表示定义索引的列字段。
Sub_part: 表示索引的长度。
Null: 表示该字段是否能为空值。
Index_type: 索引的类型。

mysql> ALTER TABLE book ADD INDEX comment_idx(comment(50));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book G;
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: comment_idx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

ERROR:
No query specified

（2）使用 CREATE INDEX 创建索引

CREATE [UNIQUE | FULLTEXT | SPETIAL] INDEX index_name ON table_name(col_name[(length)], ...) [ASC | DESC]

KEY 关键字在这里不再适用：

mysql> CREATE UNIQUE KEY bookid_idx ON book(bookid);
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 'KEY bookid_idx ON book(bookid)' at line 1

mysql> CREATE UNIQUE INDEX bookid_idx ON book(bookid);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book G;
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: bookid_idx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: comment_idx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
3 rows in set (0.01 sec)

ERROR:
No query specified

1.3 删除索引

MySQL 中删除索引使用 ALTER TABLE 或者 DROP INDEX 语句，两者都可以实现相同的功能，DROP INDEX 语句在内部被映射到一个 ALTER TABLE 语句中。

1.3.1 使用 ALTER TABLE 删除索引

ALTER TABLE table_name DROP INDEX index_name

mysql> ALTER TABLE book DROP INDEX bookid_idx;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book G;
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: comment_idx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

ERROR:
No query specified

1.3.2 使用 DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name

mysql> DROP INDEX year_publication ON book;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book G;
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: comment_idx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

注意：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除，如果组成索引的所有列都被删除，那么整个索引都会被删除。

1.4 MySQL 8.0 的新特性 1 -支持降序索引

降序索引只是对查询中特定的排序顺序有效，如果使用不当，反而会使查询效率更低。

1.5 MySQL 8.0 的新特性 2 -统计直方图

利用直方图，用户可以对一张表的一列做数据分布的统计，特别是针对没有索引的字段，这可以帮助查询优化器找到更优的执行计划。

直方图能够获得一列的数据分布情况，从而让数据库知道它含有哪些数据，直方图有多种形式，MySQL 支持两种：等宽直方图和等高直方图。直方图的共同特点是，它们都将数据分到了一系列的 buckets中去。MySQL 会自动将数据划分到不同的 buckets 中取，也会自动决定创建哪种类型的直方图。

ANALYSE TABLE table_name [UPDATE HISTOGRAM ON col_name WITH n BUCKETS | DROP HISTOGRAM ON col_name]

buckets 默认值是 100 。统计直方图的信息存储在数据字典 "column_statistcs" 中，可以通过视图information_schema.column_statistcs 访问，直方图以 JSON 格式存储。ANALYSE TABLE 会基于表大小自动判断是否要进行采样操作，也会基于表中列的数据分布情况以及 buckets 的数量来决定是否要建立等宽直方图还是等高直方图。

直方图统计了表中某些字段的数据分布情况，为优化选择高校的执行计划提供参考。直方图有着和索引本质的区别：维护一个索引有代价，每一次 INSERT 、DELETE 、UPDATE 都会需要更新索引，会对性能有一定的影响；而直方图一次创建永不更新，除非明确的去更新它，否则不会影响 INSERT 、DELETE 、UPDATE 的性能。

建立直方图的时候，MySQL 会将所有数据读到内存中，然后在内存中进行操作，包括排序。如果对一个很大的表建立直方图，可能会需要几百兆的数据读到内存中。为了规避这种风险，MySQL 会根据给定的 histogram_generation_max_mem_size 的值计算该将多少行数据读到内存中。设置histogram_generation_max_mem_size 值的方法如下：

SET histogram_generation_max_mem_size = 10000

2 存储过程和函数

简单的说，存储过程就是一条或多条 SQL 语句的集合，可视为批文件，但其作用不仅限于批处理。本章主要介绍如何创建存储过程和存储函数以及变量的使用，如何调用、查看、修改、删除存储过程和存储函数等。

内容导航》

掌握如何创建存储过程
掌握如何创建存储函数
熟悉变量的使用方法
熟悉如何定义条件和处理程序
了解光标的使用方法
掌握流程控制的使用
掌握如何调用存储过程和函数
掌握如何查看存储过程和函数
掌握修改存储过程和函数的方法
掌握如何删除存储过程和函数

2.1 创建存储过程

CREATE PROCEDURE sp_name([proc_parameter])
[characteristics ...] routine_body

/* 

CREATE PROCEDURE 为用来创建存储过程的关键字；

sp_name 为存储过程的名字；

proc_parameter 为参数列表，
其可取值为：[IN | OUT | INOUT] param_name type，
其中 IN 表示输入参数，
OUT 表示输出参数，
INOUT 即可表示输入参数，也可表示输出参数，
param_name 是参数名字，
type 是参数类型，该类型可以是 MySQL 中任意类型。 


characteristics 指定存储过程的特性，有以下取值：

·LANGUAGE SQL: 说明 routine_body 部分是由 SQL 语句组成的；
当前系统支持的语言为 SQL 。SQL 是 LANGUAGE 特性的唯一值。

·[NOT] DETERMINISTIC: 指指明存储过程执行的结果是否确定，
即相同的输入是否会得到相同的输出。

·{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: 
指明子程序 SQL 语句的限制；
CONTAINS SQL 表示子程序包含 SQL 语句；
NO SQL 表示子程序不包含 SQL 语句；
READS SQL DATA 表示子程序包含读数据的语句；
MODIFIES SQL DATA 表示子程序包含写数据的语句；
默认情况下是 CONTAINS SQL。

·SQL SECURITY {DEFINER | INVOKER}: 指明谁有权限来执行；
DEFINER 表示只有定义者才能执行；
INVOKER 表示有权限的调用者可以调用；
默认情况下为 DEFINER。

·COMMENT 'string': 注释信息，可以用来描述存储过程。


routine_body 是 SQL 代码的内容，可以用 BEGIN...END 来表示 SQL 代码的开始和结束。

*/

mysql> DELIMITER $
mysql> CREATE PROCEDURE show_day_expenditure()
    -> BEGIN
    -> SELECT * FROM day_expenditure;
    -> END
    -> $
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;

注意：由于 MySQL 在命令行中的默认分隔符为 ';' ，因此当我们需要在存储过程中使用分隔符时需要一开始就使用 DELIMITER 关键字临时重定义一下分隔符，否则输入 ‘;’ 之后语句就会结束，这样我们是无法完成存储过程的创建的，建议临时定义为正则的结束符 '$' ，定义完存储过程之后记得恢复默认的分隔符，否则调用存储过程或者后续使用 MySQL 命令行时可能会引发混乱。

mysql> CALL show_day_expenditure();
+------+--------------+-----------------------------+--------+---------------------+
| id   | item         | place                       | expend | time                |
+------+--------------+-----------------------------+--------+---------------------+
|    1 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    2 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    3 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    4 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    5 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    6 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    7 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    8 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    9 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|   10 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
| 1000 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
| 1001 | 烧烤         | 青岛精酿                    |    121 | NULL                |
| 1002 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1003 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1004 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1005 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1006 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1007 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1008 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1009 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1010 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1011 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1012 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1013 | 买波罗蜜     | 广发东门                    |     10 | 2022-05-25 19:27:32 |
| 1014 | 买波罗蜜     | 广发东门                    |     15 | 2022-05-25 19:27:32 |
+------+--------------+-----------------------------+--------+---------------------+
25 rows in set (0.01 sec)

Query OK, 0 rows affected (0.07 sec)

2.2 创建存储函数

先要了解一个全局变量，log_bin_trust_function_creators：当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。如果变量设置为1，MySQL不会对创建存储函数实施这些限制。此变量也适用于触发器的创建。

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

CREATE FUNCTION func_name([func_parameter]) RETURNS type
[characteristics ...] routine_body

/*!

CREATE FUNCTION 为用来创建存储函数的关键字。

func_name 为存储函数的名字。

[func_parameter] 为存储函数的参数列表，
其可取值为：[IN] param_name type，
其中 IN 表示输入参数，
param_name 是参数名字，
type 是参数类型，该类型可以是 MySQL 中任意类型。 

RETURNS type 表示函数返回数据的类型。

其它与存储过程一致，这里不再赘述。

*/

mysql> CREATE FUNCTION expend(item_in VARCHAR(20), place_in VARCHAR(20)) RETURNS FLOAT
    -> READS SQL DATA
    -> BEGIN
    -> DECLARE expend_out FLOAT;
    -> SELECT SUM(expend) INTO expend_out FROM day_expenditure GROUP BY item, place HAVING item REGEXP 'item_in' AND place REGEXP '[place_in]';
    -> RETURN expend_out;
    -> END
    -> $
Query OK, 0 rows affected (0.01 sec)

2.3 变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在 BEGIN...END 程序中。

（1）定义变量

在存储过程或函数中使用 DECLARE 定义变量，语法格式如下：

DECLARE var_name [, var_name2, ..., var_namen] data_type [DEFAULT value]

(2) 为变量赋值

定义变量之后，可以为变量赋值改变变量的默认值：

SET var_name = expr, [, var_name2 = expr2, ..., var_namen = exprn]

SELECT col_name [, col_name2, ..., col_namen] INTO var_name [, var_name2, ..., var_namen] table expr

2.4 定义条件和处理程序

特定情况需要特殊处理。这些情况可以是错误或子程序中的一般流程控制。定义情况是事先定义程序运行过程中可能遇到的问题，定义处理程序使我们可以对这些情况做特殊的处理，从而保证存储过程或函数在遇到错误或警告时可以继续执行或进行其它相应的动作，这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

（1）定义情况

DECLARE condition_name CONDITION FOR [condition_type] SQLSTATE [VALUE] sqlstate_value | mysql_error_code

# condition_name: 表示特殊情况的名字
# [condition_type]: 表示特殊情况的类型
# sqlstate_value | mysql_error_code 都可以表示 MySQL 的错误，sqlstate_value 为长度为 5 的字符串类型错误代码，mysql_error_code 为数值类型的错误代码

# 例如：在 ERROR 1142(42000) 中，sqlstate_value = 42000，mysql_error_code = 1142

（2）定义处理程序

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

handler_type: [CONTINUE | EXIT | UNDO]  # 错误处理方式

condition_value:                        # 错误类型
· SQLSTATE [VALUE] sqlstate_value         # 包含五个字符的字符串错误值
· condition_name                          # 我们自己定义的特殊情况名字
· SQLWARNING                              # 匹配所有以 01 开头的 SQLSTATE 错误代码
· NOT FOUND                               # 匹配所有以 02 开头的 SQLSTATE 错误代码
· SQLEXCEPTION                            # 匹配所有没有被 SQLWARNING 和 NOT FOUND 捕获的 SQLSTATE 错误代码
· mysql_error_code                        # 匹配数值类型的错误代码

sp_statement                            # 程序语句段，表示在遇到特定的错误时需要执行的存储过程或函数

mysql> DELIMITER $
mysql> CREATE PROCEDURE primary_conflict_deal()
    -> BEGIN
    -> DECLARE  CONTINUE HANDLER FOR SQLSTATE '23000' SET @primary_key_conflict = 'test_pass';
    -> SET @position = 1;
    -> INSERT INTO day_expenditure VALUES(666, 'test', 'test', 21, NOW());
    -> SET @position = 2;
    -> INSERT INTO day_expenditure VALUES(666, 'test', 'test', 21, NOW());
    -> SET @position = 3;
    -> END
    -> $
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL primary_conflict_deal();
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM day_expenditure;
+------+--------------+-----------------------------+--------+---------------------+
| id   | item         | place                       | expend | time                |
+------+--------------+-----------------------------+--------+---------------------+
|    1 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    2 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    3 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    4 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    5 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    6 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    7 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    8 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|    9 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|   10 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
|  666 | test         | test                        |     21 | 2022-05-28 17:47:59 |
| 1000 | 吃饭         | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
| 1001 | 烧烤         | 青岛精酿                    |    121 | NULL                |
| 1002 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1003 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1004 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1005 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1006 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1007 | 打车         | 上下班                      |     11 | 2022-05-25 17:50:48 |
| 1008 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1009 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1010 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1011 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1012 | 吃饭         | 万科金色领域                |     58 | 2022-05-25 19:22:59 |
| 1013 | 买波罗蜜     | 广发东门                    |     10 | 2022-05-25 19:27:32 |
| 1014 | 买波罗蜜     | 广发东门                    |     15 | 2022-05-25 19:27:32 |
+------+--------------+-----------------------------+--------+---------------------+
26 rows in set (0.00 sec)

mysql> SELECT @position;
+-----------+
| @position |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

注意：@position 是一个用户变量，执行结果等于 3 ，者表明 MySQL 被执行到了程序的末尾，如果没有错误处理程序，子程序中第二个插入语句就会主键冲突而导致整个正序 EXIT 。

@var_name 表示用户变量，使用 SET 语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

2.5 光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

（1）声明光标

DECLARE cursor_name CURSOR FOR select_statement

# cursor_name 表示光标的名称
# select_statement 表示 SELECT 语句的内容，返回一个用于创建光标的结果集

（2）打开光标

OPEN cursor_name

（3）使用光标

FETCH cursor_name INTO var_name [, var_name2, ..., var_namen]

# var_name 表示将光标中的 SELECT 语句查询出的信息存入到该参数中，必须在声明光标前定义好

（4）关闭光标

CLOSE cursor_name

MySQL 中光标只能在存储过程和存储函数中使用。

2.6 流程控制的使用

流程空值语句被用来根据条件控制语句的执行。MySQL 中用来构造控制流程的语句 IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句。

每个流程中可能包含一个单独语句，或是使用 BEGIN...END 构造的复合语句，构造可以被嵌套。

（1）IF 语句

IF expr_condition THEN statement_list

[ELSEIF expr_condition THEN statement_list]

......

[ELSE statement_list]

END IF

/* IF 实现了一个基本的条件构造。
如果 expr_condition 求值为真，
相应的 SQL 语句才会被执行,
如果没有 expr_condition 匹配，
则 ELSE 子句里的语句列表被执行。*/

(2) CASE 语句

格式 1： 

CASE case_expr
     
     WHEN when_value THEN statement_list

     [WHEN when_value THEN statement_list]
 
     ......
     
     [ELSE statement_list]

END CASE 

/*! case_expr 表示表达式变量，决定了哪一个 WHEN 子句会被执行；
when_value 参数表示表达式变量可能的取值，如果某个 when_value 与 case_expr 匹配，则执行后面的 statement_list 。*/

格式 2：

CASE
     
     WHEN expr_condition THEN statement_list

     [WHEN expr_condition THEN statement_list]
 
     ......
     
     [ELSE statement_list]

END CASE 

/* WHEN 语句会逐一执行，直到 expr_condition 表达式为真，然后执行后面的 statement_list 语句。 */

(3) LOOP 语句

LOOP 循环语句用来重复执行某些语句，直到不满足循环条件使用 LEAVE 子句跳出循环过程。

[loop_label:] LOOP
 
statement_list

END LOOP [loop_label]


# [loop_label] 表示 LOOP 语句的标注名称，可以省略；statement_list 表示需要重复执行的语句

例：

DECLARE id INT DEFAULT 0;

add_loop: LOOP

SET id = id + 1;

IF id > 10 THEN LEAVE add_loop;

END IF;

END LOOP add_loop;


例：

DECLARE id INT DEFAULT 0;

add_loop: LOOP

IF id <= 10 THEN SET id = id + 1;

END IF;

END LOOP add_loop;

(4) LEAVE 语句

LEAVE label


# label 表示循环的标志。LEAVE 和 BEGIN...END 或循环一起使用。

(5) ITERATE 语句

ITERATE label

# ITERATE 只可以出现在 LOOP、REPEAT 和 WHILE 语句内。ITERATE 的意思为“再次循环”，label 参数表示循环的标志。ITERATE 语句必须跟在循环标志前面。

CREATE PROCEDURE doiterate()
BEGIN
DACLARE p1 INT DEFAULT 0;
my_loop: LOOP
  SET p1 = p1 + 1;
  IF p1 < 10 THEN ITERATE my_loop;
  ELSEIF p1 > 20 THEN LEAVE my_loop;
  END IF;
  SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END

(6) REPEAT 语句

REPEAT 语句创建一个带条件判断的循环过程，每次执行完语句后会对条件表达式进行判断，如果表达式为真，循环结束；否则重复执行循环中的语句。

[repeat_label:] REPEAT
    statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

DECLARE id INT DEFAULT 0;
REPEAT
SET id = id + 1;
UNTIL id >= 10
END REPEAT;

(7) WHILE 语句

WHILE 语句创建一个带条件判断的循环过程，与 REPEAT 不同，WHILE 在执行时，先对指定的表达式进行，如果为真，就执行循环内的语句，否则退出循环。

[while_label:] WHILE expr_condition DO
    statement_list
END WHILE [while_label]

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
SET i = i + 1;
END WHILE;

2.7 调用存储过程和函数

存储过程必须使用 CALL 语句调用，并且存储过程和数据库相关，如果要执行其它数据库中的存储过程，需要指定数据库名，例如 CALL dbname_procname 。存储函数的调用与 MySQL 中预定义的函数的调用方式相同。

（1）调用用存储过程

CALL sp_name([parameter][, ...])

(2) 调用存储函数

SELECT function_name(param_name [, ...])

2.8 查看存储过程和函数

MySQL 存储了存储过程和函数的状态信息，用户可以使用 SHOW STATUS 语句或 SHOW CREATE 语句来查看，也可以直接从系统的 information_schema 数据库中查询。

（1）SHOW STATUS 语句

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

mysql> SHOW PROCEDURE STATUS LIKE '%conflict%' G;
*************************** 1. row ***************************
                  Db: totest
                Name: primary_conflict_deal
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2022-05-28 17:47:40
             Created: 2022-05-28 17:47:40
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> SHOW FUNCTION STATUS LIKE '%expend%' G;
*************************** 1. row ***************************
                  Db: totest
                Name: expend
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2022-05-28 17:06:28
             Created: 2022-05-28 17:06:28
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
                  Db: totest
                Name: expend1
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2022-05-28 18:03:43
             Created: 2022-05-28 18:03:43
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)

ERROR:
No query specified

(2) SHOW CREATE 语句

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

mysql> SHOW CREATE PROCEDURE primary_conflict_deal G;
*************************** 1. row ***************************
           Procedure: primary_conflict_deal
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `primary_conflict_deal`()
BEGIN
DECLARE  CONTINUE HANDLER FOR SQLSTATE '23000' SET @primary_key_conflict = 'test_pass';
SET @position = 1;
INSERT INTO day_expenditure VALUES(666, 'test', 'test', 21, NOW());
SET @position = 2;
INSERT INTO day_expenditure VALUES(666, 'test', 'test', 21, NOW());
SET @position = 3;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

（3）从 information_schema.Routines 表中查看存储过程和函数的信息

MySQL 中存储过程和函数的信息存储在 information_schema 数据库下的 Routines 表中，可以通过查看该表的记录来查看存储过程和函数。

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME LIKE '%day%' G;
*************************** 1. row ***************************
           SPECIFIC_NAME: show_day_expenditure
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: totest
            ROUTINE_NAME: show_day_expenditure
            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 * FROM day_expenditure;
END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: SQL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2022-05-28 15:17:59
            LAST_ALTERED: 2022-05-28 15:17:59
                SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8mb4
    COLLATION_CONNECTION: utf8mb4_0900_ai_ci
      DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

2.9 修改存储过程和函数

ALTER {PROCEDURE | FUNCTION} sp_name [characterastic ...]

/* sp_name 表示存储过程或函数的名字；

characterastic 表示存储过程或函数的特性，可能的取值有：

·{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}: 
指明子程序 SQL 语句的限制；
CONTAINS SQL 表示子程序包含 SQL 语句；
NO SQL 表示子程序不包含 SQL 语句；
READS SQL DATA 表示子程序包含读数据的语句；
MODIFIES SQL DATA 表示子程序包含写数据的语句；
默认情况下是 CONTAINS SQL。

·SQL SECURITY {DEFINER | INVOKER}: 指明谁有权限来执行；
DEFINER 表示只有定义者才能执行；
INVOKER 表示有权限的调用者可以调用；
默认情况下为 DEFINER。

·COMMENT 'string': 注释信息，可以用来描述存储过程。*/

mysql> SHOW PROCEDURE STATUS LIKE '%conflict%' G;
*************************** 1. row ***************************
                  Db: totest
                Name: primary_conflict_deal
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2022-05-28 17:47:40
             Created: 2022-05-28 17:47:40
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> ALTER PROCEDURE primary_conflict_deal MODIFIES SQL DATA COMMENT '修改 SQL 语句限制为 MODIFIES SQL DATA';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROCEDURE STATUS LIKE '%conflict%' G;
*************************** 1. row ***************************
                  Db: totest
                Name: primary_conflict_deal
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2022-05-30 07:14:38
             Created: 2022-05-28 17:47:40
       Security_type: DEFINER
             Comment: 修改 SQL 语句限制为 MODIFIES SQL DATA
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

2.10 删除存储过程和函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

/*! IF EXISTS 是 MySQL 的一个扩展语句: 
如果存储过程或函数不存在，它可以防止错误发生，产生一个用 SHOW WARNINGS 查看的警告。*/

mysql> DROP FUNCTION IF EXISTS expend;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP FUNCTION IF EXISTS expend;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Note  | 1305 | FUNCTION totest.expend does not exist |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

2.11 MySQL 8.0 新特性-全局变量的持久化

在 MySQL 数据库中，全局变量可以通过 SET GLOBAL 语句来设置。例如，设置服务器语句超时的限制，可以通过设置系统变量 max_execution_time 来实现：

SET GLOBAL max_execution_time = 2000

使用 SET GLOBAL 语句设置的变量值指挥临时生效。数据库重启后，服务器又会从 MySQL 配置文件中读取变量的默认值。

MySQL 8.0 新增了SET PERSIST 命令。例如，设置服务器最大连接数为 1000：

SET PERSIST max_connections = 1000

MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件用其中的配置来覆盖默认的配置文件。

# 使用 SET GLOBAL 方式设置全局变量

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SET GLOBAL max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

# 重启数据库后

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set, 1 warning (0.01 sec)


# 使用 SET PERSIST 设置全局变量

mysql> SET PERSIST max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

# 重启数据库后

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set, 1 warning (0.01 sec)

3 视图

视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的行和列数据，行和列数据来自由定义视图的查询语句所引用的表，并且在引用视图时动态生成。

内容导航》

了解视图的含义和作用
掌握视图的创建方法
熟悉如何查看视图
掌握修改视图的方法
掌握更新视图的方法
掌握删除视图的方法

3.1 视图的含义

视图是一个虚拟表，是从数据库一个或多个表中导出来的逻辑表，也可以从一寸在的视图定义。视图一经定义便存在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存储在基本表里的数据。对视图的操作与对表一样，可以对其查询、修改和删除。当对可更新视图进行修改时，相应的基本表的数据也发生变化，同时，若基本表的数据发生变化，这种变化也会自动映射到视图中。

3.2 视图的作用

与直接从数据表中读取数据，视图有以下优点：

（1）简单化

看到的就是需要的，视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次指定全部的条件。

（2）安全性

通过视图用户只能查询和修改它们所能见到的数据，数据库中其它数据即看不到也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和列上，通过视图，用户可以被限制在数据的不同子集上。

（3）逻辑数据独立性

视图可以帮助用户屏蔽真实表结构变化带来的影响

3.3 创建视图

CREATE [OR UPDATE] [ALGORITHM = {UNDEFINED | MERGE | TEMPLATE}] 
VIEW view_name [(col_list)] AS select_statement 
[WITH [CASCADED | LOCAL] CHECK OPTION]

/*
其中，CREATE [OR UPDATE] 表示创建或修改视图，建议读者直接写全 - CREATE OR UPDATE；
ALGORITHM 是视图选择算法，表示在使用视图的时候要用什么算法去处理查询视图的语句和视图定义的查询语句：
UNDEFINED 表示让系统自己选；
MERGE 表示使用合并算法，即视图定义的查询语句和查询视图的语句合并，这样操作效率最高，但是使用这个算法有一些前提：
视图中的行和基本表中的行具有一对一的关系，即视图定义的查询语句不能包含以下几种情况：
1.使用聚合函数（SUM(),COUNT(),MAX(),MIN(),AVG()）
2.包含 DISTINCT 关键字
3.包含GROUP BY 关键字
4.包含 UNION 或 UNION ALL 关键字
5.仅引用文字值（该情况下，没有基本表）
6.使用多表查询；
TEMPLATE 表示视图定义的查询语句所得到的结果将暂存于临时表中，
然后再在临时表中执行查询视图的相应语句，由于使用了临时表，
所以在 TEMPLATE 算法下，视图是不能进行更新的，否则会报 1288 错，
最关键的是，TEMPLATE 算法由于创建临时表，所以会影响效率，但是创建临时表之后，
就能释放基本表上的锁，与 MERGE 算法相比，基本表上的锁释放速度更快，这样，
使用视图的其它客户端不会被屏蔽过长时间。

CASCADED 是 WITH [CASCADED | LOCAL] CHECK OPTION 语句的默认值，
表示更新视图时要满足所有相关视图和表的约束；
LOCAL 表示更新视图时满足该视图本身定义条件即可。
*/

mysql> CREATE TABLE t (quantity INT, price INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t VALUES(3, 50);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE OR REPLACE ALGORITHM = MERGE VIEW view_t(quantity, price, total) AS SELECT quantity, price, quantity*price FROM t WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM view_t;
+----------+-------+-------+
| quantity | price | total |
+----------+-------+-------+
|        3 |    50 |   150 |
+----------+-------+-------+
1 row in set (0.01 sec)

3.4 查看视图

使用 DESCRIBE 语句查看视图基本信息

DESCRIBE view_name | DESC view_name

mysql> DESC view_t;
+----------+--------+------+-----+---------+-------+
| Field    | Type   | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| quantity | int    | YES  |     | NULL    |       |
| price    | int    | YES  |     | NULL    |       |
| total    | bigint | YES  |     | NULL    |       |
+----------+--------+------+-----+---------+-------+
3 rows in set (0.02 sec)

使用 SHOW TABLE STATUS 语句查看视图基本信息

SHOW TABLE STATUS LIKE 'view_name' G

mysql> SHOW TABLE STATUS LIKE 'view_t' G;
*************************** 1. row ***************************
           Name: view_t
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: 2022-05-31 07:44:37
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.01 sec)

ERROR:
No query specified

执行结果显示，表的说明字段值为 VIEW ，说明该表为视图，其它字段为 NULL ，说明这是一个虚表。同样的语句，查看一下基本表。

mysql> SHOW TABLE STATUS LIKE 't' G;
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-05-31 07:40:28
    Update_time: 2022-05-31 07:40:46
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

ERROR:
No query specified

使用 SHOW CREATE VIEW 语句查看视图的详细信息

SHOW CREATE VIEW view_name G

mysql> SHOW CREATE VIEW view_t G;
*************************** 1. row ***************************
                View: view_t
         Create View: CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t` (`quantity`,`price`,`total`) AS select `t`.`quantity` AS `quantity`,`t`.`price` AS `price`,(`t`.`quantity` * `t`.`price`) AS `quantity*price` from `t` WITH CASCADED CHECK OPTION
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

在 views 表中查看视图的详细信息

在 MySQL 中，information_schema 库下的 views 表中存储了所有视图的定义，通过对 views 表的查询，可以看到数据库中所有视图的详细信息。

mysql> SELECT * FROM information_schema.views WHERE table_name REGEXP 'view_t' G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: totest
          TABLE_NAME: view_t
     VIEW_DEFINITION: select `totest`.`t`.`quantity` AS `quantity`,`totest`.`t`.`price` AS `price`,(`totest`.`t`.`quantity` * `totest`.`t`.`price`) AS `quantity*price` from `totest`.`t`
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

3.5 修改视图

修改视图是指修改数据库中存在的视图，当基本表中的某些字段发生改变时，可以通过修改视图保持与基本表的一致性。

使用 CREATE OR REPLACE VIEW 语句修改视图

CREATE [OR UPDATE] [ALGORITHM = {UNDEFINED | MERGE | TEMPLATE}] 
VIEW view_name [(col_list)] AS select_statement 
[WITH [CASCADED | LOCAL] CHECK OPTION]

mysql> CREATE OR REPLACE VIEW view_t AS SELECT * FROM t;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC view_t;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| quantity | int  | YES  |     | NULL    |       |
| price    | int  | YES  |     | NULL    |       |
+----------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

使用 ALTER 语句修改视图

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

mysql> ALTER VIEW view_t AS SELECT quantity FROM t;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC view_t;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| quantity | int  | YES  |     | NULL    |       |
+----------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

3.6 更新视图

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候基本上都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对基本表增加或删除记录。

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        3 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM view_t;
+----------+
| quantity |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> UPDATE view_t SET quantity = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    50 |
+----------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM view_t;
+----------+
| quantity |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> CREATE OR REPLACE VIEW view_t2(quantity, price, total) AS SELECT quantity, price, quantity*price FROM t;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t values(5, 46);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM view_t;
+----------+
| quantity |
+----------+
|        5 |
|        5 |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM view_t2;
+----------+-------+-------+
| quantity | price | total |
+----------+-------+-------+
|        5 |    50 |   250 |
|        5 |    46 |   230 |
+----------+-------+-------+
2 rows in set (0.00 sec)

mysql> DELETE FROM view_t2 WHERE price = 50;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+----------+-------+
| quantity | price |
+----------+-------+
|        5 |    46 |
+----------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM view_t;
+----------+
| quantity |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

当视图包含以下内容时，视图更新将不会被执行：

（1）视图中不包含基本中被定义为非空的列

（2）在定义视图的 SELECT 语句后的字段列表中使用了数学表达式并且在更新时使用了该字段

（3）在定义视图的 SELECT 语句后的字段列表中使用了聚合函数并且在更新时使用了该字段

（4）在定义视图的 SELECT 语句后的字段列表中使用了 DISDINCT、UNION、TOP、

GROUP BY 或 HAVING 子句，并且在更新时使用了该字段。

3.7 删除视图

DROP VIEW [IF EXISTS] view_name [, view_name2, ..., view_namen] [RESTRICT | CASCADED]

4 MySQL 触发器

MySQL 的触发器和存储过程一样，都是嵌入到 MySQSL 的一段程序。触发器是由事件来触发的某个操作，这些事件包括 INSERT、UPDATE 和 DELETE 语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

内容导航》

了解什么是触发器
掌握创建触发器的方法
掌握查看触发器的方法
掌握触发器的使用技巧
掌握删除触发器的方法

4.1 创建触发器

CREATE [OR REPLACE] TRIGGER trigger_name trigger_time trigger_event 
ON table_name [FOR EACH ROW] trigger_statement

/*
trigger_name: 触发器名字, 注意，触发器名字是数据库级别唯一的，
每个库中不能存在同名触发器，否则会报错：ERROR 1359 (HY000): Trigger already exists
trigger_time: 触发时机 BEFORE | AFTER
trigger_event: 触发事件 INSERT | UPDATE | DELETE
table_name: 触发器所属表
trigger_statement: 触发时执行的程序
FOR EACH ROW: 行级触发，不写则为表级触发
*/

OLD 和 NEW 关键字：

OLD 只出现在 UPDATE 和 DELETE 时，表示要被更改或删除的原来的数据行。
NEW 只出现在 INSERT 和 UPDATE 时，表示插入或要替换成的新数据行。
表级触发或称语句级触发器不能用 NEW 和 OLD 关键字。

创建只有一个执行语句的触发器

mysql> CREATE TABLE commodity
    -> (
    -> id       INT AUTO_INCREMENT PRIMARY KEY,
    -> nums     INT NOT NULL,
    -> price    DECIMAL(10, 2) NOT NULL,
    -> comment  VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER sum_money BEFORE INSERT ON commodity FOR EACH ROW
    -> SET @money = @money + NEW.nums*NEW.price;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER sub_money AFTER DELETE ON commodity FOR EACH ROW
    -> SET @money = @money - OLD.nums*OLD.price;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER fix_money BEFORE UPDATE ON commodity FOR EACH ROW
    -> SET @money = @money - OLD.nums*OLD.price + NEW.nums*NEW.price;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @money = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @money;
+--------+
| @money |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO commodity(nums, price, comment) VALUES(1, 1, '一号商品'), (2, 3, '二号商品'), (4, 5, '三号商品');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT @money;
+--------+
| @money |
+--------+
|  27.00 |
+--------+
1 row in set (0.00 sec)

mysql> UPDATE commodity SET price = 45 WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT @money;
+--------+
| @money |
+--------+
| 187.00 |
+--------+
1 row in set (0.00 sec)

mysql> DELETE FROM commodity WHERE id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @money;
+--------+
| @money |
+--------+
|   7.00 |
+--------+
1 row in set (0.00 sec)

创建有多个执行语句的触发器

CREATE [OR REPLACE] TRIGGER trigger_name trigger_time trigger_event
ON table_name [FOR EACH ROW]
BEGIN
trigger_statement1;
trigger_statement2;
......
trigger_statementn;
END

4.2 查看触发器

利用 SHOW TRIGGERS 语句查看触发器

SHOW TRIGGERS

只能查看当前库所创建的所有触发器，在触发器较少的情况下，使用该语句。

在 MySQL 中，所有触发器的定义都存在 information.schema 库下的 triggers 表中，可以通过 SELECT 查看。

SELECT * FROM information.schema.triggers WHERE condition

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME REGEXP 'sum_money' G;
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: totest
              TRIGGER_NAME: sum_money
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: totest
        EVENT_OBJECT_TABLE: commodity
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SET @money = @money + NEW.nums*NEW.price
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2022-05-31 17:04:40.38
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

4.3 触发器的使用

触发器是与表有关的数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。麻了，一句话，其实触发器就是回调函数，钩子函数一类的东西。

4.4 删除触发器

DROP TRIGGER [schema_name.] trigger_name

最后

以上就是甜甜白羊为你收集整理的MySQL 学习笔记-第三篇-索引、存储过程和函数、视图、触发器的全部内容，希望文章能够帮你解决MySQL 学习笔记-第三篇-索引、存储过程和函数、视图、触发器所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：MySQL 8.0.29 从入门到疯癫
浏览次数：74 次浏览
发布日期：2023-05-23 03:46:01
本文链接：https://www.kaopuke.com/article/k-p-k_14_ujocf0_13_j_22_4.html

MySql学习笔记-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MySQL 学习笔记-第三篇-索引、存储过程和函数、视图、触发器

概述

1 索引

1.1 索引简介

1.2 创建索引

1.3 删除索引

1.4 MySQL 8.0 的新特性 1 -支持降序索引

1.5 MySQL 8.0 的新特性 2 -统计直方图

2 存储过程和函数

2.1 创建存储过程

2.2 创建存储函数

2.3 变量的使用

2.4 定义条件和处理程序

2.5 光标的使用

2.6 流程控制的使用

2.7 调用存储过程和函数

2.8 查看存储过程和函数

2.9 修改存储过程和函数

2.10 删除存储过程和函数

2.11 MySQL 8.0 新特性-全局变量的持久化

3 视图

3.1 视图的含义

3.2 视图的作用

3.3 创建视图

3.4 查看视图

3.5 修改视图

3.6 更新视图

3.7 删除视图

4 MySQL 触发器

4.1 创建触发器

4.2 查看触发器

4.3 触发器的使用

4.4 删除触发器

最后

评论列表共有 0 条评论

发表评论取消回复

MySQL 学习笔记-第三篇-索引、存储过程和函数、视图、触发器

概述

1 索引

1.1 索引简介

1.2 创建索引

1.3 删除索引

1.4 MySQL 8.0 的新特性 1 -支持降序索引

1.5 MySQL 8.0 的新特性 2 -统计直方图

2 存储过程和函数

2.1 创建存储过程

2.2 创建存储函数

2.3 变量的使用

2.4 定义条件和处理程序

2.5 光标的使用

2.6 流程控制的使用

2.7 调用存储过程和函数

2.8 查看存储过程和函数

2.9 修改存储过程和函数

2.10 删除存储过程和函数

2.11 MySQL 8.0 新特性-全局变量的持久化

3 视图

3.1 视图的含义

3.2 视图的作用

3.3 创建视图

3.4 查看视图

3.5 修改视图

3.6 更新视图

3.7 删除视图

4 MySQL 触发器

4.1 创建触发器

4.2 查看触发器

4.3 触发器的使用

4.4 删除触发器

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复