MySQL支持自定义函数,定义好之后,调用方式与调用MySQL预定义的系统函数一样。

语法

  1. CREATE FUNCTION 函数名(参数名 参数类型,...)
  2. RETURNS 返回值类型
  3. [characteristics ...]
  4. BEGIN
  5. 函数体 #函数体中肯定有 RETURN 语句
  6. END

说明

1、参数列表:指定参数为IN、OUT或INOUT只对PROCEDURE是合法的,FUNCTION中总是默认为IN参数。

2、RETURNS type 语句表示函数返回数据的类型;

RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。

3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同。

4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句,也可以省略BEGIN…END。

调用函数

在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于,存储函数是用户自己定义的,而内部函数是MySQL的开发者定义。

  1. SELECT 函数名(实参列表)

例子

例一:

创建存储函数,名称为email_by_name(),参数定义为空,该函数查询Abel的email,并返回,数据类型为字符串型。

自定义函数

  1. DELIMITER //
  2. CREATE FUNCTION email_by_name()
  3. RETURNS VARCHAR(25)
  4. DETERMINISTIC
  5. CONTAINS SQL
  6. READS SQL DATA
  7. BEGIN
  8. RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
  9. END //
  10. DELIMITER ;

调用函数

  1. SELECT email_by_name();

例二:

创建存储函数,名称为email_by_id(),参数传入emp_id,该函数查询emp_id的email,并返回,数据类型为字符串型。

自定义函数

  1. #创建函数前执行此语句,保证函数的创建会成功
  2. SET GLOBAL log_bin_trust_function_creators = 1;
  3. DELIMITER //
  4. CREATE FUNCTION email_by_id(emp_id INT)
  5. RETURNS VARCHAR(25)
  6. BEGIN
  7. RETURN (SELECT email FROM employees WHERE employee_id = emp_id);
  8. END //
  9. DELIMITER ;

调用函数

  1. SELECT email_by_id(101);
  2. # 或者
  3. SET @emp_id := 102;
  4. SELECT email_by_id(@emp_id);

例三:

创建存储函数count_by_id(),参数传入dept_id,该函数查询dept_id部门的员工人数,并返回,数据类型为整型。

自定义函数

  1. DELIMITER //
  2. CREATE FUNCTION count_by_id(dept_id INT)
  3. RETURNS INT
  4. BEGIN
  5. RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
  6. END //
  7. DELIMITER ;

函数调用

  1. SET @dept_id := 50;
  2. SELECT count_by_id(@dept_id);

注意:

若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”,有两种处理方法:

  • 方式1:加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
  • 方式2:
    1. SET GLOBAL log_bin_trust_function_creators = 1;