Mysql splits string into array through stored procedure

To split a string into an array, you need to use three MySQL functions:

REVERSE(str) Returns the string str with the order of its characters reversed.

SUBSTRING_INDEX(str,delim,count)

Returns the substring of string str after the count-th occurrence of the delimiter delim. If count is positive, returns all characters from the last delimiter to the left (counting from the left). If count is negative, returns all characters from the last delimiter to the right (counting from the right).

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str.

The function of splitting a string into an array is achieved by combining three functions.

First, write two functions

1. Get the number of all strings separated by "a certain symbol". The function content is as follows

(Copy the SQL code and execute it in Navicat)

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
 -- Get the total number of given string.
 return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER ;

2. Extract the string by segmentation

(Copy the SQL code and execute it in Navicat)

DELIMITER $$
CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
 -- Get the separated number of given string.
 declare result varchar(255) default '';
 set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
 return result;
END$$
DELIMITER ;

Then write a stored procedure to combine these two functions, input the string to be split, and the separator, and output the array after splitting by a certain symbol

(Copy the SQL code and execute it in Navicat)

DELIMITER $$
CREATE PROCEDURE `sp_print_result`(
 IN f_string varchar(1000), IN f_delimiter varchar(5)
)
BEGIN
 -- Get the separated string.
 declare cnt int default 0;
 declare i int default 0;
 set cnt = func_get_split_string_total(f_string,f_delimiter);
 drop table if exists tmp_print;
 create temporary table tmp_print (num int not null);
 while i < cnt
 do
  set i = i + 1;
  insert into tmp_print(num) values ​​(func_get_split_string(f_string,f_delimiter,i));
 end while;
 select * from tmp_print;
END$$
DELIMITER ;

Then enter a string to test.

call sp_print_result(“434,123,12,234,123,123”,”,”);

The results are as follows

The user specified as a definer ('root'@'%') does not exist does not exist

The solution is as follows:

Execute: grant all privileges on *.* to root@"%" identified by ".";

Execute: flush privileges;

This solves the problem.

Finally, let's talk about stored procedures and MySQL functions

A stored procedure is a collection of user-defined SQL statements that involve tasks on specific tables or other objects. Users can call stored procedures, while functions are usually methods defined by the database that receive parameters and return a certain type of value and do not involve specific user tables.

There are several differences between stored procedures and functions:

1) Generally speaking, the functions implemented by stored procedures are more complex, while the functions implemented by functions are more targeted. Stored procedures are powerful and can perform a series of database operations including modifying tables; user-defined functions cannot be used to perform a set of operations that modify the global database state.

2) Stored procedures can return parameters, such as recordsets, while functions can only return values ​​or table objects. A function can return only one variable; a stored procedure can return multiple. The parameters of a stored procedure can be of three types: IN, OUT, and INOUT, while a function can only be of type IN. A stored procedure does not require a return type when it is declared, but a function must describe the return type when it is declared, and the function body must contain a valid RETURN statement.

3) Stored procedures can use non-deterministic functions, but non-deterministic functions cannot be built into the body of a user-defined function.

4) A stored procedure is generally executed as an independent part (executed by EXECUTE statement), while a function can be called as part of a query statement (SELECT call). Since a function can return a table object, it can be located after the FROM keyword in a query statement. Stored procedures are not available in SQL statements, but functions can be used.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links