Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | About

4.8. 函數

函數會返回數據,調用函數使用 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)