MySQL自訂函數-mysql教程-PHP中文網

首頁

資料庫

mysql教程

MySQL自訂函數

黄舟

Feb 27, 2017 pm 01:32 PM

自訂函數：

使用者自訂函數（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(),&#39;%Y年%m月%d日 %H点:%i分:%s秒&#39;);
+--------------------------------------------------+
| DATE_FORMAT(NOW(),&#39;%Y年%m月%d日 %H点:%i分:%s秒&#39;)             |
+--------------------------------------------------+
| 2016年09月08日 21点:19分:54秒                                |
+--------------------------------------------------+
1 row in set (0.01 sec)

登入後複製

對上述過程經行封裝：

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),&#39;%Y年%m月%d日 %H点:%i分:%s秒&#39;);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 &#39;tdb_test(first_name,last_name) 
    VALUES(first_name,last_name)&#39; 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 &#39;tdb_test(first_name,last_name) VALUES(first_name,last_name);
LAST_INSERT_ID()&#39; 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(),&#39;%Y年%m月%d日 %H点:%i分:%s秒&#39;);
+--------------------------------------------------+
| DATE_FORMAT(NOW(),&#39;%Y年%m月%d日 %H点:%i分:%s秒&#39;)             |
+--------------------------------------------------+
| 2016年09月08日 21点:19分:54秒                                |
+--------------------------------------------------+
1 row in set (0.01 sec)

登入後複製

對上述過程經行封裝：

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),&#39;%Y年%m月%d日 %H点:%i分:%s秒&#39;);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 &#39;tdb_test(first_name,last_name) VALUES(first_name,last_name)&#39; 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 &#39;tdb_test(first_name,last_name) VALUES(first_name,last_name);
LAST_INSERT_ID()&#39; 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）！

本網站聲明

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

熱AI工具

熱工具

熱門話題

gmail信箱登陸入口在哪裡

7545

CakePHP 教程

1381

steam的賬戶名稱是什麼格式

win11激活密鑰永久

NYT連接提示和答案

Related knowledge

mysql：簡單的概念，用於輕鬆學習 Apr 10, 2025 am 09:29 AM

MySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。

phpmyadmin怎麼打開 Apr 10, 2025 pm 10:51 PM

可以通過以下步驟打開 phpMyAdmin：1. 登錄網站控制面板；2. 找到並點擊 phpMyAdmin 圖標；3. 輸入 MySQL 憑據；4. 點擊 "登錄"。

MySQL：世界上最受歡迎的數據庫的簡介 Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

為什麼要使用mysql？利益和優勢 Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

redis怎麼使用單線程 Apr 10, 2025 pm 07:12 PM

Redis 使用單線程架構，以提供高性能、簡單性和一致性。它利用 I/O 多路復用、事件循環、非阻塞 I/O 和共享內存來提高並發性，但同時存在並發性受限、單點故障和不適合寫密集型工作負載的局限性。

MySQL和SQL：開發人員的基本技能 Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

MySQL的位置：數據庫和編程 Apr 13, 2025 am 12:18 AM

MySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen

SQL刪除行後如何恢復數據 Apr 09, 2025 pm 12:21 PM

直接從數據庫中恢復被刪除的行通常是不可能的，除非有備份或事務回滾機制。關鍵點：事務回滾：在事務未提交前執行ROLLBACK可恢復數據。備份：定期備份數據庫可用於快速恢復數據。數據庫快照：可創建數據庫只讀副本，在數據誤刪後恢復數據。慎用DELETE語句：仔細檢查條件，避免誤刪數據。使用WHERE子句：明確指定要刪除的數據。使用測試環境：在執行DELETE操作前進行測試。

See all articles

MySQL自訂函數

熱AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

AI Hentai Generator

熱門文章

熱工具

記事本++7.3.1

SublimeText3漢化版

禪工作室 13.0.1

Dreamweaver CS6

SublimeText3 Mac版

熱門話題