Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > MySQL custom function

MySQL custom function

黄舟
Release： 2017-02-27 13:32:36
Original
3039 people have browsed it


Custom function:

User-defined function (UDF) is a way to extend MySQL. Its usage is the same as the built-in The functions are the same.

There are two necessary conditions for custom functions:
1. Parameters
2. Return value

The function can return any type of value and can also receive these types. parameters.

There is no necessary internal connection between function parameters and return value.

Example 1:
Create a parameterless function that returns the current time, year, month, day, hours, minutes and seconds.
For example: 

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-09-08 21:17:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
+--------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')             |
+--------------------------------------------------+
| 2016年09月08日 21点:19分:54秒                                |
+--------------------------------------------------+
1 row in set (0.01 sec)
Copy after login
Copy after login

Encapsulate the above process: 

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');Query OK, 0 rows affected (0.08 sec)

mysql> SELECT f1();
+-------------------------------+
| f1()                          |
+-------------------------------+
| 2016年09月08日 21点:21分:25秒             |
+-------------------------------+
1 row in set (0.02 sec)
Copy after login

Note: What follows returns is the return value type, and what follows return is the return value.

Example 2:
Create a function with parameters, such as finding the average of two numbers. 

mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;Query OK, 0 rows affected (0.00 sec)
Copy after login
Copy after login

Call: 

mysql> SELECT f2(4,6);
+---------+
| f2(4,6) |
+---------+
|    5.00 |
+---------+
1 row in set (0.02 sec)
Copy after login

If you want to delete the two custom functions created above: 

mysql> DROP FUNCTION f2;Query OK, 0 rows affected (0.11 sec)

mysql> DROP FUNCTION f1;Query OK, 0 rows affected (0.00 sec)
Copy after login
Copy after login

Create a custom function with a composite structure function body

For example: Create a function to insert data into the table and return the id of the inserted data. 

mysql> DESC tdb_test;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(20)      | NO   |     | NULL    |                |
| last_name  | varchar(20)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    ERROR 1064 (42000): You have an error in your SQL syntax; 
    check the manual that corresponds to your MySQL server version for the right syntax to use near 'tdb_test(first_name,last_name) 
    VALUES(first_name,last_name)' at line 4
Copy after login

At this time we found that after entering the sql statement to be executed, the following semicolon is the current default separator, causing the function to be unable to continue inputting. Therefore, the default delimiter needs to be modified. 

mysql> DELIMITER //
Copy after login
Copy after login

means ending with // 

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    -> LAST_INSERT_ID();
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 
use near 'tdb_test(first_name,last_name) VALUES(first_name,last_name);
LAST_INSERT_ID()' at line 4
Copy after login

also returns an error, because there are two statements to be executed, and begin and end need to be put in to form an aggregate. Let’s look at the correct ones below. practice. 

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    -> RETURN LAST_INSERT_ID();
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
Copy after login
Copy after login

Change the delimiter back

mysql> DELIMITER ;
Copy after login
Copy after login

The test results are as follows:

mysql> SELECT adduser("ttt","ddd");
+----------------------+
| adduser("ttt","ddd") |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.11 sec)

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
|  4 | 11         | 22        |
|  5 | ttt        | ddd       |
+----+------------+-----------+
5 rows in set (0.00 sec)
Copy after login
Copy after login

About the function body

  • The function body is legal The sql statement is composed of;

  • The function body can be a simple SELECT or INSERT statement;

  • If the function body is a composite structure, use BEGIN... END statement;

  • Compound structure can contain declarations, loops, control structures;

Delete function: 

DROP FUNCTION [IF EXISTS] function_name
Copy after login
Copy after login

Customized Function:

User-defined function (UDF) is a way to extend MySQL, and its usage is the same as the built-in function.

There are two necessary conditions for custom functions:
1. Parameters
2. Return value

The function can return any type of value and can also receive these types. parameters.

There is no necessary internal connection between function parameters and return value.

Example 1:
Create a parameterless function that returns the current time, year, month, day, hours, minutes and seconds.
For example: 

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-09-08 21:17:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
+--------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')             |
+--------------------------------------------------+
| 2016年09月08日 21点:19分:54秒                                |
+--------------------------------------------------+
1 row in set (0.01 sec)
Copy after login
Copy after login

Encapsulate the above process: 

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');Query OK, 0 rows affected (0.08 sec)

mysql> SELECT f1();+-------------------------------+| f1()                          |
+-------------------------------+| 2016年09月08日 21点:21分:25秒             |
+-------------------------------+1 row in set (0.02 sec)
Copy after login

Note: What follows returns is the return value type, and what follows return is the return value.

Example 2:
Create a function with parameters, such as finding the average of two numbers. 

mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;Query OK, 0 rows affected (0.00 sec)
Copy after login
Copy after login

Call: 

mysql> SELECT f2(4,6);+---------+| f2(4,6) |
+---------+|    5.00 |
+---------+1 row in set (0.02 sec)
Copy after login

If you want to delete the two custom functions created above: 

mysql> DROP FUNCTION f2;Query OK, 0 rows affected (0.11 sec)

mysql> DROP FUNCTION f1;Query OK, 0 rows affected (0.00 sec)
Copy after login
Copy after login

Create a custom function with a composite structure function body

For example: Create a function to insert data into the table and return the id of the inserted data. 

mysql> DESC tdb_test;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(20)      | NO   |     | NULL    |                |
| last_name  | varchar(20)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 
    use near 'tdb_test(first_name,last_name) VALUES(first_name,last_name)' at line 4
Copy after login

At this time we found that after entering the sql statement to be executed, the following semicolon is the current default separator, causing the function to be unable to continue inputting. Therefore, the default delimiter needs to be modified. 

mysql> DELIMITER //
Copy after login
Copy after login

means ending with // 

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    -> LAST_INSERT_ID();
    -> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'tdb_test(first_name,last_name) VALUES(first_name,last_name);
LAST_INSERT_ID()' at line 4
Copy after login

also returns an error, because there are two statements to be executed, and begin and end need to be put in to form an aggregate. Let’s look at the correct ones below. practice. 

mysql> CREATE FUNCTION adduser(first_name VARCHAR(20),last_name VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    -> INSERT tdb_test(first_name,last_name) VALUES(first_name,last_name);
    -> RETURN LAST_INSERT_ID();
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
Copy after login
Copy after login

Change the delimiter back

mysql> DELIMITER ;
Copy after login
Copy after login

The test results are as follows:

mysql> SELECT adduser("ttt","ddd");
+----------------------+
| adduser("ttt","ddd") |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.11 sec)

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
|  4 | 11         | 22        |
|  5 | ttt        | ddd       |
+----+------------+-----------+
5 rows in set (0.00 sec)
Copy after login
Copy after login

About the function body

  • The function body is legal The sql statement is composed of;

  • The function body can be a simple SELECT or INSERT statement;

  • If the function body is a composite structure, use BEGIN... END statement;

  • Compound structure can contain declarations, loops, and control structures;

Delete function: 

DROP FUNCTION [IF EXISTS] function_name
Copy after login
Copy after login

That’s it The content of MySQL custom functions, please pay attention to the PHP Chinese website (m.sbmmt.com) for more related content!


Related labels：
MySQL，自定义函数
source：php.cn
Previous article：MySQL commonly used date and time functions Next article：SQL view details
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
2
1429
Submit form without button using Javascript/Jquery I'm trying to submit a form without a button by calling a JavaScript function and executin...
From 2024-04-06 14:54:03
0
2
421
Can PHPUnit use assert equals to strictly compare two arrays containing objects? I have two arrays of objects. I want to test if these two arrays are equal, but using a st...
From 2024-04-05 13:35:37
0
1
1382
Releasing the X-axis after calling Chart.zoom(): a step-by-step guide I set the scroll strategy to setScrollStrategy(AxisScrollStrategies.progressive) and on th...
From 2024-04-04 23:40:56
0
1
1339
React/Node: Integration error encountered when subscribing using Trial_period_days I have a product defined in Stripe that I want to set up as a 7 day free trial and then ch...
From 2024-04-04 20:30:57
0
1
320
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template