定义

  • 自定义函数和存储过程很类似，但是必须要有返回值。
  • 与内置的 sum()、max() 等 MySQL 原生函数使用方法类似。
    • select fun(val);
    • select * from t where col= fun(val);
  • 自定义函数可能在遍历每条记录中使用，注意性能损耗。

语法

  1. CREATE
  2.     [DEFINER = { user | CURRENT_USER }]
  3.     FUNCTION sp_name ([func_parameter[,...]])
  4.     RETURNS type  -- 必须有返回值
  5.     [characteristic ...] routine_body
  7. func_parameter:
  8.     param_name type
  10. type:
  11.     Any valid MySQL data type
  13. characteristic:
  14.     COMMENT 'string'
  15.   | LANGUAGE SQL
  16.   | [NOT] DETERMINISTIC
  17.   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  18.   | SQL SECURITY { DEFINER | INVOKER }
  20. routine_body:
  21.     Valid SQL routine statement
  23. -- 删除
  24. DROP FUNCTION fun_name;

示例

  1. -- 老师给的例子，还是阶乘，用自定义函数的方式
  3. mysql> delimiter //
  4. mysql> 
  5. mysql> create function fun_test_1(total int)
  6.     -> returns int
  7.     -> begin
  8.     ->     declare i int;
  9.     ->     declare res int;
  10.     ->     set i := 1;
  11.     ->     set res := 1;
  12.     ->     if total <= 0 then
  13.     ->         set total := 1;
  14.     -> end if;
  15.     ->     while i <= total do
  16.     ->         set res := res * i;
  17.     ->         set i := i + 1;
  18.     ->     end while;
  19.     ->     return res;
  20.     -> end;//
  21. ERROR 1418 (HY000): 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)
  22. -- 报错，提示因为函数的声明中没有"DETERMINISTIC, NO SQL, or READS SQL DATA"等关键字 ，需要使用打开参数 log_bin_trust_function_creators
  24. -- 解决方法，set global log_bin_trust_function_creators=1; 开启该选项可能会引起主从服务器不一致
  25. --           或者 增加 上述相应功能的关键字
  28. -- 使用 deterministic 关键字
  29. -- 当你声明一个函数的返回是确定性的，则必须显示的使用deterministic关键字，默认是 no deterministic的
  30. mysql> delimiter //
  31. mysql> create function fun_test_1(total int)
  32.     -> returns int deterministic -- 这个只是告诉MySQL我这个函数是否会改变数据
  33.                                  -- 即使我下面使用了insert，update等DML语句，MySQL不会检查
  34.                                  -- 函数是否会改变数据，完全依赖创建函数的用户去指定的关键字
  35.                                  -- 而非真的是否有修改数据
  36.                                  -- 只是声明，而非约束
  37.     -> begin
  38.     ->     declare i int;
  39.     ->     declare res int;
  40.     ->     set i := 1;
  41.     ->     set res := 1;
  42.     ->     if total <= 0 then
  43.     ->         set total := 1;
  44.     ->     end if;
  45.     ->     while i <= total do
  46.     ->         set res := res * i;
  47.     ->         insert into test_proc_1 values(i, res);  -- 在自定义函数中，同样可以使用sql
  48.                                                         -- 并且该SQL是insert，其实和deterministic违背。
  49.     ->         set i := i + 1;
  50.     ->     end while;
  51.     ->     return res;
  52.     -> end;//
  53. Query OK, 0 rows affected (0.01 sec)
  54. mysql> delimiter ;
  56. mysql> truncate table test_proc_1;
  57. Query OK, 0 rows affected (0.11 sec)
  59. mysql> select fun_test_1(6);  -- return了6的阶乘，720
  60. +---------------+
  61. | fun_test_1(6) |
  62. +---------------+
  63. |           720 |
  64. +---------------+
  65. 1 row in set (0.02 sec)
  67. mysql> select * from test_proc_1; 
  68. +------+------+
  69. | a    | b    |
  70. +------+------+
  71. |    1 |    1 |
  72. |    2 |    2 |
  73. |    3 |    6 |
  74. |    4 |   24 |
  75. |    5 |  120 |
  76. |    6 |  720 |  -- 使用了insert语句进行插入阶乘的历史记录
  77. +------+------+
  78. 6 rows in set (0.00 sec)
  80. -- 关键字简单说明
  81. -- DETERMINISTIC：当给定相同的输入，产生确定的结果
  82. -- NOT DETERMINISTIC：默认值，认为产生的结果是不确定的
  84. -- READS SQL DATA：只是读取SQL数据
  85. -- MODIFIES SQL DATA：会修改数据
  86. -- NO SQL：没有SQL遇见
  87. -- CONTAINS SQL：包含SQL语句，但是没有读写语句，理论有select now()等

官方文档：https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/lzcosc 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。