函数会返回数据,调用函数使用 select fun(),不能使用call调用,否则提示
mysql> call myfun();
ERROR 1305 (42000): PROCEDURE test.myfun does not exist
下面做一个实验
CREATE TABLE `t` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`n` INT(11) UNSIGNED NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5;
CREATE DEFINER=`neo`@`%` FUNCTION `myfun`()
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
INSERT INTO t (n) VALUES(rand()*100);
RETURN LAST_INSERT_ID();
END
mysql> select myfun();
+---------+
| myfun() |
+---------+
| 9 |
+---------+
1 row in set, 2 warnings (0.07 sec)
4.8.1. TIMESTAMP TO ISO8601
USE `netkiller`;
DROP function IF EXISTS `timestamp_to_iso8601`;
DELIMITER $$
USE `netkiller`$$
CREATE DEFINER=`neo`@`db.netkiller.cn` FUNCTION `timestamp_to_iso8601`(dt timestamp) RETURNS varchar(24) CHARSET utf8
BEGIN
RETURN DATE_FORMAT( CONVERT_TZ(dt, @@session.time_zone, '+00:00') ,'%Y-%m-%dT%T.000Z');
END$$
DELIMITER ;
调用函数
mysql> select timestamp_to_iso8601(current_timestamp()) as iso8601;
+--------------------------+
| iso8601 |
+--------------------------+
| 2017-12-07T07:21:22.000Z |
+--------------------------+
1 row in set (0.00 sec)
原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。