MySQL中文參考手冊

譯者：晏子 (clyan@sohu.com）
GB 碼主頁：http://linuxdb.yeah.net

Big5 轉碼者：statue (statue@bbs.yzu.edu.tw）
詞彙轉換：彭武興 (wilson@mailbox.com.tw)
Big5 碼主頁： http://cnpa.yzu.edu.tw/~cfc/docs/mysqldoc_big5/manual_toc.html
Big5 碼分站： http://php.wilson.gs/mysqldoc/big5/manual_toc.html

第一章, 前一章, 下一章, 最後一章，目錄.

14 為MySQL增加新函數

有2種方法把新函數加到MySQL中：

• 你可以通過用戶定義函數(UDF)介面加入函數。用戶定義函數用CREATE FUNCTION和DROP FUNCTION語句動態地增加和刪除。見7.30 CREATE FUNCTION/DROP FUNCTION句法。
• 你可以加入函數作為一個原生的(內置的)MySQL函數。原生函數被編譯進mysqld伺服器並且在一個永久的基礎上可得到。

每種方法都有優點和缺點：

• 如果你編寫一個用戶定義函數，你必須安裝伺服器外還得自己安裝對像文件。如果你編譯函數進伺服器中，你不需要那樣做。
• 你能把UDF加到MySQL二進制代碼發行中。原生函數要求你修改原始碼版本。
• 如果你升級你的MySQL分發，你能繼續使用你的以前安裝的UDF。對於原生函數，你必須在每次升級時重複你的修改。

無論你使用哪種方法增加新函數，他們可以像原生函數例如ABS()或SOUNDEX()那樣使用。

14.1 增加一個新的用戶定義函數

對於UDF的工作機制，函數必須用C或C++編寫並且你的作業系統必須支援動態裝載。MySQL原始碼版本包括一個文件“sql/udf_example.cc”，它定義了5個新函數。請教這個文件看UDF調用約定怎樣工作。

對每一個你想在SQL語句中使用的函數，你應該定義對應的C(或 C++)函數。在下面的討論中，“xxx”用於一個函數名的例子。為了區別SQL和C/C++用法，XXX()(大寫)表明SQL函數調用，而xxx()((小寫)表明C/C++函數調用。

你編寫實現XXX()的介面的C/C++函數是：

xxx()（必需的）

主函數。這是計算函數結果的地方。SQL 類型於你的C/C++函數返回類型的對應關系如下：

SQL 類型	C/C++ 類型
STRING	char *
INTEGER	long long
REAL	double

xxx_init()（可選）

為xxx()的初始化函數，它可用於：

• 檢查傳到XXX()的參數數量。
• 檢查參數是一種所需的類型，或，另外地，當主函數被調用時，告訴MySQL，為了強制參數到你想要的類型。
• 分配任何由主函數所需的內存。
• 指定結果的最大長度。
• 指定(對REAL函數)小數位的最大數目。
• 指定結果是否能是NULL。

xxx_deinit()（可選）

為xxx()的結束函數，它應該釋放初始化函數分配了的任何內存。

當一條SQL語句調用XXX()時，MySQL調用初始化函數xxx_init()，讓它執行任何所需的設置，例如參數檢查或內存分配。如果xxx_init()返回一個錯誤，SQL語句用一條錯誤消息並被放棄而主函數和結束函數不被調用，否則，為每行調用主函數xxx()一次。在所有行被處理完後，結束函數xxx_deinit()被調用，因此它能執行任何必要的清除。

所有函必須是執行緒安全的(不只是主函數，還有初始化和結束函數)。這意味著，你不允許分配任何改變的全局或靜態變數！如果你需要內存，你應該在xxx_init()種分配它並且在xxx_deinit()中釋放它。

14.1.1 UDF的調用順序

主函數應該如下定義。注意返回類型和參數不同，取決於你是否在CREATE FUNCTION語句中聲明SQL函數XXX()返回STRING、INTEGER或REAL：

對STRING函數：

char *xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *result, unsigned long *length,
              char *is_null, char *error);

對INTEGER函數：

long long xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

對於REAL函數：

double xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

初始化和結束函數像這樣被聲明：

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

void xxx_deinit(UDF_INIT *initid);

initid參數被傳給所有3個函數，它指向一個UDF_INIT結構，被用來在函數之間傳遞資訊。UDF_INIT結構成員列在下面。初始化函數應該填寫它想要改變的任何成員。（對一個成員使用內定值，不改變它。）

my_bool maybe_null

如果xxx()能返回NULL，xxx_init()應該設置maybe_null為1。如果參數的任何一個被聲明maybe_null，內定值是1。

unsigned int decimals

小數位數目。內定值是在被傳給主函數的參數中小數位的最大數目。（例如，如果函數傳遞1.34、1.345和1.3，內定值將是3，因為1.345有3個小數位。

unsigned int max_length

字符串結果的最大長度。內定值不同，取決於函數的結果類型。對字符串函數，內定是最長的參數的長度。對整數函數，內定是21位。對實數函數，內定是13加上由initid->decimals指出的小數位數。（對數字函數，長度包括任何符號位或小數點字符。）

char *ptr

函數可為它自己的目的使用的一個指針。例如，函數能使用initid->ptr在函數之間傳遞分配的內存。在xxx_init()中，分配內存並將它賦給這個指針：

initid->ptr = allocated_memory;

在xxx()和xxx_deinit()中，參照initid->ptr來使用或釋放內存。

14.1.2 參數處理

args參數指向一個UDF_ARGS成員，其結構列在下面：

unsigned int arg_count

參數個數。如果你想要函數用一個特定數量的參數被調用，在初始化函數中檢查這個值。例如：

if (args->arg_count != 2)
{
    strcpy(message,"XXX() requires two arguments");
    return 1;
}

enum Item_result *arg_type

為每個參數的類型。可能的類型值是STRING_RESULT、INT_RESULT和REAL_RESULT。為了確保參數是一種給定的類型，而如果他們不是，返回一個錯誤，在初始化函數中檢查arg_type數組。例如：

if (args->arg_type[0] != STRING_RESULT
      && args->arg_type[1] != INT_RESULT)
{
    strcpy(message,"XXX() requires a string and an integer");
    return 1;
}

作為另一種要求你的函數的參數類型是特定類型的選擇，你可以使用初始化函數設置arg_type成員是你想要的類型。這導致MySQL為每個xxx()調用強制參數為那些類型，例如，為了指定頭 2個參數到字符串和整數的強制，在xxx_init()中做這些：

args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;

char **args

args->args將關於你的函數用它調用的參數的一般特性的資訊傳遞到初始化函數。對一個常數參數i，args->args[i]指向參數值。（見下面關於如何正確存取值的指令) 對一個非常數的參數，args->args[i]是0。一個常數參數只是使用常數的一個表達式，例如3或4*7-2或SI(3.14)。一個非常數參數是引用可能每行不同的值的一個表達式，例如列名字或用非常數參數調用的函數。對主函數的每次調用，args->args包含對當前正在處理的行所傳遞的實際參數。函數可以如下地引用一個參數i：

• 一個STRING_RESULT類型的參數由一個字符串指針加一個長度給出，允許處理任意的長度的二進制的數據或數據。字符串內容可由args->args[i]得到並且字符串長度是args->lengths[i]。你不應該假設字符串是以空(null)結束的。
• 對於一個INT_RESULT類型的參數，你必須強制轉換args->args[i]為一個long long值：

  long long int_val;
  int_val = *((long long*) args->args[i]);
  

• 對一個REAL_RESULT類型的參數，你必須強制轉換args->args[i]為一個double值：

  double    real_val;
  real_val = *((double*) args->args[i]);
  
  

unsigned long *lengths

對初始化函數，lengths數組指出每個參數的最大字符串長度。對於主函數調用，lengths包含為當前正在被處理的行傳遞的任何字符串參數的實際長度。對INT_RESULT或REAL_RESULT類型的參數，lengths仍然包含參數的最大長度(就像對初始化函數)。

14.1.3 返回值和出錯處理

如果沒有出現錯誤，初始化函數應該返回0，否則返回1。如果發生一個錯誤，xxx_init()應該在message參數中儲存一條空字符結束的錯誤消息，消息將被返回給客戶。消息緩衝區是MYSQL_ERRMSG_SIZE個字符長，但是你應該試著保持消息不到80個字符以便它適合一幅標準終端屏幕的寬度。

對long long和double函數，主函數xxx()的返回值是函數值。對字符串函數，字符串在result和length參數中被返回。result是至少255個字節長的一個緩衝區，設置這些為返回值的內容和長度。例如：

memcpy(result, "result string", 13);
*length = 13;

字符串函數返回值也通常指向結果。

為了在主函數中表明一個NULL返回值，設定is_null為1：

*is_null = 1; 

為了在函數中表明一個錯誤返回，設定error參數為1：

*error = 1; 

如果對任何行xxx()設置*error為1，對當前行函數值是NULL，並且在該語句中處理的後續行，XXX()被調用。(xxx()甚至將不為隨後的行被調用。）注意：在MySQL 3.22.10以前的版本中，你應該都設置*error和*is_null：

*error = 1;
*is_null = 1;

14.1.4 編譯並安裝用戶定義函數

實現UDF的文件必須在伺服器運行的主機上被編譯並且安裝。這個程序下面描述，UDF例子文件包含在MySQL原始碼版本的“udf_example.cc”中，這個文件包含下列函數：

• metaphon()返回字符串參數的一個變音位(metaphon)字符串。這有點像一個soundex字符串，但是它更針對英語音調。
• myfunc_double()返回在其參數中所有字符的ASCII值的和，除以其參數長度之和。
• myfunc_int()返回其參數長度之和。
• lookup()返回對主機名的IP數。
• reverse_lookup()返回對一個IP數的主機名。函數可以以一個字符串"xxx.xxx.xxx.xxx"或4位數字被調用。

一個可動態裝載的文件應該編譯為一個共享的對像文件，使用像這樣的命令：

shell> gcc -shared -o udf_example.so myfunc.cc

通過運行在你的MySQL原始程式樹的“sql”目錄下的下列命令，你能很容易地找出對你的系統正確的編譯器選項：

shell> make udf_example.o

你應該運行一個類似於make顯示的編譯命令，除了你應該刪除接近行結尾的-c選項並且在行最後增加-o udf_example.so。（在一些系統上，你可能需要在命令上保留-c。）

一旦你編譯了包含UDF 的一個共享對像，你必須安裝它並且把它告訴MySQL。自“udf_example.cc”編譯一個共享對像產生一個名字類似“udf_example.so”的文件（準確的名字可以依平台不同而不同)。拷貝這個文件到被某個ld尋找的目錄，例如“/usr/lib”。在許多系統上，你能設定LD_LIBRARY或LD_LIBRARY_PATH環境變數，指向有UDF函數文件的目錄。dopen手冊頁告訴你你應該在你的系統上使用哪個變數。你應該在mysql.server或safe_mysqld中設置它並且重啟mysqld。

在庫被安裝以後，用這些命令通知mysqld有關新的函數的資訊：

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";

函數可使用DROP FUNCTION刪除：

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;

CREATE FUNCTION和DROP FUNCTION語句在mysql資料庫中更新系統表func。函數名、類型和共享庫名被保存在該表中。你必須有對mysql的insert和delete權限以創建和拋棄函數。

你不應該使用CREATE FUNCTION增加一個已經被創建的函數。如果你需要重新安裝函數，你應該用DROP FUNCTION刪除它，然後用CREATE FUNCTION重新安裝它。你將需要這樣做，例如，如果你重新編譯你的函數的一個新版本，以便mysqld獲得新版本，否則伺服器將繼續使用舊版本。

活躍函數在每次伺服器啟動時再次裝載，除非你使用--skip-grant-tables選項啟動mysqld。在這種情況下，UDF初始化被跳過並且UDF不可用。（活躍函數是一個用CREATE FUNCTION裝載並且沒有用DROP FUNCTION刪除的函數。)

14.2 增加一個新的原生函數

增加一個新的原生函數的程序在下面描述。注意，你不能往一個可執行檔版本刈莨入新函數，因為該程序涉及修改MySQL原始程式。你必須從原始碼版本自行編譯MySQL。也要注意，如果你遷移到MySQL的其他版本（例如，當一個新版本被釋放時)，你將需要用新版本重複該程序。

為了加入一個新的原生MySQL函數，遵循這些步驟：

1. 在“lex.h”加入1行，它在sql_functions[]數組中定義函數名。
2. 在“sql_yacc.yy”加入2行。一行指出示yacc應該定義的預處理器符號(這應該加在文件的開始)，然後定義函數參數並且將一個具有這些參數“項目”加到simple_expr語法分析規則中。有一個例子，檢查在“sql_yacc.yy 所有的SOUNDEX出現看看它使怎樣做的。
3. 在“item_func.h”中，聲明一個繼承Item_num_func或Item_str_func的類，取決於你的函數是返回一個數字或是一個字符串。
4. 在“item_func.cc”，增加下列聲明之一，取決於你是正在定義一個數字或是字符串函數：

   double   Item_func_newname::val()
   longlong Item_func_newname::val_int()
   String  *Item_func_newname::Str(String *str)
   

5. 你也可能應該定義下列函數：

   void Item_func_newname::fix_length_and_dec()
   

   這個函數至少應該基於給定的參數計算max_length，max_length是函數可以返回的字符的最大數目。如果主函數不能返回一個NULL值，這個函數也應該設置maybe_null = 0。函數可以通過檢查參數的maybe_null變數以便檢查函數參數的任何一個是否能返回NULL。

所有函數必須是執行緒安全的（thread-safed）。

對字符串函數，已知有一些額外的考慮：

• String *str參數提供一個可以用來保存結果的字符串緩衝區。
• 函數應該返回保存結果的字符串。
• 所有的當前字符串函數試圖避免分配任何內存，除非絕對必要！

第一章, 前一章, 下一章, 最後一章，目錄.