Mysql自定義函數(shù)的創(chuàng)建和執(zhí)行

假設(shè)students表中包含id和name兩個(gè)字段，創(chuàng)建一個(gè)函數(shù)，函數(shù)的作用是根據(jù)id查找name

1.創(chuàng)建表，插入數(shù)據(jù)

create table students(id int,name varchar(100));
insert into students(id,name) values(1,'annie'),(2,'bell'),(3,'danny');

2.創(chuàng)建函數(shù)

DELIMITER //
create function find_student(id int) returns varchar(100)
READS SQL DATA
begin
?? ?declare sname varchar(100) default '';
? ? select students.name into sname from students where students.id=id;
? ? return sname;
end //
DELIMITER ;

需要注意的事項(xiàng)：

1）使用DELIMITER//修改分隔符

mysql的默認(rèn)語句結(jié)束符號是分號，當(dāng)mysql遇到分號時(shí)就自動(dòng)執(zhí)行當(dāng)前語句。因?yàn)楹瘮?shù)定義時(shí)包含多條sql語句，所以使用DELIMITER //先將分隔符設(shè)置為//，等函數(shù)創(chuàng)建語句完成后，再將分隔符改回分號即可。

2）READS SQL DATA

之前我沒寫這句話，但是創(chuàng)建時(shí)mysql報(bào)錯(cuò)，提示Error Code: 1418. 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)

上網(wǎng)查了一下，意思是沒有聲明mysql函數(shù)的類型：

mysql開啟了bin-log, 我們就必須指定我們的函數(shù)是否是哪種類型：

• 1 DETERMINISTIC 不確定的
• 2 NO SQL 沒有SQl語句，當(dāng)然也不會(huì)修改數(shù)據(jù)
• 3 READS SQL DATA 只是讀取數(shù)據(jù)，當(dāng)然也不會(huì)修改數(shù)據(jù)
• 4 MODIFIES SQL DATA 要修改數(shù)據(jù)
• 5 CONTAINS SQL 包含了SQL語句
• 所以我加上了READS SQL DATA

3）使用局部變量

變量定義：我這里使用declare sname varchar(100) default ‘’;定義了局部變量sname，

變量使用：

可以使用select students.name into sname from students where students.id=id;為變量賦值

也可以直接使用set語句來賦值，如set sname=‘test’

3.執(zhí)行函數(shù)：select 函數(shù)名(參數(shù)值);

select find_student(3);

Mysql自定義函數(shù)創(chuàng)建失敗問題

案例

目前在項(xiàng)目中，執(zhí)行創(chuàng)建mysql的函數(shù)出錯(cuò)，

mysql 創(chuàng)建函數(shù)出錯(cuò)信息如下：

Caused by: java.sql.SQLException: 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)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2373)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2739)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2440)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    ... 35 more

這是因?yàn)橛幸粋€(gè)安全參數(shù)沒有開啟,log_bin_trust_function_creators 默認(rèn)為0，是不允許function的同步的，開啟這個(gè)參數(shù)，就可以創(chuàng)建成功了。

查看是否開啟：

show variables like '%func%';
+---------------------------------+-------+ 
| Variable_name     | Value | 
+---------------------------------+-------+ 
| log_bin_trust_function_creators | ON | 
+---------------------------------+-------+ 
1 row in set (0.00 sec) 

為on則是開啟了

set global log_bin_trust_function_creators = 1;

可以通過這個(gè)命令設(shè)置，但是MySQL重啟后就失效了。

所有最后是通過修改MySQL數(shù)據(jù)庫的配置文件

在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1

修改完后重啟MySQL。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。