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

MySQL Daemon failed to start error solution

MySQL Daemon failed to start error solution A few...

Linux kernel device driver Linux kernel module loading mechanism notes summary

#include <linux/moduleparam.h> 1. Module pa...

Solution for mobile browsers not supporting position: fix

The specific method is as follows: CSS Code Copy ...

VMware + Ubuntu18.04 Graphic Tutorial on Building Hadoop Cluster Environment

Table of contents Preface VMware clone virtual ma...

How to modify the previous command when an input error occurs in the MySQL command prompt

Table of contents Current Issues Solution process...

How to solve the problem of not getting form value after submitting html form input using disabled

After the form input box input is set to the disa...

Detailed explanation of Grid layout and Flex layout of display in CSS3

Gird layout has some similarities with Flex layou...

B2C website user experience detail design reference

Recently, when using Apple.com/Ebay.com/Amazon.co...

CSS to achieve the image hovering mouse folding effect

CSS to achieve the image hovering mouse folding e...

Explanation of using if judgment conditions in sum and count functions when using SQL statements to collect data

First, let me give you an example (if you don’t w...

MySQL 8.0.18 Installation Configuration Optimization Tutorial

Mysql installation, configuration, and optimizati...

Detailed explanation of JavaScript's built-in objects Math and strings

Table of contents Math Objects Common properties ...

Detailed examples of float usage in HTML/CSS

1. Basic usage examples of float 1. Let's fir...

Detailed explanation of MySQL table name case-insensitive configuration method

By default, MySQL in Linux distinguishes between ...

Summary of SQL deduplication methods

When using SQL to extract data, we often encounte...