天天看點
傳回

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;

注： 存儲過程結束時一定要記得删除臨時表

不是非常複雜的業務沒有必要用到存儲過程的，本文不是引導大家一定要使用存儲過程，隻是讓大家知道有這麼一回事！

存儲 關系型資料庫 mysql 資料存儲通過 關系資料存儲 資料存儲列存儲 mysql存儲過程參數 mysql存儲過程函數觸發器
上一篇: Linux常用指令與伺服器運維，網站釋出與維護
下一篇: docker安裝mongodb

繼續閱讀