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

Three mysql functions are required to split a string into an array:

REVERSE (str) returns the string str that reverses the order of characters.

SUBSTRING_INDEX (str,delim,count)

Returns the substring after the delimiter delim that appears at the count of the string str. If count is positive, returns all characters from the last delimiter to the left (the number from the left). If count is negative, returns the last delimiter to all characters on the right (from the right).

REPLACE (str,from_str,to_str)

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

Through the combination of three functions to achieve the function of dividing strings into arrays.

First, write two functions.

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

(copy the sql code and execute it in navicat)

DELIMITER $$CREATE DEFINER= `root` @ `% `FUNCTION `root` (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)); END$$DELIMITER

2. Extract the string by dividing

(copy the sql code and execute it in navicat)

DELIMITER $$CREATE DEFINER= `root` @ `% `FUNCTION `root` (f_string varchar (1000), f_delimiter varchar (5), f_order int) RETURNS varchar (1000) CHARSET utf8BEGIN-- Get the separated number of given string. Declare result varchar (declare result varchar) default''; set result = reverse (reverse (substring_index (fending stringmagem fending delimiterje fond order), faddish delimiterMagne 1); return result;END$$DELIMITER

Then write a stored procedure to combine the two functions, enter the string to be divided, and the delimiter, and output the array divided by the so-and-so 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; 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); end while; select * from tmp_print;END$$DELIMITER

Then enter a string to test.

Call sp_print_result ("434, 123," 12234123, ",")

The result is as follows

An error may be reported during execution: The user specified as a definer ('root'@'%') does not exist

The solution is as follows:

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

Execute: flush privileges

It solved 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 the task of a specific table or other object, and the user can call the stored procedure, while the function is usually a method defined by the database that receives parameters and returns a certain type of value and does not involve a specific user table.

There are several differences between stored procedures and functions:

1) generally speaking, the function of the stored procedure is a little more complex, and the function of the function is more targeted. Stored procedures, powerful, 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 state of the global database.

2) for stored procedures, you can return parameters, such as recordsets, while functions can only return values or table objects. Functions can return only one variable; stored procedures can return multiple variables. The parameters of a stored procedure can have three types of IN,OUT,INOUT, while a function can only have an in class ~ ~ the stored procedure does not need a return type when it is declared, but the return type needs to be described when the function is declared, and the function body must contain a valid RETURN statement.

3) stored procedures, which can use non-deterministic functions, and do not allow built-in uncertain functions in the body of user-defined functions.

4) the stored procedure is generally executed as a separate part (EXECUTE statement execution), and the function can be called as part of the query statement (SELECT call). Because the function can return a table object, it can be located after the FROM keyword in the query statement. Stored procedures are not available in SQL statements, and functions can be used.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.