mysql 创建函数

------------------------------------------------function get_random------------------------------------------------------------

#生成制定位数的整数

DELIMITER $$

USE `test`$$
DROP FUNCTION IF EXISTS `get_random`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_random`(len INT) RETURNS INT(11)
    DETERMINISTIC
BEGIN
DECLARE powerof BIGINT DEFAULT 1;
DECLARE result INT;
IF len IS NOT NULL THEN 
IF len >= 18 THEN 
SET powerof = POW(10 , 18); 
ELSE 
SET powerof = POW(10 , len); 
END IF; 
END IF; 
SELECT ROUND(ROUND(RAND(),len) * powerof) INTO result; 
    RETURN result;    
END$$

DELIMITER ;

------------------------------------------------function rand_zone------------------------------------------------------------

#生成区间内的整数

DELIMITER $$
USE `test`$$
DROP FUNCTION IF EXISTS `rand_zone`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_zone`(_min INT , _max INT ) RETURNS INT(11)
    DETERMINISTIC
BEGIN
DECLARE minVal INT DEFAULT 0;
DECLARE maxVal INT DEFAULT POW(2,31);
DECLARE limitVal INT DEFAULT POW(2,31);
DECLARE result INT DEFAULT 0;
IF _min IS NOT NULL THEN 
SET minVal = _min;
ELSE 
SET minVal = 0;
END IF; 

IF _max IS NOT NULL THEN 
SET maxVal = _max;
ELSE 
SET maxVal = POW(2,31);
END IF; 

#select (maxVal - minVal) into limitVal;
#Select (limitVal + 1) into limitVal ;

#select FLOOR (minVal + (RAND() * limitVal)) into result;
SELECT FLOOR (minVal + (RAND() * maxVal - minVal +1 )) INTO result;
    RETURN result;    
END$$


DELIMITER ;