MySQL - 函数(FUNCTION)

about

MySQL提供了丰富的内置函数自定义函数。

而我们也对这些函数有所了解，比如聚合函数。

本篇再来了解一些内置函数和自定义函数的编写。

写在前面的话：默认情况下，函数名称和其后的括号之间必须没有空格。这有助于MySQL解析器区分函数调用和对与函数名称相同的表或列的引用。但是，函数参数周围可以有空格。

内置函数

字符串相关

SELECT CONCAT('root','@','127.0.0.1');
SELECT CONCAT(USER,'@',HOST) FROM mysql.user;      -- 在合适的位置添加分隔符，较为灵活
SELECT CONCAT_WS('@',USER,HOST) FROM mysql.user;    -- 第一个参数为分隔符
SELECT USER,GROUP_CONCAT(HOST) FROM mysql.user GROUP BY USER;   -- 适用于分组，行转列
SELECT CHAR_LENGTH('ABCD');    -- 返回字符串的长度
SELECT CHARACTER_LENGTH('ABCD');  -- 和 CHAR_LENGTH 的同义词
SELECT LOWER('ABCD');    -- 以小写形式返回字符串
SELECT UPPER('abcd');    -- 以大写形式返回字符串, UPPER 是 UCASE 的同义词
SELECT TRIM(' ABCD ');  -- 去除字符串两边的空格
SELECT BIN(12);  -- 返回十进制数字的二进制表示
SELECT OCT(12);  -- 返回十进制数字的八进制表示
SELECT HEX(12), HEX('ABCD');  -- 返回十进制数字或者字符串的十六进制的表示形式
SELECT UNHEX(HEX('ABCD'));  -- 返回被转化为十六进制表示的原始数字或字符串

数值相关

SELECT ABS(123);    -- 返回数值的绝对值
SELECT 5 / 2,5 DIV 2;   -- 向下整除
SELECT TRUNCATE(1.1234,2),TRUNCATE(-1.1234,2);  -- 包括指定位数小数,数值可为负数
SELECT PI();    -- 返回6为小数的 Π 值
SELECT SQRT(2);  -- 返回参数的平方根

自定义函数

除了内置函数，MySQL还支持自定义函数。

创建

-- 创建一个函数，返回两个整数之和
DELIMITER //
CREATE FUNCTION f1(
    n1 INT,
    n2 INT)   -- 创建函数 f1 参数可以是MySQL支持的那些类型
RETURNS INT  -- 该函数的返回值也是 int 类型
BEGIN    -- 标识函数体开始
    DECLARE num INT;    -- 定义一个int类型的变量
    SET num = n1 + n2;
    RETURN(num);

END //    -- 标识函数体结束
DELIMITER ;

另外,函数中不能有SELECT语句

执行

SELECT f1(1,1);

-- 在查询中使用
CREATE TABLE t3(
n1 INT NOT NULL DEFAULT 0,
n2 INT NOT NULL DEFAULT 0
)ENGINE=INNODB CHARSET=utf8mb4;

INSERT INTO t3(n1,n2) VALUES(2,3),(3,4);

SELECT n1,n2,f1(n1,n2) AS '两数相加' FROM t3;
+----+----+--------------+
| n1 | n2 | 两数相加     |
+----+----+--------------+
|  2 |  3 |            5 |
|  3 |  4 |            7 |
+----+----+--------------+
2 rows in set (0.01 sec)

修改

ALTER FUNCTION 函数名 选项;

函数的修改只能修改一些如COMMENT的选项，不能修改内部的SQL语句和参数列表，所以直接删了重建就完了。

查看

SHOW FUNCTION STATUS;   -- 返回所有自定义函数
SHOW FUNCTION STATUS LIKE 'f%'  -- 过滤
SHOW CREATE FUNCTION f1;  -- 返回自定义函数的创建信息
SHOW CREATE FUNCTION tt.f1;   -- 指定数据库下的自定义函数

删除

DROP FUNCTION f1;