Mysql存储过程无法传递、转换数组类型参数解决方案

简介: mysql存储过程功能弱问题一直是大家关注的问题,今天讲一下Mysql存储过程无法传递数组类型参数的解决方案。 在很多的情况下,在编写存储过程中往往会用到数组,但是mysql中存储过程传入参数并没有可以直接传入数组的方法。
+关注继续查看

mysql存储过程功能弱问题一直是大家关注的问题,今天讲一下Mysql存储过程无法传递数组类型参数的解决方案。

在很多的情况下,在编写存储过程中往往会用到数组,但是mysql中存储过程传入参数并没有可以直接传入数组的方法。在这种情况下我们只能退而求之或者说换个方式以字符串形式传入参数,然后在过程体中把字符串再转成数组?不过很遗憾告诉你,mysql并没有直接提供把字符串转数组的函数。现在你是不是有种想打人的感觉呢?不过,不用慌,此路不通,咱走另外的路,总有解决方法的。我们可以把传入的字符串截取成多个字符然后传入到临时表中,然后使用游标或者直接关联表过滤数据。这样就可以达到后面预期的效果了。

下面我们以一个例子来具体实践一下:

1、创建数据库,用于实例:


CREATE DATABASE huafeng_db;

use huafeng_db;

DROP TABLE IF EXISTS `huafeng_db`.`t_scores`;
DROP TABLE IF EXISTS `huafeng_db`.`t_students`;
DROP TABLE IF EXISTS `huafeng_db`.`t_class`;

CREATE TABLE `huafeng_db`.`t_class` (
  `class_id` int(11) NOT NULL,
  `class_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('1', '一年级');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('2', '二年级');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('3', '三年级');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('4', '四年级');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('5', '五年级');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('6', '六年级');

CREATE TABLE `t_students` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(32) NOT NULL,
  `sex` int(1) DEFAULT NULL,
  `seq_no` int(11) DEFAULT NULL,
  `class_id` int(11) NOT NULL,
  PRIMARY KEY (`student_id`),
  KEY `class_id` (`class_id`),
  CONSTRAINT `t_students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小红',0,1,'1');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小青',0,2,'2');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小明',1,3,'3');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小兰',0,4,'4');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小米',1,5,'5');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('小白',1,6,'6');

CREATE TABLE `huafeng_db`.`t_scores` (
  `score_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(64) DEFAULT NULL,
  `score` double(3,2) DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`score_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `t_scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `t_students` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('1', '语文', '90', '1');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('2', '数学', '97', '1');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('3', '英语', '95', '1');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('4', '语文', '92', '2');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('5', '数学', '100', '2');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('6', '英语', '98', '2');

2、需求: 根据学生编号批量删除学生信息

DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;
DELIMITER $$
CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN arrayStr VARCHAR(1000),IN sSplit VARCHAR(10))
SQL SECURITY INVOKER  #允许其他用户运行
BEGIN
    DECLARE e_code INT DEFAULT 0;#初始化报错码为0
    DECLARE result VARCHAR(256) CHARACTER set utf8;#初始化返回结果,解决中文乱码问题

    DECLARE arrLength INT DEFAULT 0;/*定义数组长度*/
    DECLARE arrString VARCHAR(1000);/*定义初始数组字符*/
    DECLARE sStr VARCHAR(1000);/*定义初始字符*/
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到错误后继续执行;(需要返回执行结果时用这个)


    START TRANSACTION;#启动事务
    SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/*获得数组长度*/
    SET arrString = arrayStr;
    DROP TEMPORARY TABLE IF EXISTS list_tmp;
    create temporary table list_tmp(id VARCHAR(32));/*定义临时表*/

    WHILE arrLength > 0 DO
      set sStr = substr(arrString,1,instr(arrString,sSplit)-1);            -- 得到分隔符前面的字符串  
      set arrString = substr(arrString,length(sStr)+length(sSplit)+1);     -- 得到分隔符后面的字符串  
      set arrLength = arrLength -1;
      set @str = trim(sStr);
      insert into list_tmp(id) values(@str);
     END WHILE;
     IF row_count()=0 THEN  
        SET e_code = 1;  
        SET result = '请输入正确的参数';  
      END IF;

    set @count = (SELECT count(1) FROM t_students s,list_tmp t WHERE s.seq_no = t.id);
    IF @count >0 THEN
        DELETE FROM t_scores WHERE student_id in (SELECT s.student_id FROM t_students s,list_tmp t WHERE s.seq_no = t.id);
        DELETE FROM t_students WHERE student_id in (SELECT t.id FROM list_tmp t);
    ELSE
         SET e_code = 1;
         SET result = '该学生不存在!';
    END IF;
    IF e_code=1 THEN
        ROLLBACK;  #回滚
    ELSE
        COMMIT;
        SET result = '该学生已被删除成功';
    END IF;
    SELECT result;
    DROP TEMPORARY TABLE IF EXISTS list_tmp;
END $$
DELIMITER ;

说明:在创建存储过程的时候,传入了两个参数,第一个代表要传入的数组字符串形式,第二个参数为以什么分割字符串。

  • 声明初始化变量
DECLARE arrLength INT DEFAULT 0;/*定义数组长度*/
DECLARE arrString VARCHAR(1000);/*定义初始数组字符*/
DECLARE sStr VARCHAR(1000);/*定义初始字符*/
  • 获取传入参数数组长度
SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/*获得数组长度*/
SET arrString = arrayStr;/*赋值*/
  • 创建临时表
DROP TEMPORARY TABLE IF EXISTS list_tmp;
create temporary table list_tmp(id VARCHAR(32));/*定义临时表*/
  • 截取数组字符串并依次存入到临时表中供后面业务使用
WHILE arrLength > 0 DO
  set sStr = substr(arrString,1,instr(arrString,sSplit)-1);            -- 得到分隔符前面的字符串  
  set arrString = substr(arrString,length(sStr)+length(sSplit)+1);     -- 得到分隔符后面的字符串  
  set arrLength = arrLength -1;
  set @str = trim(sStr);
  insert into list_tmp(id) values(@str);
END WHILE;

注: 存储过程结束时一定要记得删除临时表

不是非常复杂的业务没有必要用到存储过程的,本文不是引导大家一定要使用存储过程,只是让大家知道有这么一回事!

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
存储 SQL 关系型数据库
认识MySQL的存储过程
认识MySQL的存储过程。
15 4
|
3天前
|
存储 SQL 关系型数据库
认识MySQL的存储过程
认识MySQL的存储过程。
28 2
|
11天前
|
存储 SQL 关系型数据库
MySQL存储过程和触发器
MySQL存储过程和触发器
8 0
|
14天前
|
存储 SQL 关系型数据库
MySQL存储过程与函数精讲
MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起,应用程序无须关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可。
16 0
|
17天前
|
存储 关系型数据库 MySQL
MySQL的临时表以及视图与存储过程、触发器等功能概念详细解释说明以及它们的使用方法举例?
MySQL的临时表以及视图与存储过程、触发器等功能概念详细解释说明以及它们的使用方法举例?
|
23天前
|
存储 关系型数据库 MySQL
MySQL存储过程中双层游标嵌套循环实例
MySQL存储过程中双层游标嵌套循环实例
|
1月前
|
存储 SQL 关系型数据库
MySQL存储过程与触发器:提升数据库操作效率与数据一致性
本文深入探讨了MySQL数据库中的存储过程与触发器,通过丰富的代码示例,详细介绍了存储过程的定义与调用、参数与变量的应用,以及触发器的创建、使用和实际案例。存储过程作为预定义的一组SQL语句,能够提高数据库操作的效率,实现数据逻辑和复杂计算。同时,触发器作为在特定事件触发时自动执行的SQL语句,能够保障数据一致性和逻辑完整性。通过代码实例,读者将了解如何创建、调用存储过程,如何利用参数和变量进行数据处理,以及如何创建触发器并应用于实际场景。这些技术将使读者能够在数据库管理中更高效地进行操作和保障数据的完整性,为应用程序提供可靠的数据支持。
57 0
|
1月前
|
存储 SQL 关系型数据库
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)下
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)
97 1
|
1月前
|
存储 SQL 关系型数据库
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)上
【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)
32 2
|
1月前
|
存储 SQL NoSQL
[MySQL]存储过程与函数
[MySQL]存储过程与函数
推荐文章
更多