您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 自定義函數與存儲過程完整開發指南

編輯：MySQL 資料庫相關知識

一、建立自定義函數 (UDF)

場景

表 test 儲存圖片感知雜湊 (hashcode CHAR(16) HEX)。需查詢「與輸入雜湊對應位置不同字元數 < 5」的記錄。MySQL 內建函數難以實現，需自定義函數計算漢明距離。

表結構

CREATE TABLE `test` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pic` VARCHAR(50) NOT NULL,
  `hashcode` CHAR(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES
(1,'2012120910403250c3fa209bf48.jpg','bf8f83818080c0f1'),
(2,'2012120620430750c092db26557.JPG','ff9880f0f680ceff'),
(3,'2012120619582550c08861eb062.jpg','7f7f004f7f7f7c7f'),
(4,'2012112911072650b6d16e7f21f.jpg','7f7f004f7f7f007f');

建立函數步驟

進入 mysql 命令列。
修改分隔符：DELIMITER //
建立函數：

CREATE FUNCTION hashDiff(s1 CHAR(16), s2 CHAR(16))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE diff INT DEFAULT 0;
    DECLARE i  INT DEFAULT 1;
    WHILE i <= 16 DO
        IF SUBSTRING(s1, i, 1) <> SUBSTRING(s2, i, 1) THEN
            SET diff = diff + 1;
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN diff;
END//

恢復分隔符：DELIMITER ;
測試：

SELECT * FROM test WHERE hashDiff(hashcode, 'ff9880f0f680ceff') < 5;
-- 返回 id=2,3,4

關鍵點

DETERMINISTIC 聲明相同輸入必得相同輸出，允許查詢快取。
函數內不可執行修改資料的語句（INSERT/UPDATE/DELETE）。
若需在複寫環境使用，需設定 log_bin_trust_function_creators=1。

二、建立存儲過程

基本語法

DELIMITER //
CREATE PROCEDURE simpleproc(OUT param1 INT)
BEGIN
    SELECT COUNT(*) INTO param1 FROM t;
END//
DELIMITER ;

呼叫與取回

CALL simpleproc(@a);
SELECT @a;  -- 顯示結果

參數模式

模式	關鍵字	說明
輸入	IN	預設，呼叫端傳入，過程內可讀不可寫回
輸出	OUT	過程內賦值，呼叫端透過使用者變數接收
輸入輸出	INOUT	雙向，呼叫端傳入變數，過程可修改並回傳

進階範例：遊標處理

CREATE PROCEDURE process_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE oid INT;
    DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status='NEW';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO oid;
        IF done THEN LEAVE read_loop; END IF;
        -- 處理單筆訂單
        UPDATE orders SET status='PROCESSING' WHERE id=oid;
    END LOOP;
    CLOSE cur;
END//

三、管理指令速查

操作	指令
列出所有函數	SHOW FUNCTION STATUS WHERE Db='db_name';
列出所有過程	SHOW PROCEDURE STATUS WHERE Db='db_name';
檢視函數定義	SHOW CREATE FUNCTION func_name;
檢視過程定義	SHOW CREATE PROCEDURE proc_name;
刪除函數	DROP FUNCTION IF EXISTS func_name;
刪除過程	DROP PROCEDURE IF EXISTS proc_name;