简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
首页 > 数据库 > mysql教程 > 正文

MySQL自定义函数

黄舟
发布： 2017-02-27 13:32:36
原创
3024 人浏览过


自定义函数：

用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途经，其用法与内置函数相同。

自定义函数有两个必要条件：
1. 参数
2. 返回值

函数可以返回任意类型的值，同样可以接收这些类型的参数。

函数的参数和返回值之间没有必然的内在的联系。

示例1：
创建一个无参数的函数，返回当前时间的，年月日 时分秒。
例如：

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)
登录后复制
登录后复制

对上述过程经行封装：

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)
登录后复制

注：returns后面的为返回值类型，return后面的为返回值。

示例2：
创建有参数的函数，例如求两个数的平均值。

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)
登录后复制
登录后复制

调用：

mysql> SELECT f2(4,6);
+---------+
| f2(4,6) |
+---------+
|    5.00 |
+---------+
1 row in set (0.02 sec)
登录后复制

如果要删除上面所创建的两个自定义函数：

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

mysql> DROP FUNCTION f1;Query OK, 0 rows affected (0.00 sec)
登录后复制
登录后复制

创建具有复合结构函数体的自定义函数

例如：创建一个函数向表中插入数据，并返回所插入数据的id。

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
登录后复制

这时我们发现，在输入要执行的sql语句后，后面的分号为当前默认的分隔符，导致函数无法再继续输入。因此需要修改默认的分隔符。

mysql> DELIMITER //
登录后复制
登录后复制

意思是用//结束

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
登录后复制

同样返回错误，因为，返回有两个语句要执行，需放入begin和end构成一个聚合体，下面来看正确的做法。

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)
登录后复制
登录后复制

再将分隔符改回来

mysql> DELIMITER ;
登录后复制
登录后复制

测试结果如下：

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)
登录后复制
登录后复制

关于函数体

  • 函数体由合法的sql语句构成；

  • 函数体可以是简单的SELECT或INSERT语句；

  • 函数体如果为复合结构则使用BEGIN…END语句；

  • 复合结构可以包含声明，循环，控制结构；

删除函数：

DROP FUNCTION [IF EXISTS] function_name
登录后复制
登录后复制

自定义函数：

用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途经，其用法与内置函数相同。

自定义函数有两个必要条件：
1. 参数
2. 返回值

函数可以返回任意类型的值，同样可以接收这些类型的参数。

函数的参数和返回值之间没有必然的内在的联系。

示例1：
创建一个无参数的函数，返回当前时间的，年月日 时分秒。
例如：

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)
登录后复制
登录后复制

对上述过程经行封装：

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)
登录后复制

注：returns后面的为返回值类型，return后面的为返回值。

示例2：
创建有参数的函数，例如求两个数的平均值。

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)
登录后复制
登录后复制

调用：

mysql> SELECT f2(4,6);+---------+| f2(4,6) |
+---------+|    5.00 |
+---------+1 row in set (0.02 sec)
登录后复制

如果要删除上面所创建的两个自定义函数：

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

mysql> DROP FUNCTION f1;Query OK, 0 rows affected (0.00 sec)
登录后复制
登录后复制

创建具有复合结构函数体的自定义函数

例如：创建一个函数向表中插入数据，并返回所插入数据的id。

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
登录后复制

这时我们发现，在输入要执行的sql语句后，后面的分号为当前默认的分隔符，导致函数无法再继续输入。因此需要修改默认的分隔符。

mysql> DELIMITER //
登录后复制
登录后复制

意思是用//结束

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
登录后复制

同样返回错误，因为，返回有两个语句要执行，需放入begin和end构成一个聚合体，下面来看正确的做法。

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)
登录后复制
登录后复制

再将分隔符改回来

mysql> DELIMITER ;
登录后复制
登录后复制

测试结果如下：

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)
登录后复制
登录后复制

关于函数体

  • 函数体由合法的sql语句构成；

  • 函数体可以是简单的SELECT或INSERT语句；

  • 函数体如果为复合结构则使用BEGIN…END语句；

  • 复合结构可以包含声明，循环，控制结构；

删除函数：

DROP FUNCTION [IF EXISTS] function_name
登录后复制
登录后复制

 以上就是MySQL自定义函数的内容，更多相关内容请关注PHP中文网（www.php.cn）！


相关标签：
MySQL，自定义函数
来源：php.cn
上一篇：MySQL常用的日期时间函数 下一篇：SQL视图 详细介绍
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
function_exists()无法判定自定义函数 function test()    {        return true;    }    if (function_exists('TEST')) {        ech...
来自于 2024-04-29 11:01:01
0
2
1429
使用 Javascript / Jquery 提交表单且不带按钮 我试图通过调用JavaScript函数并使用JQUERY/PHP执行表单来提交没有按钮的表单。我希望表单在后端静默执行，而无需重新加载页面。不幸的是，它不断返回JavaScript...
来自于 2024-04-06 14:54:03
0
2
421
PHPUnit能否使用assert equals严格比较包含对象的两个数组？ 我有两个对象数组。我想测试这两个数组是否相等，但要使用严格比较，例如'false'!==0。在phpunit中是否有可能实现这一点，而不需要自定义比较器或其他方法？
来自于 2024-04-05 13:35:37
0
1
1382
调用 Chart.zoom() 后释放 X 轴：分步指南 我将滚动策略设置为setScrollStrategy(AxisScrollStrategies.progressive)，在加载的页面上，图表滚动得非常快，因为我的数据传入得很快。...
来自于 2024-04-04 23:40:56
0
1
1339
React/Node：使用 Trial_period_days 进行订阅时遇到集成错误 我在Stripe中定义了一个产品，我希望将其设置为7天免费试用，然后向客户收费（除非他们事先取消）通过Firebase使用自定义Nodejs函数-我收到以下错误：Missingva...
来自于 2024-04-04 20:30:57
0
1
320
相关专题
更多>
热门推荐
热门教程
更多>
相关教程
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责声明 Sitemap
PHP中文网：公益在线PHP培训，帮助PHP学习者快速成长！