Simple usage examples of MySQL custom functions

This article uses examples to illustrate the usage of MySQL custom functions. Share with you for your reference, the details are as follows:

Let's start with a simple one. Create a function to convert a datetime in the format of '2009-06-23 00:00:00' to a datetime in the format of '2009 June 23, 00:00':

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`getdate`$$
CREATE FUNCTION `sp_test`.`getdate`(gdate datetime) RETURNS varchar(255)
BEGIN
DECLARE x VARCHAR(255) DEFAULT '';
SET x = date_format(gdate,'%Y year %m month %d day %h hour %i minute %s second');
RETURN x;
END $$
DELIMITER ;

Analysis:

The first sentence defines an end identifier. Because MySQL uses a semicolon as the end identifier of SQL statements by default, and a semicolon is used inside the function body, it will conflict with the default SQL end identifier. Therefore, you need to define another symbol as the SQL end identifier first.

The second sentence is if the function already exists, delete it, sp_test is the name of the database, the function is associated with the database, getdate is the name of the function;

The third sentence creates a function. The parentheses contain the name and type of the parameter. RETURNS defines the type of the function's return value.

The function body must be placed between BEGIN END;

DECLARE is used to define variables in the function body. Here, a variable x is defined, which is empty by default. Then SET is used to assign a value to the variable x.

RETURN is the return value. Here, the variable x is returned. The type of x must be consistent with the return type defined in the third sentence.

Call:

SELECT getdate('2009-06-23 00:00:00');

Returns '2009-06-23 00:00:00'

Branch structure

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`cutString` $$
CREATE FUNCTION `sp_test`.`cutString`(s VARCHAR(255),n INT) RETURNS varchar(255)
BEGIN
IF(ISNULL(s)) THEN RETURN '';
ELSEIF CHAR_LENGTH(s)<n THEN RETURN s;
ELSEIF CHAR_LENGTH(S)=n THEN RETURN 'equal';
ELSE RETURN CONCAT(LEFT(s,n),'...');
END IF;
END $$
DELIMITER ;

Analysis:

This is a function for extracting a string. It has two parameters, one is a string s and the other is a number n. It keeps the first n bits of string s. If the number of bits in the string is less than n, it returns string s. If it is greater than n, it returns the first n bits followed by...

Call:

SELECT cutString('abcdefghijklmnopqrstuvwxyz',5);

Returns 'abced...'

Loop Structure

DELIMITER $$
DROP FUNCTION IF EXISTS `sp_test`.`morestar`$$
CREATE FUNCTION `sp_test`.`morestar`(n INT) RETURNS text
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE s TEXT DEFAULT '';
myloop:LOOP
SET i=i+1;
SET s = CONCAT(s,'*');
IF i > n THEN LEAVE myloop;
END IF;
END LOOP myloop;
RETURN s;
END $$
DELIMITER ;

Analysis: Generate n *.

Call:

SELECT morestar(5);

return '*****'

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.