本文为 mariadb 官方手册: CREATE FUNCTION 的译文
我提交到 MariaDB 官方手册的译文: https://mariadb.com/kb/zh-cn/create-function/
语法
- 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
描述
可以使用 CREATE FUNCTION 语句创建一个新的存储函数 stored function https://mariadb.com/kb/en/stored-functions/ 要使用 CREATE FUNCTION 语句, 必须要具备 CREATE ROUTINE https://mariadb.com/kb/en/grant/#database-privileges 权限
函数可以定义任意数量的参数, 在函数体 (func_body) 部分会返回一个值函数体部分可以是任意有效的 SQL 表达式, 例如某些 select 语句如果你有合适的权限, 你完全可以像调用内置函数一样调用存储函数关于权限的详细信息, 见下文: Security
此外, 你也可以使用 CREATE FUNCTION 语句的变体格式来安装一个用户自定义函数 (UDF) 关于 UDF, 详细信息见: CREATE FUNCTION (UDF) https://mariadb.com/kb/en/create-function-udf/
你可以使用一个圆括号包围 SELECT 作为 func_body 部分, 正如使用子查询一样但注意, SELECT 语句必须返回单个值 (标量值, 即单行且单列的值) 调用函数时, 如果 SELECT 语句返回了多列, 则报 1241 的错误, 如果 SELECT 语句返回了多行, 则报 1242 的错误为了保险, 可以使用 LIMIT 子句保证只返回单行数据
你可以使用 BEGIN...END https://mariadb.com/kb/en/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
如果函数根据给定的参数列表能够返回一个确定的结果, 则该函数是确定的 (deterministic) 如果函数的返回值 会因某些数据变量随机数或任意不确定的值而受影响, 则函数是不确定的此外, 如果存储函数中使用了不确定的函数 (如 NOW() 或 CURRENT_TIMESTAMP()), 则该存储函数也是不确定的
如果优化器知道函数是确定的, 它会选择一个更快更有效的执行计划你可以使用 DETERMINISTIC 关键字来定义这个 routine 如果你想显式将函数标记为不确定的(默认就是如此), 可以使用 NOT DETERMINISTIC 关键字
如果你将一个不确定的函数声明为 DETERMINISTIC, 将返回一个错误结果如果你将一个确定的函数声明为 NOT DETERMINISTIC, 则某些情况下, 该查询语句的性能将大幅降低
>OR REPLACE
如果使用了 OR REPLACE 子句, 它的行为等价于:
- DROP FUNCTION IF EXISTS function_name;
- CREATE FUNCTION function_name ...;
但不会删除该函数已有的权限 privileges
>IF NOT EXISTS
如果使用 IF NOT EXISTS 子句, 那么当函数存在时, MariaDB 将返回一个 warning 信息而不是直接返回错误 IF NOT EXISTS 不能和 OR REPLACE 一起使用
>[NOT] DETERMINISTIC
[NOT] DETERMINISTIC 子句还会影响二进制日志 binary logging https://mariadb.com/kb/en/binary-log/ , 因为日志中的语句格式无法 存储或替换不确定的语句
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 https://mariadb.com/kb/en/create-user/#account-names
SQL SECURITY 子句指定了当调用函数时所使用的权限如果 SQL SECURITY 的值为 INVOKER, 则将使用函数调用者的权限去对比 (即评估) 函数体中的语句权限如果 SQL SECURITY 的值为 DEFINER, 则总是使用 definer 用户的权限去评估函数体的权限默认值为 DEFINER
通过该子句, 你可以创建一个只允许某用户访问部分数据的函数例如, 你有一张存储了员工信息的表, 并且你已经授予了用户 roger 对该表某些列 (only on certain columns https://mariadb.com/kb/en/grant/#column-privileges ) 的 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 https://mariadb.com/kb/en/character-sets/ 如果定义了它们, 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 https://mariadb.com/kb/en/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,'!');
回到 Linux 系列文章大纲: http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲: http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲: http://www.cnblogs.com/f-ck-need-u/p/7586194.html
来源: https://www.cnblogs.com/f-ck-need-u/p/8698943.html