How to save array in mysql

Mysql method of storing arrays: first intercept the incoming array string into multiple characters; then pass it into a temporary table; finally use a cursor or directly associate the table to filter the data to achieve mysql storage array purpose.

The problem of weak functionality of mysql stored procedures has always been a concern. Today I will talk about the solution to the problem that Mysql stored procedures cannot pass array type parameters.

Recommended courses: MySQL Tutorial.

In many cases, arrays are often used when writing stored procedures, but there is no way to directly pass in the parameters of stored procedures in mysql into arrays. In this case, we can only retreat or pass the parameters in string form in another way, and then convert the string into an array in the procedure body?

But I'm sorry to tell you that mysql does not directly provide a function to convert a string into an array. Now do you feel like hitting someone? However, don't panic, this road is blocked, let's take another road, there is always a solution. We can intercept the incoming string into multiple characters and pass it into the temporary table, and then use a cursor or directly related tables to filter the data. In this way, the desired effect can be achieved later.

Let’s practice it with an example:

1. Create a database for example:

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. Requirements: Delete student information in batches based on student numbers

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 ;

Note: When creating the stored procedure, two parameters are passed in. The first one represents the array string form to be passed in, and the second parameter is how to split the string.

Declare initialization variables

DECLARE arrLength INT DEFAULT 0;/*定义数组长度*/
DECLARE arrString VARCHAR(1000);/*定义初始数组字符*/
DECLARE sStr VARCHAR(1000);/*定义初始字符*/

Get the length of the incoming parameter array

SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/*获得数组长度*/
SET arrString = arrayStr;/*赋值*/

Create a temporary table

DROP TEMPORARY TABLE IF EXISTS list_tmp;
create temporary table list_tmp(id VARCHAR(32));/*定义临时表*/

Intercept the array string and store it in the temporary table in sequence For later business use

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;

Note: Be sure to delete the temporary table when the stored procedure ends

If the business is not very complex, there is no need to use the stored procedure. This article is not to guide everyone to use it. Stored procedures are just to let everyone know that there is such a thing!

The above is the detailed content of How to save array in mysql. For more information, please follow other related articles on the PHP Chinese website!