The Ultimate Guide to High Availability with MariaDB

CREATE FUNCTION

You are viewing an old version of this article. View the current version here.

语法

CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body


func_parameter:
    param_name type


type:
    Any valid MariaDB data type


characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'


func_body:
    Valid SQL procedure statement

Contents

  1. 语法
  2. 描述
    1. 聚合函数
    2. RETURNS子句
    3. LANGUAGE SQL
    4. OR REPLACE
    5. IF NOT EXISTS
    6. [NOT] DETERMINISTIC
    7. MODIFIES SQL DATA
    8. READS SQL DATA
    9. CONTAINS SQL
    10. NO SQL
  3. Security
  4. Character sets 和 collations
  5. 示例
  6. See Also

描述

可以使用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属性。

聚合函数

MariaDB starting with 10.3.3

从MariaDB 10.3.3开始，可以创建存储聚合函数。详细信息见Stored Aggregate Functions。

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

MariaDB starting with 10.1.3

如果使用了OR REPLACE子句，它的行为等价于：

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

但不会删除该函数已有的权限privileges。

IF NOT EXISTS

MariaDB starting with 10.1.3

如果使用 IF NOT EXISTS 子句，那么当函数存在时，MariaDB将返回一个warning信息而不是直接返回错误。IF NOT EXISTS不能和OR REPLACE一起使用。

[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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.