«

MySQL存储过程创建使用及实现数据快速插入的方法是什么

时间：2024-8-5 09:34     作者：韩俊     分类： Mysql

本文小编为大家详细介绍“MySQL存储过程创建使用及实现数据快速插入的方法是什么”，内容详细，步骤清晰，细节处理妥当，希望这篇“MySQL存储过程创建使用及实现数据快速插入的方法是什么”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。

一，存储过程介绍

自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)

查看结构是否正确：

mysql> show columns from `my_datas`;
+---------+-------------+------+-----+-------------------+-------------------+
| Field   | Type        | Null | Key | Default           | Extra             |
+---------+-------------+------+-----+-------------------+-------------------+
| id      | int         | NO   | PRI | NULL              | auto_increment    |
| name    | varchar(30) | YES  |     | NULL              |                   |
| address | varchar(45) | YES  |     | NULL              |                   |
| time    | datetime    | YES  |     | NULL              | DEFAULT_GENERATED |
+---------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 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&hellip;&hellip;）

例如：

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 &hellip;&hellip;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&hellip;&hellip;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&hellip;&hellip;end loop语句

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

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查看表结构变化：

mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| address  | varchar(45) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
 
mysql> alter table user add time datetime; #添加一个time字段
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
#在address后面添加一个sex字段
mysql> alter table user add sex tinyint(1) after address;Query OK, 0 rows affected, 1 warning (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| address  | varchar(45) | YES  |     | NULL    |                |
| sex      | tinyint(1)  | YES  |     | NULL    |                |
| time     | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

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

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

modify使用

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

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

change使用

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

change关键字主要用于对字段名的更改，在语法上CHANGE 关键字之后，紧跟着的是你要修

标签: mysql

热门推荐

1 如何使用MySQL的游标处理大数据集的遍历操作
2 sqlite迁移到mysql脚本的方法
3 Mysql 5.6.24安装实例教程
4 MySQL 5.7双主同步部分表的实现过程详解
5 详解MySQL误操作后怎样进行数据恢复
6 mysql zip archive 版本(5.7.19)安装教程详细介绍
7 MySQL5.7.10 安装文档教程详解
8 MySQL日志维护策略汇总
9 MySQL存储过程中的基本函数和触发器的相关学习教程
10 对MySQL慢查询日志进行分析的基本教程

日历

标签

hbase maven eclipse sybase PostgreSQL spring emcache oracle mongodb redis nginx golang java vue android ios mysql linux 微信小程序 css html php教程 javascript python php

搜索

最新文章

热门文章