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

2025-03-02 技术教程

本文小编为大家详细介绍“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>createtableifnotexists`my_datas`(`id`int(20)notnullauto_incrementcomment'数据id',`name`varchar(30)defaultnullcomment'姓名',`address`varchar(45)defaultnullcomment'地址',`time`datetimedefaultnullcomment'创建时间',primarykey(`id`))engine=innodbauto_increment=1defaultcharset=utf8mb4;QueryOK,0rowsaffected,1warning(0.33sec)

查看结构是否正确：

mysql>showcolumnsfrom`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|+---------+-------------+------+-----+-------------------+-------------------+4rowsinset(0.00sec)

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

mysql>delimiter//#定义结束符mysql>dropprocedureifexistsaddMyData;->createprocedureaddMyData()#创建一个存储过程，名为：addMyData->begin->declarenumint;->setnum=1;->whilenum<=50#插入50条数据->do->insertinto`my_datas`(id,name,address,time)->values(null,concat('数据_',num,'号'),concat('北京四合院',round(rand()*10),'号'),concat(current_timestamp()));#concat函数拼接信息->setnum=num+1;->end->while;->end//;QueryOK,0rowsaffected,1warning(0.03sec)mysql>delimiter;　　#将语句的结束符号恢复为分号

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

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

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

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

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

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

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

mysql>delimiter!!mysql>createproceduredelete_data(inidsint)#定义一个输入的参数->begin->deletefrom`my_datas`whereid=ids;->end!!QueryOK,0rowsaffected(0.05sec)mysql>delimiter;

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

mysql>calldelete_data(3);#输入指定参数QueryOK,1rowaffected(0.04sec)mysql>select*from`my_datas`limit3;+----+-------------+---------------------+---------------------+|id|name|address|time|+----+-------------+---------------------+---------------------+|1|数据_1号|北京四合院3号|2022-08-2414:21:17||2|数据_2号|北京四合院8号|2022-08-2414:21:17||4|数据_4号|北京四合院3号|2022-08-2414:21:17|+----+-------------+---------------------+---------------------+3rowsinset(0.00sec)

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

(3),out参数的使用

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

mysql>createproceduresums(aint,bint,outcint)->begin->setc=a+b;->end//QueryOK,0rowsaffected(0.03sec)mysql>delimiter;

进行验证：

mysql>callsums(11,25,@s);QueryOK,0rowsaffected(0.01sec)mysql>select@s;#正常输出c的值+------+|@s|+------+|36|+------+

(4),inout参数的使用

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

mysql>delimiter//mysql>createprocedureinout_test(inouttestint)->begin->selecttest;->settest=100;->selecttest;->end->//QueryOK,0rowsaffected(0.07sec)mysql>delimiter;

测试执行过程：

mysql>set@test=52->;QueryOK,0rowsaffected(0.00sec)mysql>callinout_test(@test);+------+|test|+------+|52|+------+1rowinset(0.00sec)+------+|test|+------+|100|+------+1rowinset(0.00sec)QueryOK,0rowsaffected(0.00sec)#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量mysql>select@test;+-------+|@test|+-------+|100|+-------+1rowinset(0.00sec)

注意事项：参数的名字不要和列名相同，不然在过程体中，参数名会当作列名来处理，并且，存储过程命名尽量不要跟一些常用函数命名一样，否则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;QueryOK,0rowsaffected(0.00sec)mysql>SELECT@l;+-------------+|@l|+-------------+|黎治跃在内卷|+-------------+1rowinset(0.00sec)mysql>set@z='做个人吧，黎治跃';QueryOK,0rowsaffected(0.00sec)mysql>select@z;+--------------------------+|@z|+--------------------------+|做个人吧，黎治跃|+--------------------------+1rowinset(0.00sec)mysql>SET@y=5+2+52;QueryOK,0rowsaffected(0.00sec)mysql>select@y;+------+|@y|+------+|59|+------+1rowinset(0.00sec)

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

mysql>createproceduresee()selectconcat(@lzy,'Java全能选手');QueryOK,0rowsaffected(0.02sec)mysql>set@lzy='黎治跃：';QueryOK,0rowsaffected(0.00sec)mysql>callsee();+---------------------------------+|concat(@lzy,'Java全能选手')|+---------------------------------+|黎治跃：Java全能选手|+---------------------------------+1rowinset(0.00sec)QueryOK,0rowsaffected(0.00sec)

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

mysql>CREATEPROCEDUREp1()SET@last_procedure='l1';QueryOK,0rowsaffected(0.02sec)mysql>CREATEPROCEDUREp2()SELECTCONCAT('Lastprocedurewas',@last_procedure);QueryOK,0rowsaffected(0.03sec)mysql>CALLp1();mysql>CALLp2();+-----------------------------------------------+|CONCAT('Lastprocedurewas',@last_proc|+-----------------------------------------------+|Lastprocedurewasl1|+-----------------------------------------------+1rowinset(0.00sec)QueryOK,0rowsaffected(0.00sec)

注意:

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>createprceduretest2(insint)->begin->declarenumint;->setnum=s+1;->ifnum=0then->insertinto`new_table`values(555);->endif;->ifs=0then->update`new_table`sets1=s1+1;->else->update`new_table`sets1=s1+2;->endif;->end;->//QueryOK,0rowsaffected(0.07sec)mysql>delimiter;5.1.2，case语句

mysql>delimiter//mysql>createproceduretest(insbint)->begin->declarenumint;->setnum=sb+1;->casenum->when0then->insertinto`new_table`values(23);->when1then->insertinto`new_table`values(24);->else->insertinto`new_table`values(25);->endcase;->end;->//QueryOK,0rowsaffected(0.06sec)mysql>delimiter;5.2，循环语句5.2.1，while ……end while语句

mysql>delimiter//mysql>createproceduretest()->begin->declarenumint;->setnum=0;->whilenum<6do->insertinto`new_tables`values(num);->setnum=num+1;->endwhile;->end;->//QueryOK,0rowsaffected(0.03sec)mysql>delimiter;5.2.2，repeat……end repeat语句

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

mysql>delimiter//mysql>createproceduretest()->begin->declarenumint;->setnum=0;->repeat->insertinto`new_table`values(num);->setnum=num+1;->untilnum>=5#循环条件->endrepeat;->end;->//QueryOK,0rowsaffected(0.04sec)mysql>delimiter;5.2.3，loop……end loop语句

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

mysql>delimiter//mysql>createproceduretest5()->begin->declarenumint;->setnum=0;->LOOP1:loop->insertinto`new_table`values(num);->setnum=num+1;->ifnum>=5then->leaveLOOP1;->endif;->endloop;->end;->//QueryOK,0rowsaffected(0.04sec)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，删除，添加表字段及默认值

删除表字段
ALTERTABLE表名DROP字段名;
添加表字段
ALTERTABLE表名ADD字段名字段数据类型;
添加表字段默认值
ALTERTABLE表名ALTER字段名SETDEFAULT默认值;
删除表字段默认值
ALTERTABLE表名ALTER字段名DROPDEFAULT;

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

mysql>showcolumnsfromuser;+----------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+----------+-------------+------+-----+---------+----------------+|id|int|NO|PRI|NULL|auto_increment||username|varchar(30)|YES||NULL|||address|varchar(45)|YES||NULL||+----------+-------------+------+-----+---------+----------------+3rowsinset(0.04sec)mysql>altertableuseraddtimedatetime;#添加一个time字段QueryOK,0rowsaffected(0.17sec)Records:0Duplicates:0Warnings:0#在address后面添加一个sex字段mysql>altertableuseraddsextinyint(1)afteraddress;QueryOK,0rowsaffected,1warning(0.41sec)Records:0Duplicates:0Warnings:1mysql>showcolumnsfromuser;+----------+-------------+------+-----+---------+----------------+|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||+----------+-------------+------+-----+---------+----------------+5rowsinset(0.00sec)6.2.2，CHANGE与MODIFY对修改字段的作用

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

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

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

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

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

#使用change仅修改字段名mysql>altertableuserchangeaddressaddressvarchar(40);QueryOK,499rowsaffected(0.38sec)Records:499Duplicates:0Warnings:0#使用modify仅修改字段属性mysql>altertableusermodifyaddressvarchar(45);QueryOK,0rowsaffected(0.06sec)Records:0Duplicates:0Warnings:0#使用change同时修改字段名和字段属性mysql>altertableuserchangeaddressu_addressvarchar(40));QueryOK,499rowsaffected(0.26sec)Records:499Duplicates:0Warnings:0#查看更改后的表结构mysql>showcolumnsfromuser;+-----------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-----------+-------------+------+-----+---------+----------------+|id|int|NO|PRI|NULL|auto_increment||username|varchar(30)|YES||NULL|||u_address|varchar(40)|YES||NULL|||sex|tinyint(1)|YES||NULL|||time|datetime|YES||NULL||+-----------+-------------+------+-----+---------+----------------+5rowsinset(0.01sec)6.2.3，其他修改的使用

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

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

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

mysql>showtablestatuslike'user'\G;***************************1.row***************************Name:userEngine:InnoDBVersion:10Row_format:DynamicRows:499Avg_row_length:131Data_length:65536Max_data_length:0Index_length:0Data_free:0Auto_increment:500Create_time:2022-08-2417:32:27Update_time:NULLCheck_time:NULLCollation:utf8mb4_0900_ai_ciChecksum:NULLCreate_options:Comment:1rowinset(0.03sec)ERROR:Noqueryspecified