Example code and method of storing arrays in mysql

Example code and method of storing arrays in mysql

In many cases, arrays are often used when writing stored procedures, but there is no method to directly pass arrays into stored procedures in MySQL. In this case, we can only fall back or change the way to pass the parameters in the form of strings, and then convert the strings into arrays in the procedure body? But I'm sorry to tell you that MySQL does not directly provide a function to convert a string to an array. Do you feel like hitting someone now? But don't panic. If this road is blocked, we can take another one. There is always a solution. We can truncate the incoming string into multiple characters and pass them into a temporary table, and then use a cursor or directly join the table to filter the data. This will achieve the desired effect later.

Let's take an example to put this into practice:

1. Create a database for the 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', 'First Grade');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('2', 'Second Grade');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('3', 'Grade Three');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('4', 'Fourth Grade');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('5', 'Fifth Grade');

INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('6', 'Grade 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('Xiaohong',0,1,'1');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('Xiaoqing',0,2,'2');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('Xiao Ming',1,3,'3');

INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES('Xiao Lan',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', 'Chinese', '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', 'English', '95', '1');

INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('4', 'Chinese', '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', 'English', '98', '2');

2. Requirement: Batch delete student information according to student ID

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 the error code to 0

  DECLARE result VARCHAR(256) CHARACTER set utf8; #Initialize the returned result to solve the Chinese garbled problem DECLARE arrLength INT DEFAULT 0; /*Define the array length*/

  DECLARE arrString VARCHAR(1000);/*define the initial array character*/

  DECLARE sStr VARCHAR(1000);/*define the initial character*/

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;#Continue execution after encountering an error; (use this when you need to return the execution result)

 

 

  START TRANSACTION;#Start 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 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 = '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 ;

Note: When creating a stored procedure, two parameters are passed in. The first parameter represents the array string format to be passed in, and the second parameter is the method by which the string is split.

Declare and initialize variables

DECLARE arrLength INT DEFAULT 0;/*define array length*/

DECLARE arrString VARCHAR(1000);/*define the initial array character*/

DECLARE sStr VARCHAR(1000);/*define the initial character*/

Get the length of the incoming parameter array

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

SET arrString = arrayStr;/*assignment*/

Creating 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 a temporary table for subsequent business use

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;

Note: Be sure to delete the temporary table at the end of the stored procedure

There is no need to use stored procedures for businesses that are not very complex. This article does not guide everyone to use stored procedures, but just lets everyone know that there is such a thing!

You may also be interested in:
  • Mysql splits string into array through stored procedure
  • How to use PHP to reorganize MySQL duplicate ID two-dimensional array into a three-dimensional array
  • An example of how PHP converts MySQL query results into an array and concatenates them with where
  • How to query MySQL database in PHP and save the results to an array
  • In-depth understanding of using mysql_fetch_row() to return query results as an array
  • In PHP, processing the array returned by mysql_fetch_assoc does not require foreach--echo

<<:  Vue image cropping component example code

>>:  Solve the problem of PhPStudy MySQL startup failure under Windows system

Recommend

Detailed explanation of javascript knowledge points

Table of contents 1. Basic Introduction to JavaSc...

Solution to mysql prompt "got timeout reading communication packets"

Error message: user: 'root' host: `localh...

jquery+springboot realizes file upload function

This article example shares the specific code of ...

Detailed explanation of the usage of MySQL data type DECIMAL

MySQL DECIMAL data type is used to store exact nu...

JavaScript article will show you how to play with web forms

1. Introduction Earlier we introduced the rapid d...

JavaScript BOM Explained

Table of contents 1. BOM Introduction 1. JavaScri...

Solution to the problem of not being able to access the home page when adding a tomcat container to Docker

question The tomcat container was successfully ad...

Linux kernel device driver Linux kernel basic notes summary

1. Linux kernel driver module mechanism Static lo...

CSS3 filter (filter) to achieve the sample code of gray or black mode of web page

front end css3,filter can not only achieve the gr...

Beautiful checkbox style (multiple selection box) perfectly compatible with IE8/9/10, FF, etc.

It's embarrassing to say that I had to search ...

Detailed explanation of the differences between similar tags and attributes in HTML

【1】<i></i> and <em></em> ...

A brief discussion on Nginx10m+ high concurrency kernel optimization

What is high concurrency? The default Linux kerne...

IE8 provides a good experience: Activities

Today I had a sneak peek at IE8 beta 1 (hereafter...

An article to deal with Mysql date and time functions

Table of contents Preface 1. Get the current time...

How to understand Vue front-end and back-end data interaction and display

Table of contents 1. Technical Overview 2. Techni...