mysql存數(shù)組的實(shí)例代碼和方法
發(fā)布日期：2022-03-12 09:45 | 文章來源：站長之家
在很多的情況下，在編寫存儲(chǔ)過程中往往會(huì)用到數(shù)組，但是mysql中存儲(chǔ)過程傳入?yún)?shù)并沒有可以直接傳入數(shù)組的方法。在這種情況下我們只能退而求之或者說換個(gè)方式以字符串形式傳入?yún)?shù)，然后在過程體中把字符串再轉(zhuǎn)成數(shù)組？不過很遺憾告訴你，mysql并沒有直接提供把字符串轉(zhuǎn)數(shù)組的函數(shù)?，F(xiàn)在你是不是有種想打人的感覺呢？不過，不用慌，此路不通，咱走另外的路，總有解決方法的。我們可以把傳入的字符串截取成多個(gè)字符然后傳入到臨時(shí)表中，然后使用游標(biāo)或者直接關(guān)聯(lián)表過濾數(shù)據(jù)。這樣就可以達(dá)到后面預(yù)期的效果了。
下面我們以一個(gè)例子來具體實(shí)踐一下：
1、創(chuàng)建數(shù)據(jù)庫，用于實(shí)例：
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', '一年級(jí)');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('2', '二年級(jí)');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('3', '三年級(jí)');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('4', '四年級(jí)');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('5', '五年級(jí)');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('6', '六年級(jí)');
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', '數(shù)學(xué)', '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', '數(shù)學(xué)', '100', '2');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('6', '英語', '98', '2');
2、需求： 根據(jù)學(xué)生編號(hào)批量刪除學(xué)生信息
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 #允許其他用戶運(yùn)行BEGIN DECLARE e_code INT DEFAULT 0;#初始化報(bào)錯(cuò)碼為0
DECLARE result VARCHAR(256) CHARACTER set utf8;#初始化返回結(jié)果，解決中文亂碼問題
DECLARE arrLength INT DEFAULT 0;/*定義數(shù)組長度*/
DECLARE arrString VARCHAR(1000);/*定義初始數(shù)組字符*/
DECLARE sStr VARCHAR(1000);/*定義初始字符*/
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#遇到錯(cuò)誤后繼續(xù)執(zhí)行;(需要返回執(zhí)行結(jié)果時(shí)用這個(gè))
START TRANSACTION;#啟動(dòng)事務(wù)
SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/*獲得數(shù)組長度*/
SET arrString = arrayStr;
DROP TEMPORARY TABLE IF EXISTS list_tmp;
create temporary table list_tmp(id VARCHAR(32));/*定義臨時(shí)表*/
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 = '請(qǐng)輸入正確的參數(shù)';
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 = '該學(xué)生不存在！';
END IF; IF e_code=1 THEN
ROLLBACK; #回滾
ELSE
COMMIT;
SET result = '該學(xué)生已被刪除成功';
END IF;
SELECT result;
DROP TEMPORARY TABLE IF EXISTS list_tmp;
END $$
DELIMITER ;
說明：在創(chuàng)建存儲(chǔ)過程的時(shí)候，傳入了兩個(gè)參數(shù)，第一個(gè)代表要傳入的數(shù)組字符串形式，第二個(gè)參數(shù)為以什么分割字符串。
聲明初始化變量
DECLARE arrLength INT DEFAULT 0;/*定義數(shù)組長度*/ DECLARE arrString VARCHAR(1000);/*定義初始數(shù)組字符*/ DECLARE sStr VARCHAR(1000);/*定義初始字符*/
獲取傳入?yún)?shù)數(shù)組長度
SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/*獲得數(shù)組長度*/ SET arrString = arrayStr;/*賦值*/
創(chuàng)建臨時(shí)表
DROP TEMPORARY TABLE IF EXISTS list_tmp; create temporary table list_tmp(id VARCHAR(32));/*定義臨時(shí)表*/
截取數(shù)組字符串并依次存入到臨時(shí)表中供后面業(yè)務(wù)使用
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;
注： 存儲(chǔ)過程結(jié)束時(shí)一定要記得刪除臨時(shí)表
不是非常復(fù)雜的業(yè)務(wù)沒有必要用到存儲(chǔ)過程的，本文不是引導(dǎo)大家一定要使用存儲(chǔ)過程，只是讓大家知道有這么一回事！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信