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;
注：?存儲過程結束時一定要記得刪除臨時表
不是非常復雜的業務沒有必要用到存儲過程的，本文不是引導大家一定要使用存儲過程，只是讓大家知道有這么一回事！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
