編寫MySQL儲存儲存函數

標籤：

這篇部落格總結了編寫MySQL儲存函數（function）的過程、調試思路、總結及參考資料。

0、準備

建立測試資料庫

CREATE DATABASE IF NOT EXISTS `funcdemo`;

1、編寫

首先寫好function架構，定義好參數和傳回值類型：

USE funcdemo;

DELIMITER ;DELIMITER $$CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)BEGIN    RETURN CONCAT(‘Hello, ‘,s,‘!‘);END$$

命令列登入MySQL

mysql -uroot -proot;

在MySQL命令列匯入指令碼funcdemo.sql

source C:/sql/funcdemo.sql

注意：這裡可能會報告指令碼中的錯誤，修改後再重新匯入。

匯入成功結果如下：

mysql> source C:/sql/funcdemo.sql;Query OK, 1 row affected, 1 warning (0.00 sec)Database changedQuery OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)

查看已建立的function

mysql> use funcdemo;Database changedmysql> show function status;

可以看到已建立的函數

| funcdemo                 | hello                  | FUNCTION | [email protected] |

調用看返回結果

mysql> select hello(‘functest‘);+-------------------+| hello(‘functest‘) |+-------------------+| Hello, functest!  |+-------------------+1 row in set (0.00 sec)

2、編寫商務邏輯

編寫商務邏輯，比如加入字串長度判斷

BEGIN    IF LENGTH(s) > 10 THEN      RETURN CONCAT(‘input string too long!‘);    ELSE      RETURN CONCAT(‘Hello‘, s, ‘!‘);END$$

重新用source命令匯入，再次執行：

mysql> source C:/sql/funcdemo.sql;Query OK, 1 row affected, 1 warning (0.00 sec)Database changedQuery OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> select hello(‘functestverylonglong‘);+-------------------------------+| hello(‘functestverylonglong‘) |+-------------------------------+| input string too long!        |+-------------------------------+1 row in set (0.00 sec)

3、調試

儲存函數不能像儲存函數那樣用 select varname; 來查看參數，但是可以通過暫存資料表的方式查看。

建立暫存資料表，插入要查看的參數：

CREATE TEMPORARY TABLE IF NOT EXISTS `tmp` (  value CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);DELETE FROM tmp;INSERT INTO tmp (value) VALUES(s);

重新匯入後，執行函數再查看暫存資料表：

mysql> select hello(‘functest‘);+-------------------+| hello(‘functest‘) |+-------------------+| Hellofunctest!    |+-------------------+1 row in set (0.01 sec)mysql> select * from tmp;+----------+| value    |+----------+| functest |+----------+1 row in set (0.00 sec)

4、總結

第一次編寫儲存函數遇到了很多問題：

- 用navicat用戶端經常遇到因為文法不對丟失內容問題，後來採用命令列解決。
- source命令匯入指令碼，遇到路徑問題，報告指令碼不存在：windows環境下，需要用 / 替換預設的 \ 作為路徑分隔字元。
- 儲存函數前後要定義分隔字元 DELIMITER；預設分隔符號是 ; 在儲存函數前要改為 $$，匯入結束要改為 ;

DELIMITER ;DELIMITER $$BEGIN- - 儲存函數定義END$$DELIMITER ;

- 如何對定義的參數進行類型轉換、尋找MySQL內建的字串、日期等函數。
- 終端下查看的中文亂碼：set names gb2312;
- 用show命令查看已定義的資料庫、表、函數：

show databases;show tables;show function status;

- 用show命令查看建立，在show命令後加入CREATE參數：

mysql> show create database funcdemo;+----------+-------------------------------------------------------------------+| Database | Create Database                                                   |+----------+-------------------------------------------------------------------+| funcdemo | CREATE DATABASE `funcdemo` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+-------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table t;+-------+-------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                            |+-------+-------------------------------------------------------------------------------------------------------------------------+| t     | CREATE TABLE `t` (  `c` char(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create function hello;

- 用mysqldump帶 --opt -R 參數匯出資料庫和儲存函數

>mysqldump -proot -uroot --opt -R funcdemo > c:/sql/backup.sql

多看，多寫，多調試，多總結。

本文的樣本指令碼可以在這裡下載。

下一篇會介紹如何使用MySQL官方文檔查看SQL文法、函數說明樣本進行介紹。

編寫MySQL儲存儲存函數