Shulou(Shulou.com)06/01 Report--

The following mainly brings you the steps of mysql array storage. I hope these contents can bring you practical use, which is also the main purpose of this article on editing mysql array storage steps. All right, don't talk too much nonsense, let's just read the following.

In many cases, arrays are often used in writing stored procedures, but there is no way to pass parameters to stored procedures directly in mysql. In this case, we can only go back and ask for it, or instead pass in the parameters in the form of a string, and then convert the string into an array in the process body?

However, I am sorry to tell you that mysql does not directly provide the function to convert strings to arrays. Now do you feel like you want to hit someone? However, don't panic, this road is blocked, let's go the other way, there is always a solution. We can intercept the incoming string into multiple characters, pass it into a temporary table, and then use cursors or directly associated tables to filter the data. In this way, the expected results can be achieved.

Let's put it into practice with an example:

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 ('1year,' Grade 1') INSERT INTO `huafeng_ db`.`t _ class` (`class_ id`, `class_ name`) VALUES ('2years,' sophomore); INSERT INTO `huafeng_ db`.`t _ class` (`class_ id`, `class_ name`) VALUES ('3th,' third grade'); INSERT INTO `huafeng_ db`.class` (`class_ id`, `class_ name`) VALUES ('44th,' fourth grade'); INSERT INTO `huafeng_ db`.`t _ class` (`class_ id`, `class_ name`) VALUES ('51st,' fifth grade') INSERT INTO `huafeng_ db`.`t _ class` (`class_ id`, `class_ name`) VALUES ('6years,' sixth grade') 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 `tstudentsprincipibfk1` FOREIGN KEY (`class_ id`) REFERENCES `tclass` (`class_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `huafeng_ db`.`t _ students` (`student_ name`, `sex`, `seq_ no`, `class_ id`) VALUES (`Xiaohong, 0Power1); INSERT INTO `huafeng_ db`.`t _ students` (`student_ name`, `sex`, `seq_ no`, `class_ id`) VALUES ('Xiaoqing', 0Pride 2') VALUES; INSERT INTO `huafeng_ db`.`t _ students` (`student_ name`, `sex`, `seq_ no`, `class_ id`) VALUES ('Xiaoming', `sex`, `seq_ no`, `class_ id`) INSERT INTO `huafeng_ db`.`t _ students` (`student_ name`, `sex`, `seq_ no`, `class_ id`) VALUES (`Xiaolan, 0pr 4); INSERT INTO `huafeng_ db`.`t _ students` (`student_ name`, `sex`, `seq_ no`, `class_ id`) VALUES ('millet', 1pm 5m 5') VALUES (`student_ name`, `sex`, `seq_ no`, `class_ id`) VALUES (`Xiaobai, `sex`, `seq_ no`, `class_ id`) CREATE TABLE `huafeng_ db`.`t _ scores` (`score_ id` int (11) NOT NULL AUTO_INCREMENT, `course_ name` varchar (64) DEFAULT NULL, `score` double (3Power2) DEFAULT NULL, `student_ id` int (11) DEFAULT NULL, PRIMARY KEY (`score_ id`), KEY `student_ id` (`student_ id`), CONSTRAINT `tscoresscoresaccounibfk1` FOREIGN KEY (`student_ id`) REFERENCES `t_ students` (`student_ id`) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 INSERT INTO `tscores` (`VALUES id`, `course_ name`, `score`, `student_ id`) VALUES ('1century,' language', '90cycles,' 1'); INSERT INTO `tscores` (`score_ id`, `course_ name`, `score`, `student_ id`) VALUES ('2numbers,' mathematics, '97skills,' 1'); INSERT INTO `tscores` (`score_ id`, `course_ name`, `score`, `student_ id`) VALUES ('34th,' English', '9509,' 1') INSERT INTO `tscores` (`score_ id`, `course_ name`, `score`, `student_ id`) VALUES ('44th,' Chinese', '92birthday,' 2'); INSERT INTO `tscores` (`score_ id`, `course_ name`, `score`, `student_ id`) VALUES ('54th,' math', '100th,' 2'); INSERT INTO `tscores` (`score_ id`, `course_ name`, `score`, `student_ id`) VALUES ('618,' English','98','2')

2. Demand: delete student information in batches according to student number

DROP PROCEDURE IF EXISTS `packs delicatestudentInfobySeqNo`; DELIMITER $$CREATE PROCEDURE p_del_studentInfo_bySeqNo (IN arrayStr VARCHAR (1000), IN sSplit VARCHAR (10)) SQL SECURITY INVOKER # allows other users to run BEGIN DECLARE e_code INT DEFAULT 0 DECLARE arrLength INT DEFAULT # initialize the error code to 0 DECLARE result VARCHAR (25) CHARACTER set utf8;# to initialize the returned result, and solve the Chinese garbled problem DECLARE arrLength INT DEFAULT 0 random character * define array length * / DECLARE arrString VARCHAR (1000) / * define the initial array characters * / DECLARE sStr VARCHAR (1000); / * define the initial characters * / DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET eigencodewords 1: # continue execution when you encounter an error; (use this when you need to return the execution result) START TRANSACTION;# to start the transaction SET arrLength = LENGTH (arrayStr)-LENGTH (REPLACE (arrayStr,sSplit,'')); / * get the 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 set arrString = substr (arrString,length (sStr) + length (sSplit) + 1) before the delimiter;-- get the string set arrLength = arrLength-1 after the delimiter 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 ListingStemp 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 sforce listentmp 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 = 'the student does not exist!' ; END IF; IF e_code=1 THEN ROLLBACK; # rollback ELSE COMMIT; SET result = 'the student has been deleted successfully'; END IF; SELECT result; DROP TEMPORARY TABLE IF EXISTS list_tmp;END $$DELIMITER

Description: when you create a stored procedure, you pass in two parameters, the first representing the form of the array string to be passed in, and the second parameter is what to split the string.

Declare initialization variables

DECLARE arrLength INT DEFAULT * define the array length * / DECLARE arrString VARCHAR (1000); / * define the initial array character * / DECLARE sStr VARCHAR (1000); / * define the initial character * /

Get the length of the array of incoming parameters

SET arrLength = LENGTH (arrayStr)-LENGTH (REPLACE (arrayStr,sSplit,'')); / * get the array length * / SET arrString = arrayStr;/* assignment * /

Create a temporary table

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

Intercept array strings and store them in a temporary table in turn for subsequent business use

WHILE arrLength > 0 DO set sStr = substr (arrString,1,instr (arrString,sSplit)-1);-- get the string set arrString = substr (arrString,length (sStr) + length (sSplit) + 1) before the delimiter;-- get the string set arrLength = arrLength-1 after the delimiter; set @ str = trim (sStr); insert into list_tmp (id) values (@ str); END WHILE

Note: be sure to delete temporary tables at the end of the stored procedure

For the above steps on mysql array storage, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.