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);  -- 返回参数的平方根

加密相关

先来说AES加密：

-- AES_ENCRYPT:加密函数，第一个参数是需要加密的字符串，第二个参数是key
SELECT AES_ENCRYPT('ABCD', 'KEY');  -- 返回结果为乱码，表的字符集为拉丁或者utf8mb4可解决
SELECT CHAR_LENGTH(AES_ENCRYPT('ABCDE', 'KEY')); -- 加密字符串长度取决于原始字符串的长度
SELECT AES_DECRYPT(AES_ENCRYPT('ABCDE', 'KEY'), 'KEY')  
-- AES_DECRYPT：解密函数，第一个参数是AES_ENCRYPT加密后的字符串,第二个参数是key

-- 示例
CREATE TABLE t2(
id INT PRIMARY KEY AUTO_INCREMENT,
USER VARCHAR(32) NOT NULL,
pwd VARCHAR(512) DEFAULT NULL
)ENGINE=INNODB CHARSET=utf8mb4;

-- 由于ASE加密后的字符串长度不定，这里使用 HEX函数强转为16进制字符串
INSERT INTO t2(USER,pwd) VALUE('张开', HEX(AES_ENCRYPT('加密字符串','KEY')));
-- 再通过 UNHEX 函数转换16进制字符串为加密后的字符，再AES_DECRYPT解密
SELECT USER,AES_DECRYPT(UNHEX(pwd),'KEY') AS pwd FROM t2;

再来说MD5和SHA系列：

-- MD5加密
SELECT MD5('ABCD'),CHAR_LENGTH(MD5('ABCD'));  -- MD5没啥好说的

-- SHA1
SELECT SHA1('ABCD'),CHAR_LENGTH(SHA1('ABCD'));  -- 以40个十六进制数字的字符串形式返回
-- SHA1 是 SHA 的同义词

-- SHA2系列
SELECT SHA2('ABCD', 0),CHAR_LENGTH(SHA2('ABCD',0));      -- 64
SELECT SHA2('ABCD', 224),CHAR_LENGTH(SHA2('ABCD',224));  -- 56
SELECT SHA2('ABCD', 256),CHAR_LENGTH(SHA2('ABCD',256));  -- 64
SELECT SHA2('ABCD', 384),CHAR_LENGTH(SHA2('ABCD',384));  -- 96
SELECT SHA2('ABCD', 512),CHAR_LENGTH(SHA2('ABCD',512));  -- 128
SELECT SHA2('ABCD', 5122),CHAR_LENGTH(SHA2('ABCD',5122));  -- NULL

SHA2函数的第一个参数是需要加密的字符串，第二个参数必须是:

0/256:·0等于256，返回64位加密字符串
224:返回56位加密字符串
384:返回96位加密字符串
512:返回128位加密字符串
如果是其它数值，返回NULL

参考：https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html

日期时间相关

SELECT NOW();  -- 返回当前日期时间
SELECT CURRENT_TIME();  -- 返回当前时间
SELECT CURRENT_DATE();  -- 返回当前日期
SELECT UNIX_TIMESTAMP()  -- 返回时间戳时间

SELECT DAYOFMONTH(NOW()); -- 返回月份中的一天（0-31）
SELECT DAYOFYEAR(NOW());  -- 返回当前天位于一年中的第多少天
SELECT YEAR(NOW());  -- 提取年
SELECT HOUR(NOW());  -- 提取小时
SELECT DAY(NOW());  -- 提取天
SELECT MINUTE(NOW());  -- 提取分钟
SELECT MONTHNAME(NOW());  -- 返回当前月份
SELECT QUARTER(NOW());  -- 根据日期返回当前位于第几个季度
SELECT LAST_DAY(NOW()), LAST_DAY('2020-09-30');  -- 返回参数的月份的最后一天

SELECT DAYNAME(NOW());  -- 返回工作日的名称
SELECT WEEK(NOW());  -- 返回当前位于一年中位于第几个星期
SELECT WEEKDAY(NOW());  -- 返回工作日索引，0：星期一、1：星期二.....
SELECT WEEKOFYEAR(NOW());  -- 返回日期的日历周，范围是从1到的数字53

再来看日期时间的格式化转换：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- 2020-09-17 15:18:53
SELECT DATE_FORMAT(NOW(), '%y-%m-%d %H:%i:%s');  -- 20-09-17 15:19:40
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');  -- Sunday October 2009
SELECT DATE_FORMAT('1900-10-04 22:23:00','%D %y %a %d %m %b %j');  -- 4th 00 Thu 04 10 Oct 277
SELECT DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');  -- 22 22 10 10:23:00 PM 22:23:00 00 6
SELECT DATE_FORMAT('1999-01-01', '%X %V');  -- 1998 52
SELECT DATE_FORMAT('2006-06-00', '%d');  -- 00

上面是关于日期格式化的常见用法，而时间格式化参考日期格式化即可：

SELECT TIME_FORMAT(NOW(), '%H:%i:%s');  -- 15:18:26

TIME_FORMAT的用法类似于DATE_FORMAT，但是格式字符串可能只包含小时、分钟、秒和微秒的格式说明符。其他说明符生成空值或0。

其他

SELECT UUID();  -- 返回 UUID
SELECT SLEEP(3);  -- 睡指定秒数
ISNULL()      -- 在查询中，可以用来判断值是否为null

更多MySQL内置函数及用法参考：

自定义函数

除了内置函数，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;

that's all,see also:

mysql之自定义函数 | MySQL 自定义函数的创建、修改、删除查看方法 | 【学习笔记】mysql 自带aes_encrypt()加密与aes_decrypt()解密及乱码问题解决 | MySQL利用AES_ENCRYPT()与AES_DECRYPT()加解密的正确方法示例 | Python开发【第十八篇】：MySQL（二）

posted @ 2020-09-17 16:15 听雨危楼阅读(854) 评论(0) 编辑收藏举报

刷新页面返回顶部

王战山的学习笔记

非淡泊无以明志，非宁静无以致远

1-MySQL - 函数(FUNCTION)

about

内置函数

自定义函数

公告