Shulou(Shulou.com)05/31 Report--

How to store arrays in mysql? In response to this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.

1. Create a database for instance:

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 ', ' Grade 1');INSERT INTO `huafeng_db`.` t_class` (`class_id`, `class_name`) VALUES ('2 ', ' Year 2');INSERT INTO `huafeng_db`.` t_class` (`class_id`, `class_name`) VALUES ('3 ', ' Grade 3');INSERT INTO `huafeng_db`.` t_class` (`class_id`, `class_name`) VALUES ('4 ', ' Grade 4');INSERT INTO `huafeng_db`.` t_class` (`class_id`, `class_name`) VALUES ('5 ', ' Grade 5');INSERT INTO `huafeng_db`.` t_class`(`class_id`, `class_name`) VALUES ('6 ', ' 6th'); 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 ('little red', 0,1,'1');INSERT INTO `huafeng_db`.` t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES ('little green', 0,2,'2');INSERT INTO `huafeng_db`.` t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES ('small ming', 1,3,'3');INSERT INTO `huafeng_db`.` t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES ('small lan', 0,4,'4');INSERT INTO `huafeng_db`.` t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES ('millet ',1,5,'5');INSERT INTO `huafeng_db`.` t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES ('small white', 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 ', ' language','90',' 1');INSERT INTO `t_scores`(`score_id`, `course_name`, `score`, `student_id`) VALUES ('2 ', ' Math','97',' 1');INSERT INTO `t_scores`(`score_id`, `course_name`, `score`, `student_id`) VALUES ('3 ', ' English','95',' 1');INSERT INTO `t_scores`(`score_id`, `course_name`, `score`, `student_id`) VALUES ('4 ', ' language','92',' 2');INSERT INTO `t_scores`(`score_id`, `course_name`, `score`, `student_id`) VALUES ('5 ', ' Math','100',' 2');INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('6 ', ' English','98',' 2');

2. Requirements: Delete student information in batches according to student number

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 #Allow other users to run BEGIN DECLARE e_code INT DEFAULT 0;#Initialize error code to 0 DECLARE result VARCHAR(256) CHARACTER set utf8;#Initialize return result, resolve Chinese garbled code problem DECLARE arrLength INT DEFAULT 0;/* defines array length */ DECLARE arrString VARCHAR(1000);/* defines initial array characters */ DECLARE sStr VARCHAR(1000);/* defines initial characters */ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#continues execution after encountering errors;(use this when execution results need to be returned) START TRANSACTION;#START TRANSACTION SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/* GET ARRAY LENGTH */ SET arrString = arrayStr; DROP TEMPORARY TABLE IF EXISTS list_tmp; create temporary table list_tmp(id VARCHAR(32));/* DEFINE TEMPORARY TABLE */ WHILE arrLength > 0 DO set sStr = substr(arrString,1,instr(arrString,sSplit)-1); --Get the string before the delimiter set arrString = substr(arrString,length(sStr)+length(sSplit)+1); --Get the string after the delimiter 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 = 'Please enter the correct parameters'; 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 = 'This student does not exist! '; END IF; IF e_code=1 THEN ROLLBACK; #Roll back ELSE COMMIT; SET result = 'This student has been deleted successfully'; END IF; SELECT result; DROP TEMPORARY TABLE IF EXISTS list_tmp;END $$DELIMITER ;

Description: When creating a stored procedure, two parameters are passed in, the first representing the array string form to be passed in, and the second parameter is what to divide the string by.

declare initialization variables

DECLARE arrLength INT DEFAULT 0;/* defines array length */DECLARE arrString VARCHAR(1000);/* defines initial array characters */DECLARE sStr VARCHAR(1000);/* defines initial characters */

Gets the length of the incoming parameter array

SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/* Get array length */SET arrString = arrayStr;/* Assign */

create a temporary table

DROP TEMPORARY TABLE IF EXISTS list_tmp;create temporary table list_tmp(id VARCHAR(32));/* define temporary table */

Intercept the array string and store it in the temporary table in turn for later business use

WHILE arrLength > 0 DO set sStr = substr(arrString,1,instr(arrString,sSplit)-1); --Get string before delimiter set arrString = substr(arrString,length(sStr)+length(sSplit)+1); --Get the string after the delimiter set arrLength = arrLength -1; set @str = trim(sStr); insert into list_tmp(id) values(@str);END WHILE; About how to store the array in mysql The answer to the problem is shared here, I hope the above content can be of some help to everyone, if you still have a lot of doubts, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.