知乎专栏 |
函数会返回数据,调用函数使用 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)
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)