MySQL存储过程的使用实现数据快速插入

以你之姓@ 2023-10-04 19:21 8阅读 0赞

**目录**

一，存储过程介绍

二，存储过程的优缺点

优点

缺点

三，存储过程的创建与调用

3.1，存储过程中的常用语法及参数

3.2，存储过程的使用

(1),创建存储函数，向数据表中插入50条数据

(2),in输入参数的使用

(3),out参数的使用

(4),inout参数的使用

四，存储过程中的变量及使用细则

4.1，变量定义

4.2，变量赋值

4.3，用户变量的使用

4.4，存储过程的一些常用查看命令

五，常用的存储过程的控制语句

5.1，条件语句

5.2，循环语句

六，其他相关知识点

6.1，存储过程体

6.2，MySQL AlTER命令对表的灵活操作

6.2.1，删除，添加表字段及默认值

6.2.2，CHANGE与MODIFY对修改字段的作用

6.2.3，其他修改的使用

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

## 一，存储过程介绍 ##

自MySQL 5.0 版本开始支持存储过程。**存储过程（Stored Procedure）**是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想就是数据库 SQL 语言层面的代码封装与重用。类似于Java开发中封装工具类方便以后直接调用的作用。

## 二，存储过程的优缺点 ##

#### 优点 ####

*  存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，k就提升了sQL的执行效率。
 *  可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。
 *  存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
 *  可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
 *  良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的sQL语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

#### 缺点 ####

*  可移植性差。存储过程不能跨数据库移植，比如在MysQL、Oracle和sQL Server里编写的存储过程，在换成其他数据库时都需要重新编写。
 *  调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
 *  存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
 *  它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

## 三，存储过程的创建与调用 ##

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

### 3.1，存储过程中的常用语法及参数 ###

声明语句结束符，可以自定义:

> DELIMITER $$

声明存储过程:

> CREATE PROCEDURE pro\_name(IN num int)

存储过程开始和结束符号:

> BEGIN .... END

变量赋值:

> SET num=1

变量定义:

> DECLARE num int unsigned default 100;

创建mysql存储过程、存储函数:

> create procedure 存储过程名(参数)

存储过程体:

> create function 存储函数名(参数)

参数解析：

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

> CREATEPROCEDURE 存储过程名(\[\[IN |OUT |INOUT \] 参数名 数据类形...\])

*  IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
 *  OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
 *  INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

需要注意的是，这里的参数根据需求而定，如果不需要参数，亦可不填写！

### 3.2，存储过程的使用 ###

下面依次根据实例对不同的情况进行演示：

首先准备一张my\_datas表：

mysql> create table if not exists `my_datas`(
    `id` int(20) not null auto_increment comment '数据id',
    `name` varchar(30) default null comment '姓名',
    `address` varchar(45) default null comment '地址',
    `time` datetime default null comment '创建时间',
    primary key(`id`)
    )engine=innodb auto_increment=1 default charset=utf8mb4;
    Query OK, 0 rows affected, 1 warning (0.33 sec)

查看结构是否正确：

#### (1),创建存储函数，向数据表中插入50条数据 ####

mysql> delimiter //    #定义结束符
    mysql> drop procedure if exists addMyData;
        -> create procedure addMyData()  #创建一个存储过程，名为：addMyData
        -> begin
        -> declare num int;
        -> set num =1;
        -> while num <=50   #插入50条数据
        -> do
        -> insert into `my_datas`(id,name,address,time) 
        -> values(null,concat('数据_',num,'号'),concat('北京四 合院',round(rand()*10),'号'),concat(current_timestamp()));  #concat函数拼接信息
        -> set num =num +1;
        -> end
        -> while;
        -> end //;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    
    mysql> delimiter;　　#将语句的结束符号恢复为分号

默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 DELIMITER //命令将语句的结束符号从分号 ; 临时改为两个//，使得过程体中使用的分号被直接传递到服务器，而不会被客户端解释。

调用存储函数，并查询插入结果

mysql> call addMyData();
    Query OK, 1 row affected (0.58 sec)
    
    mysql> select * from `my_datas`;
    +----+--------------+----------------------+---------------------+
    | id | name         | address              | time                |
    +----+--------------+----------------------+---------------------+
    |  1 | 数据_1号     | 北京四合院3号        | 2022-08-24 14:21:17 |
    |  2 | 数据_2号     | 北京四合院8号        | 2022-08-24 14:21:17 |
    |  3 | 数据_3号     | 北京四合院4号        | 2022-08-24 14:21:17 |
    |  4 | 数据_4号     | 北京四合院3号        | 2022-08-24 14:21:17 |
    |  5 | 数据_5号     | 北京四合院3号        | 2022-08-24 14:21:17 |
    |  6 | 数据_6号     | 北京四合院7号        | 2022-08-24 14:21:17 |
    |  7 | 数据_7号     | 北京四合院7号        | 2022-08-24 14:21:17 |
    |  8 | 数据_8号     | 北京四合院5号        | 2022-08-24 14:21:17 |
    |  9 | 数据_9号     | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 10 | 数据_10号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 11 | 数据_11号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 12 | 数据_12号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 13 | 数据_13号    | 北京四合院6号        | 2022-08-24 14:21:17 |
    | 14 | 数据_14号    | 北京四合院8号        | 2022-08-24 14:21:17 |
    | 15 | 数据_15号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 16 | 数据_16号    | 北京四合院9号        | 2022-08-24 14:21:17 |
    | 17 | 数据_17号    | 北京四合院7号        | 2022-08-24 14:21:17 |
    | 18 | 数据_18号    | 北京四合院8号        | 2022-08-24 14:21:17 |
    | 19 | 数据_19号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 20 | 数据_20号    | 北京四合院9号        | 2022-08-24 14:21:17 |
    | 21 | 数据_21号    | 北京四合院2号        | 2022-08-24 14:21:17 |
    | 22 | 数据_22号    | 北京四合院2号        | 2022-08-24 14:21:17 |
    | 23 | 数据_23号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 24 | 数据_24号    | 北京四合院10号       | 2022-08-24 14:21:17 |
    | 25 | 数据_25号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 26 | 数据_26号    | 北京四合院5号        | 2022-08-24 14:21:17 |
    | 27 | 数据_27号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 28 | 数据_28号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 29 | 数据_29号    | 北京四合院10号       | 2022-08-24 14:21:17 |
    | 30 | 数据_30号    | 北京四合院10号       | 2022-08-24 14:21:17 |
    | 31 | 数据_31号    | 北京四合院0号        | 2022-08-24 14:21:17 |
    | 32 | 数据_32号    | 北京四合院2号        | 2022-08-24 14:21:17 |
    | 33 | 数据_33号    | 北京四合院8号        | 2022-08-24 14:21:17 |
    | 34 | 数据_34号    | 北京四合院6号        | 2022-08-24 14:21:17 |
    | 35 | 数据_35号    | 北京四合院5号        | 2022-08-24 14:21:17 |
    | 36 | 数据_36号    | 北京四合院7号        | 2022-08-24 14:21:17 |
    | 37 | 数据_37号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 38 | 数据_38号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 39 | 数据_39号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 40 | 数据_40号    | 北京四合院6号        | 2022-08-24 14:21:17 |
    | 41 | 数据_41号    | 北京四合院9号        | 2022-08-24 14:21:17 |
    | 42 | 数据_42号    | 北京四合院7号        | 2022-08-24 14:21:17 |
    | 43 | 数据_43号    | 北京四合院9号        | 2022-08-24 14:21:17 |
    | 44 | 数据_44号    | 北京四合院5号        | 2022-08-24 14:21:17 |
    | 45 | 数据_45号    | 北京四合院8号        | 2022-08-24 14:21:17 |
    | 46 | 数据_46号    | 北京四合院3号        | 2022-08-24 14:21:17 |
    | 47 | 数据_47号    | 北京四合院1号        | 2022-08-24 14:21:17 |
    | 48 | 数据_48号    | 北京四合院7号        | 2022-08-24 14:21:17 |
    | 49 | 数据_49号    | 北京四合院10号       | 2022-08-24 14:21:17 |
    | 50 | 数据_50号    | 北京四合院9号        | 2022-08-24 14:21:17 |
    +----+--------------+----------------------+---------------------+
    50 rows in set (0.00 sec)

这样，50条需要的数据就能快速插入完毕。但是局限性在于插入的数据是在定义存储过程中写死的，不够灵活。

#### (2),**in**输入参数的使用 ####

为了方便灵活的插入/修改/删除/查询我们需要的数据，我们可以定义**in** 来输入参数，如下：

创建一个用域删除指定id信息的存储过程

mysql> delimiter !!
    mysql> create procedure delete_data(in ids int)  #定义一个输入的参数
        -> begin
        -> delete from `my_datas` where id=ids;
        -> end !!
    Query OK, 0 rows affected (0.05 sec)
    mysql> delimiter ;

执行存储过程并查看数据库信息情况：

mysql> call delete_data(3);  #输入指定参数
       
    Query OK, 1 row affected (0.04 sec)
    
    mysql> select * from `my_datas` limit 3;
    
    +----+-------------+---------------------+---------------------+
    | id | name        | address             | time                |
    +----+-------------+---------------------+---------------------+
    |  1 | 数据_1号    | 北京四合院3号       | 2022-08-24 14:21:17 |
    |  2 | 数据_2号    | 北京四合院8号       | 2022-08-24 14:21:17 |
    |  4 | 数据_4号    | 北京四合院3号       | 2022-08-24 14:21:17 |
    +----+-------------+---------------------+---------------------+
    3 rows in set (0.00 sec)

在存储过程中设置了需要传参的变量ids，调用存储过程的时候，通过传参将3赋值给ids，然后进行存储过程里的SQL操作。

#### (3),**out**参数的使用 ####

构建一个简单的存储过程：

mysql> create procedure sums(a int ,b int ,out c int)
        -> begin
        -> set c = a+b;
        -> end //
    Query OK, 0 rows affected (0.03 sec)
    mysql> delimiter ;

进行验证：

mysql> call sums(11,25,@s);
    Query OK, 0 rows affected (0.01 sec)
    mysql> select @s;  #正常输出c的值
    +------+
    | @s   |
    +------+
    |   36 |
    +------+

#### (4),**inout**参数的使用 ####

构建一个inout型的存储过程：

mysql> delimiter //
    mysql> create procedure inout_test(inout test int)
        -> begin
        -> select test;
        -> set test=100;
        -> select test;
        -> end
        -> //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> delimiter ;

测试执行过程：

mysql> set @test=52
        -> ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call inout_test(@test);
    +------+
    | test |
    +------+
    |   52 |
    +------+
    1 row in set (0.00 sec)
    
    +------+
    | test |
    +------+
    |  100 |
    +------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    #调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量
    mysql> select @test;
    +-------+
    | @test |
    +-------+
    |   100 |
    +-------+
    1 row in set (0.00 sec)

**注意事项：**参数的名字不要和列名相同，不然在过程体中，参数名会当作列名来处理，并且，存储过程命名尽量不要跟一些常用函数命名一样，否则sql检查会出错。

## 四，存储过程中的变量及使用细则 ##

### 4.1，变量定义 ###

局部变量声明一定要放在存储过程体的开始：

> DECLARE 变量名 数据类型（int，float，date……）

例如：

> DECLARE numbers int
> 
> DECLARE l\_datetime datetime DEFAULT '2022-09-31 16:12:35';
> 
> DECLARE l\_varchar varchar(255) DEFAULT '黎治跃在内卷！';

### 4.2，变量赋值 ###

> SET 变量名 = 表达式值 \[,variable\_name = expression ...\]

### 4.3，用户变量的使用 ###

mysql > SELECT '黎治跃在内卷' into @l;  
    Query OK, 0 rows affected (0.00 sec)
    mysql > SELECT @l;  
    +-------------+  
    |   @l        |  
    +-------------+  
    | 黎治跃在内卷 |  
    +-------------+  
    1 row in set (0.00 sec)
    
    mysql> set @z='做个人吧，黎治跃';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @z;
    +--------------------------+
    | @z                       |
    +--------------------------+
    | 做个人吧，黎治跃         |
    +--------------------------+
    1 row in set (0.00 sec)
    
    mysql> SET @y=5+2+52; 
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @y;
    +------+
    | @y   |
    +------+
    |   59 |
    +------+
    1 row in set (0.00 sec)

存储过程中使用用户变量：

mysql> create procedure see() select concat(@lzy,'Java全能选手');
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> set @lzy ='黎治跃：';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call see();
    +---------------------------------+
    | concat(@lzy,'Java全能选手')     |
    +---------------------------------+
    | 黎治跃：Java全能选手            |
    +---------------------------------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)

在存储过程间传递全局范围的用户变量：

mysql> CREATE PROCEDURE p1()   SET @last_procedure='l1';  
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> CALL p1( );  
    mysql> CALL p2( );  
    +-----------------------------------------------+  
    | CONCAT('Last procedure was ',@last_proc       |  
    +-----------------------------------------------+  
    | Last procedure was l1                         |  
    +-----------------------------------------------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)

**注意:**

*  1、用户变量名一般以@开头
 *  2、滥用用户变量会导致程序难以理解及管理

### 4.4，存储过程的一些常用查看命令 ###

> 查看具体存储过程信息
> 
> show create procedure 存储过程名 \\G
> 
> 查看所有存储过程
> 
> show procedure status \\G
> 
> 模糊查询对应的存储过程信息
> 
> show procedure status like “模糊查询名” \\G
> 
> 查看指定表中的存储信息
> 
> select \* from 表名 where routine\_name = '存储过程名' \\G

## 五，常用的存储过程的控制语句 ##

### 5.1，条件语句 ###

5.1.1，if-then-else

mysql > delimiter //  
    mysql > create prcedure test2(in s int)  
         -> begin 
         -> declare num int;  
         -> set num=s+1;  
         -> if num=0 then 
         -> insert into `new_table` values(555);  
         -> end if;  
         -> if s=0 then 
         -> update `new_table` set s1=s1+1;  
         -> else 
         -> update `new_table` set s1=s1+2;  
         -> end if;  
         -> end;  
         -> //  
    Query OK, 0 rows affected (0.07 sec)
    
    mysql > delimiter ;

5.1.2，case语句

mysql > delimiter //  
    mysql > create procedure test(in sb int)  
         -> begin 
         -> declare num int;  
         -> set num=sb+1;  
         -> case num  
         -> when 0 then   
         -> insert into `new_table` values(23);  
         -> when 1 then   
         -> insert into `new_table` values(24);  
         -> else   
         -> insert into `new_table` values(25);  
         -> end case;  
         -> end;  
         -> //  
    Query OK, 0 rows affected (0.06 sec)
    
    mysql > delimiter ;

### 5.2，循环语句 ###

5.2.1，while ……end while语句

mysql > delimiter //  
    mysql > create procedure test()  
         -> begin 
         -> declare num int;  
         -> set num=0;  
         -> while num<6 do  
         -> insert into `new_tables` values(num);  
         -> set num=num+1;  
         -> end while;  
         -> end;  
         -> //  
    Query OK, 0 rows affected (0.03 sec)
    
    mysql > delimiter ;

5.2.2，repeat……end repeat语句

这个语句与while语句的不同之处在于while是先检查再执行，而repeat语句是执行操作后检查结果。

mysql > delimiter //  
    mysql > create procedure test()  
         -> begin   
         -> declare num int;  
         -> set num=0;  
         -> repeat  
         -> insert into `new_table` values(num);  
         -> set num=num+1;  
         -> until num>=5  #循环条件
         -> end repeat;  
         -> end;  
         -> //  
    
    Query OK, 0 rows affected (0.04 sec)
    
    mysql > delimiter ;

5.2.3，loop……end loop语句

loop循环相当于一个while True ...if ... break 循环，与repeat一循环不同，loop可以在循环体的任何位置通过leave离开循环，而repeat只能在循环体最后进行until判断 。此外loop还提供了循环标签，用于在嵌套·循环中标识不同层次的循环。

mysql > delimiter //  
    mysql > create procedure test5()  
         -> begin 
         -> declare num int;  
         -> set num=0;  
         -> LOOP1:loop  
         -> insert into `new_table` values(num);  
         -> set num=num+1;  
         -> if num >=5 then 
         -> leave LOOP1;  
         -> end if;  
         -> end loop;  
         -> end;  
         -> //  
    Query OK, 0 rows affected (0.04 sec)
    
    mysql > delimiter ;

## 六，其他相关知识点 ##

### 6.1，存储过程体 ###

*  存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
 *  过程体格式：以begin开始，以end结束(可嵌套)

>     BEGIN
>     　　BEGIN
>     　　　　BEGIN
>     　　　　　　statements; 
>     　　　　END
>     　　END
>     END

每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句compound statement)，则不需要分号。

为语句块贴标签:

> label1: BEGIN 　　
> 
> label2: BEGIN 　　
> 
> 　　label3: BEGIN 　　
> 
> 　　　　statements; 　　
> 
> 　　END label3 ; 　
> 
> 　END label2;
> 
> END label1

标签有两个作用：

*  1、增强代码的可读性
 *  2、在某些语句(例如:leave和iterate语句)，需要用到标签

### 6.2，MySQL AlTER命令对表的灵活操作 ###

#### 6.2.1，删除，添加表字段及默认值 ####

> 删除表字段
> 
>     ALTER TABLE 表名  DROP 字段名;
> 
> 添加表字段
> 
>     ALTER TABLE 表名 ADD 字段名 字段数据类型;
> 
> 添加表字段默认值
> 
>     ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
> 
> 删除表字段默认值
> 
>     ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

另外，如果需要将表字段插入指定的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。使用show columns查看表结构变化：

#### 6.2.2，CHANGE与MODIFY对修改字段的作用 ####

当需要修改字段类型或者字段名时，常常会使用到change与modify关键字

> modify使用
> 
> alter table 表名 modify 字段名 字段属性（更改后）

modify主要用于更改数据字段范围，当遇到在数据库构建时，范围数据定义过小，或者范围数据定义过大浪费内存空间时，对字段属性的更改。

> change使用
> 
> alter table 表名 change old字段名 new字段名 对应的字段属性

change关键字主要用于对字段名的更改，在语法上CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。但是CHANGE又**不仅仅可以更改字段名，它也可以同时修改指明后的字段属性**，同时对字段名和字段属性进行修改。

#### 6.2.3，其他修改的使用 ####

> 修改指定表的数据引擎
> 
> alter table user engine=指定数据引擎
> 
> 例如：ALTER TABLE testalter\_tbl ENGINE = MYISAM;

如果对当前数据表信息不清楚的话可以通过SHOW TABLE STATUS命令进行查看。

例如：查看当前user表的信息

mysql> show table status like 'user' \G;
    *************************** 1. row ***************************
               Name: user
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 499
     Avg_row_length: 131
        Data_length: 65536
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
     Auto_increment: 500
        Create_time: 2022-08-24 17:32:27
        Update_time: NULL
         Check_time: NULL
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.03 sec)
    
    ERROR: 
    No query specified

> 修改表名
> 
> alter table old表名 rename to new表名

此外，ALTER关键字的操作也不仅仅局限于操作表，在以后的索引，外键上也有很多作用。

学无止境，留下一笔吧~