MySQL基礎知識08函數

標籤：values   contain   mys   character   hostname   提交   safe   fine   enable   

本文主要內容如下：

1. 函數的文法結構

2. 函數的例子

2.1. 最簡單的函數

2.2. 返回查詢結果的例子

3. 函數與預存程序的區別

3.1. 參數和傳回值的區別

3.2. 函數不允許提交事務

3.3. 函數的特徵限制

 

 

1.  函數的文法結構

函數過程的文法結構如下：

CREATE

[DEFINER = { user | CURRENT_USER }]

FUNCTION sp_name ([func_parameter[,...]])

RETURNS type

[characteristic ...] routine_body

 

各個部分的介紹如下：

DEFINER：函數的建立者所屬使用者名稱。

 

函數的參數：

proc_parameter:

 param_name type

函數的參數只能是IN類型的參數。

 

參數類型：

type:

Any valid MySQL data type

 

傳回值：

RETURNS type

 

特徵：

characteristic:

COMMENT ‘string‘

| LANGUAGE SQL

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

 

函數體：

routine_body:

Valid SQL routine statement

 

 

其中，

(1)COMMENT定義注釋；

  

(2)LANGUAGE SQL

 

(3)DETERMINISTIC

DETERMINISTIC：確定的。

NOT DETERMINISTIC：不確定的。

 

(4) CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA:

(a)CONTAINS SQL:函數包含SQL語句；

(b)NO SQL： 函數中不包含SQL語句。

(c)READS SQL DATA：函數中包含讀取資料的SQL語句。

(d)MOFIFIES SQL DATA：函數中包含修改資料的SQL語句。

 

(5)SQL SECURITY { DEFINER | INVOKER }：

假定函數定義者為a,調用者為b，則執行函數時:

DEFINER：先檢查b是否有執行函數許可權，再檢查a是否有訪問相關資料表的許可權。

INVOKER：先檢查b是否有執行函數許可權，再檢查b是否有訪問相關資料表的許可權。

 

2.  函數的例子

 

2.1.  最簡單的函數

 

一個最簡單的函數的例子如下：

drop function if exists sf_p1;

delimiter $$

create function  sf_p1()

returns integer

deterministic

return 100;

$$

delimiter ;

 

使用DETERMINISTIC特徵限制，是因為MySQL啟用了Binary Log；在啟用了Binary Log時，如果不使用DETERMINISTRIC等特徵限制，則該函數無法建立成功。

 

調用該函數：

mysql> select sf_p1();

+---------+

| sf_p1() |

+---------+

|     100 |

+---------+

1 row in set (0.00 sec)

 

 

2.2.  返回查詢結果的例子

 

drop function if exists sf_p1;

delimiter $$

create function  sf_p1()

returns integer

deterministic

begin

declare x integer;

select count(*) into x from t1;

return x;

end;

$$

delimiter ;

 

調用函數：

mysql> select sf_p1();

+---------+

| sf_p1() |

+---------+

|       1 |

+---------+

1 row in set (0.00 sec)

 

 

3.  函數與預存程序的區別

 

3.1.  參數和傳回值的區別

 

(1)函數的參數在文法上不允許使用IN和OUT以及INOUT修飾符；在語義上只支援IN型別參數。預存程序則在文法和語義上均支援IN,OUT和INOUT三種修飾符。

 

(2)函數必須有傳回值定義，即RETURNS語句；函數還必須有至少一條RETURN語句來返回一個值;函數還必須有且僅有一個傳回值。預存程序沒有傳回值的概念；預存程序需要通過OUT或者INOUT類型的參數來返回資料；預存程序可以定義N個OUT或INOUT類型的參數，N>=0。

 

3.2.  函數不允許提交事務

函數不允許顯式的提交事務(start transaction和commit等語句)，函數也不允許隱式提交事務(truncate table等語句)；而預存程序則沒有這個限制。

以下的函數中存在隱式提交事務的truncate table語句，因此這個函數將無法建立成功。

 

drop function if exists sf_p1;

delimiter $$

create function  sf_p1()

returns varchar(100)

NOT DETERMINISTIC

begin

truncate table t1;

insert into t1 values ( @@hostname);

insert into t1 values ( uuid());

insert into t1 values ( cast(rand() as char));

return ‘1‘;

end;

$$

delimiter ;

 

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

 

解決辦法：

移除truncate語句。

 

 

 

3.3.  函數的特徵限制

在啟用了Binary Log的情況下，函數必須使用某些特徵限制，否則該函數將無法建立成功。而預存程序則不存在這樣的限制。

在啟用了Binary Log的情況下，有可能會存在主從複製，這時必須保證函數的傳回值在master和slave中是完全相同的，否則會導致主從資料不一致的問題。

因此，在啟用了Binary Log的情況下，MySQL要求函數必須使用一些特徵限制，否則不允許建立該函數。

這些限制可以是以下三種之一：

DETERMINISTIC：確定的值。

NO SQL：函數不執行任何SQL。

READS SQL DATA：函數僅僅讀取一些資料。

 

如果目前使用者有SUPER許可權，則沒有上述限制。

 

如果全域變數log_bin_trust_function_creators設定為ON，則也沒有上述限制。這個全域變數預設為OFF。

mysql> show variables like ‘%log_bin_trust%‘;

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF   |

+---------------------------------+-------+

1 row in set (0.02 sec)

 

 

drop function if exists sf_p1;

delimiter $$

create function  sf_p1()

returns varchar(100)

begin

declare x varchar(100);

set x = ‘001‘;

return x;

end;

$$

delimiter ;

 

 

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

 

 

解決辦法：

增加DETERMINSTIC或NO SQL等特徵。

drop function if exists sf_p1;

delimiter $$

create function  sf_p1()

returns varchar(100)

deterministic

begin

declare x varchar(100);

set x = ‘001‘;

return x;

end;

$$

delimiter ;

 

 

或者：

drop function if exists sf_p1;

delimiter $$

create function  sf_p1()

returns varchar(100)

no sql

begin

declare x varchar(100);

set x = cast( rand() as char);

return x;

end;

$$

delimiter ;

 

MySQL基礎知識08函數