Contents

可以使用 CREATE FUNCTION 语句创建一个新的存储函数 stored function 。要使用 CREATE FUNCTION 语句，必须要具备 CREATE ROUTINE 数据库权限。

函数可以定义任意数量的参数，在函数体(func_body)部分会返回一个值。函数体部分可以是任意有效的SQL表达式，例如某些select语句。如果你有合适的权限，你完全可以像调用内置函数一样调用存储函数。关于权限的详细信息，见下文： Security 。

此外，你也可以使用 CREATE FUNCTION 语句的变体格式来安装一个用户自定义函数(UDF)。关于UDF，详细信息见： CREATE FUNCTION (UDF) 。

你可以使用一个圆括号包围 SELECT 作为func_body部分，正如使用子查询一样。但注意， SELECT 语句必须返回单个值(标量值，即单行且单列的值)。调用函数时，如果SELECT语句返回了多列，则报1241的错误，如果SELECT语句返回了多行，则报1242的错误。为了保险，可以使用 LIMIT 子句保证只返回单行数据。

你可以使用 BEGIN...END 语句块替换这里的 RETURN 子句，但是在语句块中，必须要包含一个 RETURN 语句。当调用函数时，执行到 RETURN 子句时将立即返回其结果，在RETURN子句之后的语句都不会再执行。

默认情况下，函数是关联到默认数据库上的。如果要将函数显式关联到一个指定的数据库，可以在创建时使用全称 db_name . func_name 。如果创建的存储函数名和内置的函数名同名，则必须使用全称来调用它。

定义存储函数时，参数列表可以为空。如果指定参数名，则参数名不区分大小写。

每个参数都可以声明为任意有效的数据类型，但无法使用COLLATE属性。

RETURNS子句

RETURNS 子句指定函数的返回类型。可以使用 NULL 值来表示返回任意有效数据类型。

如果 RETURN 子句的返回值类型和此处定义的数据类型不一致会如何？这取决于创建函数的时候， SQL_MODE 的影响行为。

如果SQL_MODE为strict模式的值(即指定了STRICT_ALL_TABLES或STRICT_TRANS_TABLES)，将报1366错误。

除这种情况，如果返回值类型不一致，则返回值将被强制转换为指定的数据类型。例如， RETURNS 子句指定返回一个 ENUM 或 SET 数据类型，但 RETURN 子句返回了一个整型，则返回值将强制转换为 ENUM 或 SET 成员对应的字符串(译者注：虽然 ENUM 允许存储数值，但强烈建议不要存储数值，因为非常容易混淆 ENUM 的索引值和实际存储的数值，因此这里直接说是字符串)。

MariaDB将在创建routine的时候保留系统变量SQL_MODE的值，以后任何时间调用routine时都使用该SQL_MODE值，而不管当前调用routine时的SQL MODE值是什么。

LANGUAGE SQL

LANGUAGE SQL 代表的是一个标准的SQL子句，它是为了移植性而存在的。但是，该子句在MariaDB中没有任何意义，因为MariaDB的存储函数中唯一支持的语言只有SQL。

OR REPLACE

DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ...;

IF NOT EXISTS

[NOT] DETERMINISTIC

如果函数根据给定的参数列表能够返回一个确定的结果，则该函数是确定的(deterministic)。如果函数的返回值 会因某些数据、变量、随机数或任意不确定的值而受影响，则函数是不确定的。此外，如果存储函数中使用了不确定的函数(如 NOW() 或 CURRENT_TIMESTAMP() )，则该存储函数也是不确定的。

如果优化器知道函数是确定的，它会选择一个更快更有效的执行计划。你可以使用 DETERMINISTIC 关键字来定义这个routine。如果你想显式将函数标记为不确定的(默认就是如此)，可以使用 NOT DETERMINISTIC 关键字。

如果你将一个不确定的函数声明为 DETERMINISTIC ，将返回一个错误结果。如果你将一个确定的函数声明为 NOT DETERMINISTIC ，则某些情况下，该查询语句的性能将大幅降低。

[NOT] DETERMINISTIC 子句还会影响二进制日志 binary logging ，因为日志中的语句格式无法 存储或替换不确定的语句。

CONTAINS SQL , NO SQL , READS SQL DATA 以及 MODIFIES SQL DATA 是信息类的子句，它们告诉服务器该函数是做什么的。MariaDB不会对这些语句做任何语法检查。如果不指定这些语句，则默认使用 CONTAINS SQL 。

MODIFIES SQL DATA

MODIFIES SQL DATA 意味着函数中包含了要修改数据库中数据的语句。例如函数中使用了类似于 DELETE , UPDATE , INSERT , REPLACE 或DDL类的语句。

READS SQL DATA

READS SQL DATA 意味着函数中包含了从数据库中读取数据的语句，但是不会修改任何数据。例如函数中使用了不包含任何写操作的 SELECT 语句。

CONTAINS SQL

CONTAINS SQL 意味着函数包含了至少一条SQL语句，但是它不会读也不会写数据库。例如函数中包含了 SET 或 DO 子句。

NO SQL

NO SQL 意味着什么？啥也不意味着。因为MariaDB目前除了SQL语言，不支持任何其他语言。

Security

要想调用函数，你必须要拥有该函数的 EXECUTE 权限。

MariaDB会自动为创建函数 CREATE FUNCTION 的用户授予 EXECUTE 和 ALTER ROUTINE 权限，即使使用了 DEFINER 子句。

每个函数都有一个关联的账号(即definer)。默认情况下，definer即为函数的创建者。可以使用 DEFINER 子句显式指定关联到其他账号上。要使用 DEFINER ，你必须要拥有 SUPER 权限。详细信息见： Account Names 。

SQL SECURITY 子句指定了当调用函数时所使用的权限。如果 SQL SECURITY 的值为 INVOKER ，则将使用函数调用者的权限去对比(即评估)函数体中的语句权限。如果 SQL SECURITY 的值为 DEFINER ，则总是使用definer用户的权限去评估函数体的权限。默认值为 DEFINER 。

通过该子句，你可以创建一个只允许某用户访问部分数据的函数。例如，你有一张存储了员工信息的表，并且你已经授予了用户 roger 对该表某些列( only on certain columns )的 SELECT 权限。

CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;

可以定义一个函数来获取部门中薪水最高的用户，并授予 EXECUTE 权限：

CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
  (SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;

由于 SQL SECURITY 的默认值为 DEFINER ，无论 roger 用户何时调用该函数，都会使用你的权限来执行其中的子查询。只要你有查询每个员工薪水的权限，即使函数调用者不具备直接查询薪水的权限，他们也能获取到每个部门的最高薪水。

Character sets 和 collations

可以为函数声明使用任意有效的字符集和排序规则 character set and collation 。如果定义了它们，COLLATE属性需要定义在CHARACTER SET之后。

如果没有指定字符集和排序规则，则使用函数创建时的系统默认值。即使之后系统默认字符集和排序规则改变了，函数所使用的字符集也不会随之改变。这种情况下，应该重建函数并使用数据库所使用的字符集和排序规则。

下面的函数示例使用了一个参数，并在函数中执行了一个SQL内置函数CONCAT()，最后返回结果。

CREATE FUNCTION hello (s CHAR(20))
    RETURNS CHAR(50) DETERMINISTIC
    RETURN CONCAT('Hello, ',s,'!');
SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+

你可以在函数内部使用一个语句块来操作数据(即使用DML)，例如 INSERT 和 UPDATE 。下面的例子中创建了一个函数计数器，它使用了一个临时表来存储当前的值。因为语句块包含了语句终止符号";"，因此必须首先使用 DELIMITER 语句改变语句的终止符，使得函数体中能够使用分号。更多信息见 Delimiters in the mysql client 。

CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
  BEGIN
    UPDATE counter SET c = c + 1;
    RETURN (SELECT c FROM counter LIMIT 1);
  END //
DELIMITER ;

字符集和排序规则：

CREATE FUNCTION hello2 (s CHAR(20))
  RETURNS CHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' DETERMINISTIC
  RETURN CONCAT('Hello, ',s,'!');

See Also

• Stored Aggregate Functions
• CREATE FUNCTION (UDF)
• SHOW CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION
• SHOW FUNCTION STATUS
• Stored Routine Privileges
• Information Schema ROUTINES Table