MySQL 学习笔记（三）

2020-11-25 本文已影响0人 Whyn

索引

视图

视图是一个虚拟表，是从数据库中一个或多个表（或已存在的视图）中抽象而出的一个逻辑表。视图并不会存储真实数据，它包含的只是一个 SQL 查询，视图中的数据来源于其抽象的基本表，通过视图可以更加简单安全的操作基本表。

在关系型数据库中，每张表的结构都对应真实业务中的一个实体信息，因此，当一个大型实体信息内部依赖另一个实体信息时，在关系型数据中的表现就是两个具备关联关系的独立数据表，表的分离存储优点是数据更加内聚，存储空间小，但是增加了复杂性，比如要查看一个大型实体信息完整数据，则必须进行多表连接查询，而视图的出现，就可以消除这种复杂性，只需为这些关联表创建一个视图即可，该视图就可以表达所需的大型实体信息。

对视图的操作，主要有如下内容：

创建视图：视图是一张虚表，其字段可以来自一张表的全部或部分字段，也可以来自多张表的各个字段信息。MySQL 中创建视图使用的语句为CREATE VIEW，其语法如下所示：

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

其中：

CREATE：表示创建新视图。
REPLACE：表示替换已存在的视图。
ALGORITHM：表示视图选择的算法。其中：
- UNDEFINED：表示 MySQL 将自动选择算法。
- MERGE：表示将使用的视图语句和视图定义合并起来，使得视图定义的某一部分取代语句对应的部分。
- TEMPTABLE：表示将视图的结果存入临时表中，然后用临时表来执行语句。
DEFINER和SQL SECURITY：表示视图调用使用的访问权限检测安全上下文。
view_name：表示视图名称。
column_list：表示视图的属性列表（多个字段间使用逗号,进行分隔）。
select_statement：表示SELECT语句，用于选择基本表数据。
[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内。其中：
- CASCADED：该值为默认值，表示更新视图时要满足所有相关视图和表的条件。
- LOCAL：表示更新视图时满足该视图本身定义的条件即可。

举个例子：要求创建如下三个视图：

为表comment创建一个视图，用于操作其字段content
为表comment创建一个视图，视图只有一个字段comment，用于操作comment表中的字段content。
以表article和表comment作为基本表，创建一个视图，要求该视图包含文章的完整信息：标题，正文，评论。

具体代码如下所示：

# article 初始数据
mysql> SELECT * FROM article;
+----+----------------+---------------+---------+
| id | title          | content       | pubTime |
+----+----------------+---------------+---------+
|  1 | first article  | content one   | NULL    |
|  2 | second article | content two   | NULL    |
|  3 | third article  | content three | NULL    |
+----+----------------+---------------+---------+
3 rows in set (0.00 sec)

# comment 初始数据
mysql> SELECT * FROM comment;
+----+------------+------------+
| id | content    | article_id |
+----+------------+------------+
|  1 | comment 1  |          1 |
|  2 | comment 11 |          1 |
|  3 | comment 3  |          3 |
+----+------------+------------+
3 rows in set (0.00 sec)

# 创建视图，不显示指定字段，则视图字段与 SELECT 语句相同
mysql> CREATE VIEW v1_comment AS SELECT content FROM comment;
Query OK, 0 rows affected (0.36 sec)

# 查看视图
mysql> SELECT * FROM v1_comment;
+------------+
| content    |
+------------+
| comment 1  |
| comment 11 |
| comment 3  |
+------------+
3 rows in set (0.00 sec)

# 创建视图，显示指定字段
mysql> CREATE VIEW v2_comment(comment) AS SELECT content FROM comment;
Query OK, 0 rows affected (0.24 sec)

# 查看视图
mysql> SELECT * FROM v2_comment;
+------------+
| comment    |
+------------+
| comment 1  |
| comment 11 |
| comment 3  |
+------------+
3 rows in set (0.00 sec)

# 创建视图（基于多个表）
mysql> CREATE VIEW v_article_info(title, content, comment) AS SELECT title, a.content, c.content FROM article a LEFT OUTER JOIN comment c ON a.id = c.article_id;
Query OK, 0 rows affected (0.36 sec)

# 查看视图
mysql> SELECT * FROM v_article_info;
+----------------+---------------+------------+
| title          | content       | comment    |
+----------------+---------------+------------+
| first article  | content one   | comment 11 |
| first article  | content one   | comment 1  |
| second article | content two   | NULL       |
| third article  | content three | comment 3  |
+----------------+---------------+------------+
4 rows in set (0.00 sec)

查看视图：由于视图也是一张表（虚拟表），因此对于表的查询操作（比如：DESC，SHOW CREATE TABLE，SHOW TABLE STATUS...），同样适用于视图。同时，MySQL 还专门提供了一个用于查询视图详细信息的命令：SHOW CREATE VIEW，其语法如下所示：
```
SHOW CREATE VIEW view_name
```
注：查看视图必须有SHOW VIEW权限，可以通过 MySQL 内置数据库mysql中表user进行查询。

举个例子：查看视图v_article_info的详细信息：
```
mysql> SHOW CREATE VIEW v_article_info\G
*************************** 1. row ***************************
                View: v_article_info
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_article_info` (`title`,`content`,`comment`) AS select `a`.`title` AS `title`,`a`.`content` AS `content`,`c`.`content` AS `content` from (`article` `a` left join `comment` `c` on((`a`.`id` = `c`.`article_id`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)
```
另外，MySQL 中所有视图定义都存储在内置数据库information_schema的表views中，因此也可以通过查询该表查看视图详细信息：
```
SELECT * FROM information_schema.views;
```

修改视图：当视图对应的基本表字段发生变化的时候，可以通过修改视图来保持视图字段与基本表一致，保证后续操作。
MySQL 提供两种方式对视图进行修改：

使用命令CREATE OR REPLACE VIEW来更新视图，其语法如下所示：

CREATE OR REPLACE
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

可以看到，更新视图与创建视图命令完全一致，CREATE OR REPLACE命令在视图存在时，会对视图进行修改；当视图不存在时，则创建视图。

举个例子：修改视图v1_comment对应的基本表comment，将comment.content字段重命名为comment.comment，此时查看下视图v1_comment结果；然后更新视图v1_comment，再查看下v1_comment视图，观察两次视图数据差别：

# 修改表字段名称
mysql> ALTER TABLE comment CHANGE content comment tinytext;
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看视图 => 报错，因此基本表字段被修改
mysql> SELECT * FROM v1_comment;
ERROR 1356 (HY000): View 'whyn.v1_comment' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

# 更新视图
mysql> CREATE OR REPLACE VIEW v1_comment AS SELECT comment FROM comment;
Query OK, 0 rows affected (0.41 sec)

# 查看视图
mysql> SELECT * FROM v1_comment;
+------------+
| comment    |
+------------+
| comment 1  |
| comment 11 |
| comment 3  |
+------------+
3 rows in set (0.01 sec)

由上述例子可以看到，当基本表结构变化时，必须同步更新视图结构，才能使用视图。

使用命令ALTER VIEW来修改视图，其语法如下所示：

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

可以看到，ALTER VIEW和CREATE OR REPLACE VIEW的参数是一模一样的，两者在用法上除了关键字不同，格式是一致的。ALTER VIEW区别于CREATE OR REPLACE VIEW之处在于前者只能用于修改系统中已存在的视图。

举个例子：修改视图v_article_info，以保证同步更新到表comment的最新结构：

# 查看视图 => 由于基本表 comment 被修改，因此查看失败
mysql> SELECT * FROM v_article_info;
ERROR 1356 (HY000): View 'whyn.v_article_info' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

# 修改视图，同步字段信息
mysql> ALTER VIEW v_article_info(title, content, comment) AS
    -> SELECT title, content, comment FROM article LEFT OUTER JOIN comment ON article.id = comment.article_id;
Query OK, 0 rows affected (0.40 sec)

# 查看视图
mysql> SELECT * FROM v_article_info;
+----------------+---------------+------------+
| title          | content       | comment    |
+----------------+---------------+------------+
| first article  | content one   | comment 11 |
| first article  | content one   | comment 1  |
| second article | content two   | NULL       |
| third article  | content three | comment 3  |
+----------------+---------------+------------+
4 rows in set (0.00 sec)

更新视图数据：视图是一张虚拟表，因此可以使用表数据操作来实现对视图数据的增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT）。其中：

对视图的增加、修改和删除数据操作，都会同步转到基本表中进行；
对基本表的增加、修改和删除数据操作，都会同步更新到视图中。

注：并非所有的视图都是可更新的，基本上来说，如果 MySQL 无法确实定位到被更新的基数据，则不允许更新操作。通常来说，定义了以下行为的视图是不能进行更新的：分组（GROUP BY）、连接、子查询、组合（UNION）、聚集函数、DISTINCT和计算列（COUNT()等）...

举个例子：对基本表comment进行增加（或修改、删除）操作，查看该操作下视图v1_comment的数据变化；
然后对视图v1_comment进行删除（或增加、修改）操作，查看该操作下基本表comment的数据变化：

# 基本表 comment 初始数据
mysql> SELECT * FROM comment;
+----+------------+------------+
| id | comment    | article_id |
+----+------------+------------+
|  1 | comment 1  |          1 |
|  2 | comment 11 |          1 |
|  3 | comment 3  |          3 |
+----+------------+------------+
3 rows in set (0.01 sec)

# 视图 v1_comment 初始数据
mysql> SELECT * FROM v1_comment;
+------------+
| comment    |
+------------+
| comment 1  |
| comment 11 |
| comment 3  |
+------------+
3 rows in set (0.00 sec)

# 基本表 comment 添加一条数据
mysql> INSERT INTO  comment(comment, article_id) VALUES ('comment view test', 2);
Query OK, 1 row affected (0.38 sec)

# 基本表 comment 查询
mysql> SELECT * FROM comment;
+----+-------------------+------------+
| id | comment           | article_id |
+----+-------------------+------------+
|  1 | comment 1         |          1 |
|  2 | comment 11        |          1 |
|  3 | comment 3         |          3 |
|  6 | comment view test |          2 |
+----+-------------------+------------+
4 rows in set (0.00 sec)

# 视图 v1_comment 查询 => 可以看到，基本表增加数据，视图会同步进行更新
mysql> SELECT * FROM v1_comment;
+-------------------+
| comment           |
+-------------------+
| comment 1         |
| comment 11        |
| comment 3         |
| comment view test |
+-------------------+
4 rows in set (0.00 sec)

# 视图 comment 删除一条数据
mysql> DELETE FROM v1_comment WHERE comment = 'comment view test';
Query OK, 1 row affected (0.36 sec)

# 查看视图 => 数据已被删除
mysql> SELECT * FROM v1_comment;
+------------+
| comment    |
+------------+
| comment 1  |
| comment 11 |
| comment 3  |
+------------+
3 rows in set (0.01 sec)

# 查看基本表 => 可以看到，当视图删除数据后，会同步该操作到基本表中
mysql> SELECT * FROM comment;
+----+------------+------------+
| id | comment    | article_id |
+----+------------+------------+
|  1 | comment 1  |          1 |
|  2 | comment 11 |          1 |
|  3 | comment 3  |          3 |
+----+------------+------------+
3 rows in set (0.00 sec)

综上，对基本表和视图的修改操作，都会同步到彼此之中。

删除视图：删除一个或多个视图采用的命令为DROP VIEW。其语法如下所示：

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

举个例子：删除视图v2_comment：

# 搜索表/视图
mysql> SHOW tables LIKE 'v2%';
+----------------------+
| Tables_in_whyn (v2%) |
+----------------------+
| v2_comment           |
+----------------------+
1 row in set (0.00 sec)

# 删除视图
mysql> DROP VIEW IF EXISTS v2_comment;
Query OK, 0 rows affected (0.37 sec)

# 删除视图成功
mysql> show tables like 'v2%';
Empty set (0.00 sec)

存储过程

存储过程是数据库用于封装一系列 SQL 语句集合的批处理程序单元，并且支持传入参数（入参），传出参数（出参），主要用来对一些具备重复性复杂操作进行封装，简化操作，并且存储过程创建后保存的是预编译结果，后续的调用过程无需再次编译，性能更加高效...

MySQL 中，存储过程程序主要包含『存储过程（Stored Procedure）』、『存储函数（Stored Function）』、『触发器（Trigger）』、『事件（Event）』和『视图（View）』，本章我们只关注存储过程和存储函数，其他存储过程程序详情可查看：Stored Objects

存储过程可以使用命令CREATE PROCEDURE进行创建，然后使用关键字CALL进行调用，存储函数可以通过命令CREATE FUNCTION进行创建，其可直接进行调用，就像调用 MySQL 内置函数。两者的区别是：存储函数内部必须包含有一个RETURN语句，并且该RETURN语句只能返回单个值或者表对象，而存储过程不允许执行RETURN，但是可以通过输出参数OUT返回多个值...

所有的存储过程和存储函数都存储在服务器上，客户端只需发送存储过程名称和相应参数就可以在服务器上进行调用，因此在大数据量情况下，存储过程能大幅提升效率。

对存储过程程序的操作，主要包含增删改查以及调用，具体如下：

增：创建存储过程和创建存储函数方法如下：
- 创建存储过程：创建存储过程使用的命令为CREATE PROCEDURE，其语法如下所示：
```
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
```
  其中：
  - DEFINER：用于指定具备执行权限的用户。
  - sp_name：表示存储过程的名称。
  - proc_parameter：表示存储过程的参数列表，其格式如下所示：
```
[ IN | OUT | INOUT ] param_name type
```
    其中：
    - IN：表示输入参数（可以理解为按值传递）。
    - OUT：表示输出参数（可以理解为按引用传递，因此可作为返回值）。
    - INOUT：表示输入输出参数。
    - param_name：表示参数名称。
    - type：表示参数类型，该类型可以是 MySQL 数据库中任意类型。
  - characteristic：表示存储过程特性，其值有如下可选：
```
characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}
```
    其中：
    - COMMENT：表示注释信息，可用来描述存储过程或函数。
    - LANGUAGE SQL：表示routine_body部分是由 SQL 语句组成。
    - [NOT] DETERMINISTIC：指明存储过程执行结果是否是确定的。DETERMINISTIC表示每次相同的输入，执行结果都是相同的输出（确定）；而NOT DETERMINISTIC表示相同的输入可能得到不同的输出（不确定）。默认为NOT DETERMINISTIC。
    - { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用 SQL 语句的限制，其默认值为CONTAINS SQL。其中：
      - CONTAINS SQL：表示子程序包含 SQL 语句，但是不包含读写数据的语句。
      - NO SQL：表示子程序不包含 SQL 语句。
      - READS SQL DATA：表示子程序包含读数据的语句。
      - MODIFIES SQL DATA：表示子程序包含写数据的语句。
    - SQL SECURITY { DEFINER | INVOKER }：用于指定具备执行权限的用户，其默认值为DEFINER。其中：
      - DEFINER：表示只有定义者才能执行。
      - INVOKER：表示拥有权限的调用者可以执行。
  - routine_body：表示存储过程主体，即 SQL 语句集合，其格式如下所示：
```
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]
```
    其中：
    - begin_label/end_label：表示代码块开始/结束标签，end_label必须与begin_label相同，但也可忽略不写。
    - BEGIN：标识代码块开始。
    - END：标识代码块结束。
    注：routine_body中BEGIN和END支持多重嵌套，其中每条 SQL 语句必须以分号;进行结尾。
    注：routine_body中支持变量定义和赋值，流程控制，条件判断等高级程序语言功能，具体内容请参考后文。
    
    举个例子：创建一个存储过程showArticle，用于查看表article所有内容：
```
mysql> DELIMITER //                   # 临时更改分隔符
mysql> CREATE PROCEDURE showArticle() # 创建存储过程
    -> BEGIN
    -> SELECT * FROM article;
    -> END //
Query OK, 0 rows affected (1.06 sec)

mysql> DELIMITER ;                    # 恢复 SQL 语句分隔符
mysql> CALL showArticle();            # 调用存储过程
+----+----------------+---------------+---------+
| id | title          | content       | pubTime |
+----+----------------+---------------+---------+
|  1 | first article  | content one   | NULL    |
|  2 | second article | content two   | NULL    |
|  3 | third article  | content three | NULL    |
+----+----------------+---------------+---------+
3 rows in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)
```
    注：由于 MySQL 命令行程序默认的 SQL 语句分隔符为分号;，而存储过程主体代码块中每条 SQL 语句必须以分号;进行结尾，因此默认情况下我们无法直接发送完整的存储过程给到服务器端，解决的办法就是临时更改分隔符，比如DELIMITER //，其中，任何字符都可以用作语句分隔符，除了反斜杠\，因为\是 MySQL 内置的转义字符。
- 创建存储函数：创建存储函数使用的命令为CREATE FUNCTION，其语法如下所示：
```
CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
```
  其中：RETURNS type表示存储函数返回的类型，该类型可以是 MySQL 数据库中的任意类型。其他参数与CREATE PROCEDURE命令一致。
  
  举个例子：定义一个存储函数showArticleFunc，该函数返回表article的第一条数据记录的title：
```
mysql> DELIMITER //
mysql> CREATE FUNCTION showArticleFunc()           # 创建存储函数
    -> RETURNS VARCHAR(50)
    -> READS SQL DATA
    -> NOT DETERMINISTIC
    -> BEGIN
    -> RETURN (SELECT title FROM article LIMIT 1);
    -> END //
Query OK, 0 rows affected (0.26 sec)

mysql> DELIMITER ;
mysql> SELECT showArticleFunc();                   # 调用存储函数
+-------------------+
| showArticleFunc() |
+-------------------+
| first article     |
+-------------------+
1 row in set (0.01 sec)
```
  注：默认情况下，存储函数必须指明至少一个DETERMINISTIC，NO SQL或READS SQL DATA，否则出错。
- 调用：对于存储过程，使用关键字CALL进行调用。
  对于存储函数，直接像内置函数一样进行调用。
- 查：查看存储过程和存储函数有如下三种方法：
  - 使用命令SHOW STATUS进行查看，其语法如下所示：
```
SHOW { PROCEDURE | FUNCTION } STATUS [LIKE 'pattern' | WHERE expr]
```
  - 使用命令SHOW CREATE进行查看，其语法如下所示：
```
SHOW CREATE { PROCEDURE | FUNCTION } sp_name
```
  - MySQL 中存储过程和存储函数的信息存储在内置数据库information_schema的表Routines中，可以通过查看该表的记录来查询存储过程和存储函数的信息，其格式如下所示：
```
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'sp_name'
```
  注：要查看系统中存在的所有存储过程和存储函数，可以使用如下命令：
```
SELECT routine_name FROM information_schema.Routines;
```
- 改：修改存储过程和存储函数可以使用ALTER语句，其语法如下所示：
```
ALTER { PROCEDURE | FUNCTION } sp_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}
```
- 删：删除存储过程和存储函数可以借助DROP命令，其语法如下所示：
```
DROP { PROCEDURE | FUNCTION } [IF EXISTS] sp_name
```

下面介绍下存储过程和存储函数主体块（即routine_body中的BEGIN...END块）支持的一些高级语言语法特性：

变量：在 MySQL 中，主要存在六种类型变量：『局部变量』,『用户变量』,『会话变量』,『全局变量』,『持久化变量』和『参数』。对变量的操作主要包含『定义』与『赋值』，其中：

定义：对于不同的类型变量，其定义方式不同，具体如下所述：
- 局部变量：局部变量只能在存储过程BEGIN...END块中使用，局部变量的定义使用DECLARE语句，其语法如下所示：
```
DECLARE var_name [, var_name] ... type [DEFAULT value]
```
- 用户变量：用户自定义变量以@为前缀，可以使用SET命令进行设置，其语法如下所示：
```
SET @var_name = expr [, @var_name = expr] ...
```
  注：用户变量无需声明与定义，直接使用即可。
  注：用户变量在当前会话中有效，因此可以跨存储过程进行访问。
  
  举个例子：如下所示：
```
mysql> SET @v1 = 'user defined variable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @v1;
+-----------------------+
| @v1                   |
+-----------------------+
| user defined variable |
+-----------------------+
1 row in set (0.00 sec)
```
- 会话变量：会话变量由系统提供，只在当前会话中有效，其语法如下所示：
```
@@session.val_name
```
  其中：
  - 查看系统所有会话变量：可使用如下命令：
```
SHOW SESSION VARIABLES [like_or_where]
```
  - 查看指定会话：可使用如下命令：
```
SELECT @@session.val_name
```
- 全局变量：全局变量由系统提供，在整个 MySQL 服务器内都有效，其语法如下所示：
```
@@global.val_name
```
  其中：
  - 查看系统所有全局变量：可使用如下命令：
```
SHOW GLOBAL VARIABLES [like_or_where]
```
  - 查看指定全局变量值：可使用如下命令：
```
SELECT @@global.val_name
```
- 持久化变量：与全局变量一样，持久化变量全局有效。其语法如下所示：
```
@@PERSIST.val_name
```
  注：全局变量设置在数据库重启后，会失效，而持久化变量其实就是将全局变量配置到mysqld-auto.cnf文件中，下次启动时，加载该配置文件，让配置一直有效。
- 参数：参数是直接在创建存储过程或存储函数时直接定义，其格式如下所示：
```
[ IN | OUT | INOUT ] param_name type
```
其中：IN表示入参，OUT表示出参，INOUT表示出参入参，具体内容可参考上文。

注：参数具体内容是由客户端调用时传递给存储过程程序的。

赋值：MySQL 支持两种变量赋值方法：

SELECT...INTO命令：可以通过SELECT...INTO命令查询指定记录，并将结果赋值给一个或多个变量，其语法如下所示：

SELECT col_name[,...] INTO var_name[,...] table_expr;

比如：

mysql> SELECT * FROM (VALUES ROW('hello',3)) AS tb INTO @var1, @var2;
Query OK, 1 row affected (0.15 sec)

mysql> SELECT @var1, @var2;
+-------+-------+
| @var1 | @var2 |
+-------+-------+
| hello |     3 |
+-------+-------+
1 row in set (0.00 sec)

注：上述示例中，VALUES关键字用于创建一个表，ROW()函数用于填充一行的内容，所以VALUES ROW('hello', 3)实际上是创建了一个表，如下所示：

mysql> VALUES ROW('hello', 3);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
| hello    |        3 |
+----------+----------+
1 row in set (0.00 sec)

SET命令：为变量赋值，可以使用SET命令，其语法如下所示：

SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

其中：

局部变量 / 参数：其赋值方法如下：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test1( IN var1 VARCHAR(50) ) # 参数
    -> BEGIN
    -> DECLARE var2 VARCHAR(50) DEFAULT 'undefined'; # 定义局部变量
    -> SET var2 = var1;                              # 局部变量赋值
    -> SET var1 = 'change argument value';           # 参数赋值
    -> SELECT var1, var2;
    -> END //
Query OK, 0 rows affected (0.34 sec) 

mysql> CALL test1(@myvar) // # 调用存储过程，此时 @myvar = NULL
+-----------------------+------+
| var1                  | var2 |
+-----------------------+------+
| change argument value | NULL |
+-----------------------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myvar // # IN 参数是按值传递，故函数内更改，不影响外部变量
+--------+
| @myvar |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)

mysql> SET @myvar = 'user variable' //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL test1(@myvar) //
+-----------------------+---------------+
| var1                  | var2          |
+-----------------------+---------------+
| change argument value | user variable |
+-----------------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

用户变量：其赋值方法如下：
```
SET @myvar = expr;
```

会话变量：其赋值方法如下所示：

# 法一：使用 SESSION 关键字
SET SESSION sql_mode = 'TRADITIONAL';

# 法二：使用 LOCAL 关键字
SET LOCAL sql_mode = 'TRADITIONAL';

# 法三：使用前缀 @@
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
# 可忽略 SESSION 或 LOCAL 关键字
SET @@sql_mode = 'TRADITIONAL';
# 可忽略 @@ 前缀
SET sql_mode = 'TRADITIONAL';

全局变量：其赋值方法如下所示：

# 法一：使用 GLOBAL 关键字
SET GLOBAL max_connections = 1000;

# 法二：使用前缀 @@
SET @@GLOBAL.max_connections = 1000;

持久化变量：其赋值方法如下所示：

# 法一：使用 PERSIST 关键字
SET PERSIST max_connections = 1000;

# 法二：使用前缀 @@
SET @@PERSIST.max_connections = 1000;

流程控制：流程控制语句可以根据条件判断来控制语句的执行流程。MySQL 中流程控制语句可以分为如下几类：

条件判断：包含两种条件判断语句：

IF：其语法如下所示：

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

其中：当search_condition为真（TRUE）时，执行THEN语句，否则执行ELSEIF或ELSE语句。

举个例子：如下所示：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test(IN var VARCHAR(20))
    -> BEGIN
    -> IF var IS NULL THEN
    -> SELECT 'argument is null';
    -> ELSE
    -> SELECT CONCAT('argument exists: ',var);
    -> END IF;
    -> END //
Query OK, 0 rows affected (0.31 sec)

mysql> DELIMITER ;
mysql> CALL test(@var);
+------------------+
| argument is null |
+------------------+
| argument is null |
+------------------+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> SET @var = 'hello';
Query OK, 0 rows affected (0.04 sec)

mysql> CALL test(@var);
+---------------------------------+
| concat('argument exists: ',var) |
+---------------------------------+
| argument exists: hello          |
+---------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

CASE：其语法如下所示：

# 格式一
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

# 格式二
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

举个例子：如下所示：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test(IN var INT)
    -> BEGIN
    -> CASE @var
    -> WHEN 1 THEN SELECT 1;
    -> WHEN 2 THEN SELECT 2;
    -> ELSE SELECT 'unknown';
    -> END CASE;
    -> END //
Query OK, 0 rows affected (0.38 sec)

mysql> DELIMITER ;

mysql> CALL test(@var);
+---------+
| unknown |
+---------+
| unknown |
+---------+
1 row in set (0.13 sec)

Query OK, 0 rows affected (0.13 sec)

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

mysql> CALL test(@var);
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

循环：MySQL 提供三种循环控制语句，如下所示：

WHILE循环：其语法如下所示：

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

举个例子：循环打印数字 1 ~ 10：

mysql> DELIMITER //
mysql> CREATE PROCEDURE test(OUT result CHAR(20))
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> DECLARE str CHAR(20) DEFAULT '1';
    -> WHILE i < 10 DO
    -> SET i = i+1;
    -> SET str = CONCAT(str, ',', i);
    -> END WHILE;
    -> SET result = str;
    -> END //
Query OK, 0 rows affected (0.23 sec)

mysql> DELIMITER ;
mysql> CALL test(@var1);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT @var1;
+----------------------+
| @var1                |
+----------------------+
| 1,2,3,4,5,6,7,8,9,10 |
+----------------------+
1 row in set (0.00 sec)

注：由于字符串拼接函数CONCAT()参数只要有一个为NULL，则其返回值为NULL，因此这里创建一个带有默认值的局部变量str，用于进行拼接，避免外部变量直接拼接（因为外部变量可能为NULL），最后再赋值给外部变量即可确保拼接成功。

LOOP死循环：其语法如下所示：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

REPEAT重复：其语法如下所示：

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

举个例子：循环打印数字 1 ~ 10：

mysql> DELIMITER //
mysql> CREATE FUNCTION test()
    -> RETURNS VARCHAR(30)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> DECLARE str VARCHAR(30) DEFAULT '1';
    -> REPEAT
    -> SET i = i + 1;
    -> SET str = CONCAT(str, '-', i);
    -> UNTIL i>=10 END REPEAT;
    -> RETURN str;
    -> END //
Query OK, 0 rows affected (1.15 sec)

mysql> DELIMITER ;
mysql> SELECT test();
+----------------------+
| test()               |
+----------------------+
| 1-2-3-4-5-6-7-8-9-10 |
+----------------------+
1 row in set (0.04 sec)

中断控制语句：中断跳转控制语句主要有三个：

LEAVE：该语句用于跳出循环，与高级语言（比如 Java）的break功能一致。其语法如下所示：
```
LEAVE label
```
INTERATE：该语句用于继续执行循环，与高级语言（比如 Java）的continue功能一致。其语法如下所示：
```
ITERATE label
```
RETURN：该语句用于结束存储过程函数，并返回一个值。其语法如下所示：
```
RETURN expr
```
注：RETURN只用于存储函数，不作用于其他存储程序（如存储过程，触发器，事件等）。
注：存储函数至少包含一个RETURN语句。

举个例子：存储一个存储函数，其内有一个死循环loop，要求打印出 0 ~ 9 个数：

mysql> DELIMITER //
mysql> CREATE FUNCTION test()
    -> RETURNS VARCHAR(30)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE i INT DEFAULT 0;
    -> DECLARE str VARCHAR(30) DEFAULT '';
    -> label_loop: LOOP
    -> IF i < 10 THEN
    -> SET str = CONCAT(str,' ',i);
    -> SET i = i + 1;
    -> ITERATE label_loop;          # 继续循环
    -> END IF;
    -> LEAVE label_loop;            # 跳出 loop 死循环
    -> END LOOP label_loop;
    -> RETURN str;
    -> END //
Query OK, 0 rows affected (0.36 sec)

mysql> DELIMITER ;
mysql> SELECT test();
+----------------------+
| test()               |
+----------------------+
|  0 1 2 3 4 5 6 7 8 9 |
+----------------------+
1 row in set (0.00 sec)

条件处理：在存储过程程序运行过程中，可能会遇到一些突发场景需要进行处理，比如产生异常、发生错误等，此时可以使用一些特殊的Condition来捕获这些异常，并进行处理（由该Condition指向的处理器Handler进行处理）。这里就涉及到Condition的定义与处理器Handler的使用，具体如下：
- 条件定义：定义Condition的语法如下所示：
```
DECLARE condition_name CONDITION FOR condition_value

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
}
```
  注：条件定义必须位于游标和处理器Handler声明之前。
  其中：
  - condition_name：表示条件的名称。
  - condition_value：表示错误码。其有两种取值范围：
    - mysql_error_code：一个数值类型的错误码。
      注：不要使用0作为错误码，因为 MySQL 中0表示成功，而不是错误标识。 \
    - sqlstate_value：一个长度为5的字符串类型错误代码。
      注：不要使用以'00'开头的SQLSTATE错误码，因此 MySQL 将其视为成功而不是错误。
  注：MySQL 内置的完整错误码表（包含mysql_error_code及其对应的sqlstate_value）可查看：Server Error Message Reference
  
  举个例子：要求定义"ERROR 1148(42000)"错误，名称为command_not_allowed，可使用如下两种方式定义：
```
# 方法一：使用 sqlstate_value
DECLARE command_not_allowed CONDITION FOR '42000';

# 方法二：使用 mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
```
- 处理器Handler：定义处理器的语法如下所示：
```
DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}
```
  处理器可用于处理一个或多个条件Condition，当与其绑定的条件产生时，statement块就会被调用。其中：
  - handler_action：表示处理器在执行完statement语句块后采取的操作，其值有：
    - CONTINUE：表示忽略错误，继续执行。
    - EXIT：表示遇到错误后，退出定义当前处理器的BEGIN...END块。
    - UNDO：表示遇到错误后撤回之前的操作。MySQL 中暂不支持该操作。
  - condition_value：表示错误类型。其取值范围有如下可选：
    - mysql_error_code：匹配数值类型错误码。
    - sqlstate_value：匹配长度为5的字符串类型错误码。
    - condition_name：表示匹配以DECLARE...Condition定义的错误条件名。
    - SQLWARNING：匹配以所有01开头的SQLSTATE错误码。
    - NOT FOUND：表示匹配所有以02开头的SQLSTATE错误码。
    - SQLEXCEPTION：表示匹配所有除SQLWARNING和NOT FOUND之外的SQLSTATE错误码。
  - statement：表示当一个或多个绑定条件产生时，触发执行的处理器代码块。该代码块可以是简单的一条语句，也可以是BEGIN...END构成的多语句块。

综上：DECLARE...CONDITION语句其实就是命名（condition_name）了一个错误条件（错误条件其实就是一个错误码（condition_value）），通常我们还需要定义一个处理器Handler绑定这个错误条件，这样当系统产生这个错误条件时，处理器就能进行捕获并处理。

举个例子：创建一个存储过程，并为其添加'23000'错误捕获，输出信息提示：

mysql> CREATE TABLE tmp (
    -> id INT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (1.29 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE handle23000()
    -> BEGIN
    -> # 创建错误捕获处理
    -> DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'detected ERROR(23000)';
    -> SET @x = 1;
    -> INSERT INTO tmp VALUES(1);
    -> SET @x = 2;
    -> INSERT INTO tmp VALUES(1); # error occured
    -> SET @x = 3;
    -> END //
Query OK, 0 rows affected (0.35 sec)

mysql> DELIMITER ;
mysql> CALL handle23000();
+-----------------------+
| detected ERROR(23000) |
+-----------------------+
| detected ERROR(23000) |
+-----------------------+
1 row in set (0.15 sec)

Query OK, 0 rows affected (0.15 sec)

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

存储过程handle23000在第二次进行插入操作时，由于表tmp已存在相同主键，此时 MySQL 会抛出错误'23000'，然后处理器就会捕获到该错误，从而执行SELECT 'detected ERROR(23000)'语句，又由于处理器我们设置了EXIT，即遇到错误代码后，就直接退出当前代码块（BEGIN...END），因此会话变量@x的值为2，而如果将EXIT改为CONTINUE，则遇到错误后，会继续执行后续代码，因此此时的@x = 3。

注：可以为错误码23000定义名称，更加语义化：

CREATE PROCEDURE handle23000()
BEGIN
DECLARE whyn_primary_key_already_exists CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR whyn_primary_key_already_exists SELECT 'detected ERROR(23000)';
...
END //

游标（cursor）：如果查询语句返回多条记录，则可以使用游标来逐条读取结果集记录。
对游标的操作依次包含如下：
- 声明：声明游标使用DECLARE关键字，其语法如下所示：
```
DECLARE cursor_name CURSOR FOR select_statement
```
  其中：
  - cursor_name：表示游标的名称。
  - select_statement：表示查询语句SELECT，其返回的就是游标需要进行遍历的结果集。
  注：游标的声明必须在处理器Handler之前，在变量和条件声明之后。
- 打开：打开游标使用OPEN关键字，其语法如下：
```
OPEN cursor_name
```
- 使用：打开游标后，就可以使用该游标遍历结果集。其语法如下所示：
```
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
```
  其中：
  - cursor_name：表示游标的名称。
  - var_name：表示游标查询出来的一条记录赋值给到的变量。
    注：FETCH的参数个数（即var_name）必须匹配游标SELECT返回结果集的列数。
  使用游标遍历结果集时，游标会自动将当前遍历到的一行记录赋值给相应参数，然后自动遍历下一行记录，依次重复上述步骤，直至遍历到结果集末尾，此时会抛出一个没有数据的Condition（即'02000'），我们可以设置一个Handler来检测该条件（或者检测NOT FOUND条件）。
- 关闭：关闭游标使用CLOSE关键字，其语法如下：
```
CLOSE cursor_name
```
  注：如果没有显示关闭游标，则在超过游标定义的BEGIN...END块后，会自动进行关闭。
综上，游标的一套组合拳就是：声明 -> 打开 -> 使用（遍历）-> 关闭。

举个例子：遍历表article，要求依次输出该表的所有内容：标题 + 正文 + 评论：
```
mysql> DELIMITER //
mysql> CREATE PROCEDURE traverseArticle()
    -> BEGIN
    -> # 遍历完成标识
    -> DECLARE done BOOLEAN DEFAULT FALSE;
    -> DECLARE _title VARCHAR(50) DEFAULT '';
    -> DECLARE _content TEXT DEFAULT '';
    -> DECLARE _comment TINYTEXT;
    ->
    -> # 定义游标
    -> DECLARE mycursor CURSOR FOR SELECT title, content, comment FROM article LEFT OUTER JOIN comment ON article.id = comment.article_id;
    -> # 游标遍历到尾部产生的错误条件
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->
    -> OPEN mycursor;
    ->
    -> label_loop_article: LOOP
    -> FETCH mycursor INTO _title, _content, _comment;
    -> # 直接输出
    -> SELECT _title, _content, _comment;
    ->
    -> IF done THEN
    -> LEAVE label_loop_article;
    -> END IF;
    -> END LOOP;
    -> CLOSE mycursor;
    -> END //
Query OK, 0 rows affected (0.26 sec)

mysql> DELIMITER ;

mysql> CALL traverseArticle();
+---------------+-------------+------------+
| _title        | _content    | _comment   |
+---------------+-------------+------------+
| first article | content one | comment 11 |
+---------------+-------------+------------+
1 row in set (0.00 sec)

+---------------+-------------+-----------+
| _title        | _content    | _comment  |
+---------------+-------------+-----------+
| first article | content one | comment 1 |
+---------------+-------------+-----------+
1 row in set (0.00 sec)
...
```
注：在 MySQL 中，布尔类型BOOLEAN其实就是TINYINT(1)类型，其中：0为false，非0为true。

更多存储过程内容，可以参考：

官网：Compound Statement Syntax
面向MySQL存储过程编程（详细）

触发器

触发器（trigger）是一个特殊的存储过程程序，它预先定义了六个事件，分别为BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE和AFTER DELETE，当 MYSQL 对表执行INSERT、UPDATE或DELETE操作时，会自动触发相应触发器执行相应操作。
注：存储过程需手动使用CALL进行调用，而触发器是监听某些事件自动被触发执行的。

对触发器的操作包含如下内容：

创建：创建触发器的语法如下所示：

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

注：只有表才支持触发器，临时表和视图均不支持。

其中：

DEFINER：指触发器触发时进行访问检测所使用的账户。
trigger_name：表示触发器的名称。
trigger_time：表示触发时机，其值可为BEFORE或AFTER二选一。
trigger_event：表示触发事件，包括INSERT、UPDATE和DELETE可选。
tbl_name：表示触发器关联的表名，即在哪张表上建立触发器。
trigger_order：表示触发顺序，可以指定当前触发器在指定触发器触发前/后再触发。
trigger_body：表示触发器执行语句。该语句可以是一个简单的语句，也可以是一个语句块（BEGIN...END）。

注：trigger_time和trigger_event两两结合，总共可生成以下六种事件：

BEFORE INSERT/AFTER INESRT：在INSERT触发器代码内，可引用一个名为NEW的虚拟表，该表内容为要进行插入的数据。
注：对于AUTO_INCREMENT列，NEW在before insert中为0，在after insert中包含新的自动生成值。
注：在BEFORE INSERT事件中，可通过更改NEW表中的相关字段来更改插入的值。
**BEFORE DELETE/ **AFTER DELETE：在DELETE触发器代码内，可以应用一个名为OLD的虚拟表，该表内容为要进行删除的数据。
注：OLD表中数据均为只读数据，不能进行修改。
**BEFORE UPDATE/ **AFTER UPDATE：在UPDATE触发器代码内，可以引用一个名为NEW和OLD的虚拟表，NEW表中包含更新的数据，而OLD表中包含旧的数据（暂未被更新的数据）。
注：在BEFORE UPDATE事件中，可通过更改NEW表中的相关字段来更改插入的值。

举个例子：创建一个表tmp，为其添加所有的触发器，分别为表执行INSERT、UPDATE和DELETE操作，查看相应触发器情况：

# 建表
mysql> CREATE TABLE tmp (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> data VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (1.23 sec)

# INSERT Trigger
mysql> CREATE TRIGGER tri_before_insert BEFORE INSERT ON tmp FOR EACH ROW SET @before_insert = CONCAT(NEW.id, ' ', NEW.data);
Query OK, 0 rows affected (1.48 sec)

mysql> CREATE TRIGGER tri_after_insert AFTER INSERT ON tmp FOR EACH ROW SET @after_insert = CONCAT(NEW.id, ' ', NEW.data);
Query OK, 0 rows affected (0.30 sec)

# DELETE Trigger
mysql> CREATE TRIGGER tri_before_delete BEFORE DELETE ON tmp FOR EACH ROW SET @before_delete = CONCAT(OLD.id, ' ', OLD.data);
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TRIGGER tri_after_delete AFTER DELETE ON tmp FOR EACH ROW SET @after_delete = CONCAT(OLD.id, ' ', OLD.data);
Query OK, 0 rows affected (0.32 sec)

mysql> DELIMITER //

# UPDATE Trigger
mysql> CREATE TRIGGER tri_before_update BEFORE UPDATE ON tmp FOR EACH ROW
    -> BEGIN
    -> SET @before_update_old = CONCAT(OLD.id, ' ', OLD.data);
    -> SET @before_update_new = CONCAT(NEW.id, ' ', NEW.data);
    -> END //
Query OK, 0 rows affected (0.68 sec)

mysql> CREATE TRIGGER tri_after_update AFTER UPDATE ON tmp FOR EACH ROW
    -> BEGIN
    -> set @after_update_old = CONCAT(OLD.id, ' ', OLD.data);
    -> SET @after_update_new = CONCAT(NEW.id, ' ', NEW.data);
    -> END //
Query OK, 0 rows affected (0.27 sec)

mysql> DELIMITER ;

# 执行插入操作
mysql> INSERT INTO tmp(data) VALUES ('first data');
Query OK, 1 row affected (0.17 sec)

# 查看 INSERT 触发器
mysql> SELECT @before_insert, @after_insert;
+----------------+---------------+
| @before_insert | @after_insert |
+----------------+---------------+
| 0 first data   | 1 first data  |
+----------------+---------------+
1 row in set (0.00 sec)

# 执行更新操作
mysql> UPDATE tmp SET data = 'update first data' WHERE data = 'first data';
Query OK, 1 row affected (0.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看 UPDATE 触发器
mysql> SELECT @before_update_old, @before_update_new, @after_update_old, @after_update_new;
+--------------------+---------------------+-------------------+---------------------+
| @before_update_old | @before_update_new  | @after_update_old | @after_update_new   |
+--------------------+---------------------+-------------------+---------------------+
| 1 first data       | 1 update first data | 1 first data      | 1 update first data |
+--------------------+---------------------+-------------------+---------------------+
1 row in set (0.00 sec)

# 执行删除操作
mysql> DELETE FROM tmp WHERE data = 'update first data';
Query OK, 1 row affected (0.42 sec)

# 查看 DELETE 触发器
mysql> SELECT @before_delete, @after_delete;
+---------------------+---------------------+
| @before_delete      | @after_delete       |
+---------------------+---------------------+
| 1 update first data | 1 update first data |
+---------------------+---------------------+
1 row in set (0.00 sec)

注：当表被删除时，其关联的触发器也都会自动被删除。

查看：MySQL 中可以通过如下两种方式查看触发器相关信息：
- 通过命令SHOW TRIGGERS进行查看。其语法如下所示：
```
SHOW TRIGGERS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]
```
  其中：pattern匹配的是表名，而不是触发器名。
- 在 MySQL 中，所有触发器的定义都存储在内置数据库information_schema中的triggers表中，因此可以通过查询该表来查看触发器信息：
```
SELECT * FROM information_schema.triggers WHERE trigger_name = 'trigger_name'
```
  注：同样的，如果想查看系统中存在的所有触发器名称，可使用如下命令：
```
SELECT trigger_name FROM information_schema.triggers;
```

删除：删除触发器使用如下命令：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

其他

MySQL 的内容很多，以下记录一些边角料内容：

区分大小写搜索：默认情况下，字符匹配搜索是不区分大小写。如果想区分大小写，可以采用如下方式：
```
SELECT * FROM `table_name` WHERE `column` = CONVERT('value' using utf8mb4) COLLATE utf8mb4_bin;
```
其中：
- table_name：表示要进行查询的表名。
- column：表示字段名。
- CONVERT('value' using utf8mb4)：表示将要查询的值value转换为utf8mb4编码。
- COLLATE utf8mb4_bin：表示排序或比较使用的编码为utf8mb4_bin，这里需要注意的是：COLLATE指定的编码集必须与数据表的编码集一致，否则表中的数据编码与指定查询的编码不一致，可能会导致结果出错。
注：可以通过命令SHOW COLLATION查看 MySQL 内置的编码集，其中：
- 以_ci结尾的编码集表示大小写无关，即 Case Insensitive 的缩写。
- 以_bin结尾的编码集表示将字符串看作二进制字符串，然后从最高位到最低位依次进行比对，显然这是一种大小写区分的比对。
更多COLLATE详细内容，可参考：MYSQL中的COLLATE是什么？
查看当前使用的数据库：SELECT DATABASE()
全局变量持久化：在 MySQL 中，全局变量可以通过SET GLOBAL语句进行设置，如下所示：
```
SET GLOBAL MAX_EXECUTION_TIME = 2000
```
但是SET GLOBAL设置的变量值在数据库重启后，会失效，如果需要持久化全局变量设置，可以使用SET PERSIST语句，比如：
```
SET PERSIST MAX_EXECUTION_TIME = 2000
```
注：SET PERSIST语句是 MySQL 8.0 提供的新特性。

使用SET PERSIST命令设置的配置内容会被保存到数据目录下的mysqld-auto.cnf配置文件中，MySQL 在启动时会读取该文件，这样就能覆盖默认配置文件内容。

注：可通过以下命令查看系统变量：
```
SHOW VARIABLES LIKE 'pattern'
```

附录

Docker 安装 mysql/mysql-server：其配置步骤如下所示：
1. 下载最新镜像文件：
```
$ docker pull mysql/mysql-server
```
2. 创建容器启动 mysql-server 镜像：
```
$ docker run --name mysql --restart on-failure -p 3306:3306 -d  mysql/mysql-server
```
3. 查看 MySQL 日志获取自动生成的 ROOT 密码：
```
$ docker logs mysql | grep GENERATED --color=auto
GENERATED ROOT PASSWORD: Axegh3kAJyDLaRuBemecis&EShOs
```
4. 登录到容器内的 MySQL，输入上述步骤获取的密码：
```
$ docker exec -it mysql mysql -uroot -p
```
5. 首次登录成功后，进行其他操作前，需要手动更改 ROOT 密码：
```
# 将 root 用户密码更改为 password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
```
以上，就已完成 mysql-server 的配置。

MySQL 学习笔记（三）

索引

视图

存储过程

触发器

其他

附录

参考

猜你喜欢

热点阅读