mysql存数组的实例代码和方法

数据运维 2023-04-29 大白菜程序猿手机阅读

在很多的情况下，在编写存储过程中往往会用到数组，但是mysql中存储过程传入参数并没有可以直接传入数组的方法。在这种情况下我们只能退而求之或者说换个方式以字符串形式传入

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

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

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;

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

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

mysql存数组的实例代码和方法

mysql记录什么意思

MySQL中包含IN子句的语句是怎样执行的

大促活动OceanBase保障及应急预案

OceanBase v3.1.x 将不再更新版本 | 社区月报2024.6

怎么在命令行打开mysql