mysql中高阶玩法系列（一）

客官°小女子只卖身不卖艺 2021-11-23 05:12 249阅读 0赞

### 目录 ###

*  变量
 *  自定义函数
 *   *  结束符 DELIMITER
 *  存储过程
 *  参考

--------------------

先定义一张表，在之后的讲解中的所有例子，都是基于这张表做的演示，那么就定义一张最常见的用户表吧。

CREATE TABLE `user` (
        `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(45) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
        `pwd` VARCHAR(255) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
        `created_at` INT(8) UNSIGNED NOT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_created_at` (`created_at`)
    )  ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI;

插入几条数据，方便之后快乐的玩耍

INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('1', '牛A', '555', '1559318400');
    INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('2', '牛B', '555', '1559318400');
    INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('3', '牛C', '555', '1573441871');
    INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('4', '牛X', '555', '1558329240');

![在这里插入图片描述][20190725224136243.]  
![在这里插入图片描述][20190725230908971.]

# 变量 #

在mysql中，mysql变量可分为两大类，即系统变量和用户变量。

但根据实际应用又被细化为四种类型，即局部变量、用户变量、会话变量和全局变量。

*  **局部变量**

mysql局部变量，只能用在begin/end语句块中，比如存储过程中的begin/end语句块。

其作用域仅限于该语句块。

# declare语句专门用于定义局部变量，可以使用default来说明默认值
    declare x int default 0;
    
    # 局部变量的赋值方式一
    set x = 666;
    
    # 局部变量的赋值方式二
    select created_at 
    into x
    from user 
    where name = '牛X';

*  **用户变量**

mysql用户变量，mysql中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。同一连接未关闭情况下，帮你暂存一些计算结果，可以将值从一个语句传递到另一个语句

其作用域为当前连接。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放

# 第一种用法，使用set时可以用“=”或“:=”两种赋值符号赋值
    set @x = 19;
    
    set @x := 20;
    
    
    # 第二种用法，使用select时必须用“:=”赋值符号赋值
    select @x := 22;
    
    select @x := created_at 
    from user 
    where name = '牛X';

*  **会话变量**

mysql会话变量，服务器为每个连接的客户端维护一系列会话变量。

其作用域仅限于当前连接，即每个连接中的会话变量是独立的。客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。

# 显示所有的会话变量
    show session variables;
    
    # 设置会话变量的值的三种方式
    set session auto_increment_increment = 1;
    set @@session.auto_increment_increment = 2;
    set auto_increment_increment = 3;        -- 当省略session关键字时，默认缺省为session
    
    # 查询会话变量的值的三种方式
    select @@auto_increment_increment;
    select @@session.auto_increment_increment;
    show session variables like '%auto_increment_increment%';        # session关键字可省略

*  **全局变量**

mysql全局变量，全局变量影响服务器整体操作，当服务启动时，它将所有全局变量初始化为默认值。要想更改全局变量，必须具有super权限。

其作用域为server的整个生命周期。对于全局变量的更改可以被访问该全局变量的任何客户端看见。

# 显示所有的全局变量
    show global variables;
    
    # 设置全局变量的值的两种方式
    set global sql_warnings=ON;        # global不能省略
    set @@global.sql_warnings=OFF;
    
    # 查询全局变量的值的两种方式
    select @@global.sql_warnings;
    show global variables like '%sql_warnings%';

# 自定义函数 #

函数存储着一系列sql语句，调用函数就是一次性执行这些sql语句。所以函数可以降低语句重复。比如我经常性的要查询一个数据，查询的频率较高，那么我就可以自定义一个函数，每次只需要执行一下这个函数就能得到我要的数据了，十分方便。

创建函数语法：

create function 函数名([变量名 数据类型]) returns 数据类型
    begin
    	sql语句;
    	return 值;
    end;

示例：

# 最简单的仅有一条sql的函数
    create function myfunc1() returns int return 123;
    select myfunc1(); 		# 调用函数
    
    
    
    # 无参函数
    create function myfunc2() returns int
    begin 
        declare x int;
        select id from user where name="牛A" into x;
        return x;
    end;
    select myfunc2();
    
    
    
    # 带参函数
    create function myfunc3(who varchar(45)) returns int
    begin 
        declare x int;
        select id from user where name = who into x;
        return x;
    end;
    select myfunc3("牛B");

## 结束符 DELIMITER ##

也许已经有心急的人迫不及待的试一试了，可是都还没执行，就发现一片报红，说什么关键字在该位置无效。  
![在这里插入图片描述][20190725231459974.]

然而，事实上是分号`;`惹的祸。

在MySQL中，分号默认是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，MySQL解释器遇到`;`后就开始执行，这时候就会报出错误，因为没有找到和 BEGIN 匹配的 END。

但有时候，我们不希望MySQL语句马上就被执行，因为可能要输入较多的语句，且语句中包含有分号，所以，一般我们会用到 DELIMITER 命令（DELIMITER 是定界符，结束符的意思），它是一条命令，不需要语句结束标识。

我们会用DELIMITER 命令来修改结束符，默认的是分号（;），一般我们会用`DELIMITER $$`来暂时性的修改结束符，好方便我们后面创建函数或创建存储过程时来和`;`区别开来，让MySQL解释器不会错误识别我们的语句。`DELIMITER`可以用很多符号来作为结束符，比如`DELIMITER //`，`DELIMITER **`等等都是可以的。

像上面创建函数时之所以报错，就是结束符的原因，所以，可以改成以下方式来创建函数就没问题了。

DELIMITER $$		# 修改结束符
    create function myfunc2() returns int
    begin 
        declare x int;
        select id from user where name="牛A" into x;
        return x;
    end;
    $$					# 由于上面修改了结束符为$$，所以MySQL解释器知道到这里为止是可以执行的了
    DELIMITER ;			# 将结束符修改回为; 主要原因是我们还是习惯性的用;作为结束符

查看所有自定义函数

show function status

查看函数创建时的定义语句：

show create function 函数名

删除某自定义函数：

drop function function_name;

注意：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句也不行。

例如：下面定义了一个函数，可是执行的时候，就会报错，说是函数不允许返回一个结果集。

delimiter $$
    create function myfunc()returns int 
    begin
    select * from user;
    return 100;
    end;
    $$
    delimiter ;

![在这里插入图片描述][20190725223217827.]

Error Code: 1415. Not allowed to return a result set from a function

可是在很多场景下，我们需要一个结果集，所以就有了mysql的存储过程，存储过程它允许返回结果集。

# 存储过程 #

在数据库的实际操作中，并非所有操作都那么简单，经常会有一个完整的操作需要多条 SQL 语句处理多个表才能完成。

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

存储过程通常有如下优点：

*  **封装性**

存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

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

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

*  **可减少网络流量**

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

*  **高性能**

存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

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

使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

存储过程的特点：

*  **存储过程就是具有名字的一段代码，用来完成一个特定的功能。**
 *  **创建的存储过程保存在数据库的数据字典中。**

创建存储过程语法：

create procedure 存储过程名([过程参数 变量名 数据类型]) 
    begin
    	sql语句;
    end;

### 过程参数
    MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。
    
    IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
    OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
    INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

示例：

# 无参存储过程
    DELIMITER $$ 		
    create procedure mypro1()
    begin
    	select * from user;
    end;
    $$					
    DELIMITER ;		
    
    
    
    # 调用存储过程
    call mypro1();

执行结果图：  
![在这里插入图片描述][20190728171958113.]

示例：

# 带参存储过程
    DELIMITER $$	
    create procedure mypro2(in who varchar(45))
    begin
    	select * from user where name = who;
    end;
    $$					
    DELIMITER ;	
    
    
    
    # 调用存储过程
    call mypro2('牛C');

执行结果图：  
![在这里插入图片描述][2019072817264894.]

模拟场景应用：

现在我们需要知道用户都是什么节日注册账号的，方便我们知道在什么节日我们系统将会有大量用户注册。

DELIMITER $$
    create procedure testdemo()
    begin
    	select *, from_unixtime(created_at, "%m-%d") as cdate, 
    	case from_unixtime(created_at, "%m-%d")
        when "06-01" then '儿童节'
        when "11-11" then '单身万岁节'
        when "05-20" then '恋爱酸臭节'
        else 'nothing'
    	end as festival from user;
    end;
    $$					
    DELIMITER ;
    
    
    
    # 调用存储过程
    call testdemo();

执行结果图：  
![在这里插入图片描述][20190728180904867.]

查看所有的存储过程

show procedure status;

删除存储过程

drop procedure procedure_name;

注意：如果存储过程或存储函数不存在时，仍然进行删除，可以使用IF EXISTS子句，它可以防止发生错误。一般来说，建议使用下面的命令来删除存储过程。

drop procedure if exists procedure_name;

**自定义函数与存储过程之间存在几点区别**：

*  自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。
 *  自定义函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。
 *  可以直接对自定义函数进行调用而不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。
 *  自定义函数是不能返回结果集的，而存储过程可以返回结果集

# 参考 #

[https://www.runoob.com/w3cnote/mysql-stored-procedure.html][https_www.runoob.com_w3cnote_mysql-stored-procedure.html]  
[https://www.cnblogs.com/progor/p/8871480.html][https_www.cnblogs.com_progor_p_8871480.html]  
[https://www.cnblogs.com/Brambling/p/9259375.html][https_www.cnblogs.com_Brambling_p_9259375.html]  
[http://c.biancheng.net/view/2591.html][http_c.biancheng.net_view_2591.html]

[20190725224136243.]: /images/20211122/4762533c888c40e6ada1f0e7eba62148.png
[20190725230908971.]: /images/20211122/5d240d51846b4b63bdd6c581003421cc.png
[20190725231459974.]: /images/20211122/bf08d60e1b0b4aafbbdef6faec21f995.png
[20190725223217827.]: /images/20211122/0e88014743bd4f53bd0d178ed84500d4.png
[20190728171958113.]: /images/20211122/d771196f548c4859a56d86daef20608c.png
[2019072817264894.]: /images/20211122/e419c01d4c7d4dba9a44f86c13c50e01.png
[20190728180904867.]: /images/20211122/6e23a9ea7c1b4e7386aeb73965334d27.png
[https_www.runoob.com_w3cnote_mysql-stored-procedure.html]: https://www.runoob.com/w3cnote/mysql-stored-procedure.html
[https_www.cnblogs.com_progor_p_8871480.html]: https://www.cnblogs.com/progor/p/8871480.html
[https_www.cnblogs.com_Brambling_p_9259375.html]: https://www.cnblogs.com/Brambling/p/9259375.html
[http_c.biancheng.net_view_2591.html]: http://c.biancheng.net/view/2591.html