月度归档:2017年05月

mysql生成固定位数随机字母以及数字

随机数字

CREATE DEFINER=`root`@`%` FUNCTION `rand_int`(counts INTEGER) RETURNS varchar(20) CHARSET utf8
BEGIN
       DECLARE sTemp VARCHAR(20);
    DECLARE sTempCounts INTEGER;
       SET sTemp = CONCAT( ROUND(ROUND(RAND(),counts)*(POW(10,counts))),'');

    IF(CHAR_LENGTH(sTemp)<counts) THEN

      SET sTempCounts = counts - CHAR_LENGTH(sTemp);
      SET sTemp = CONCAT(sTemp, RIGHT(CONCAT(POW(10,sTempCounts),''),sTempCounts));
    END IF;
      RETURN sTemp;
END

随机字母:

CREATE DEFINER=`root`@`%` FUNCTION `rand_str`(  
    f_num INT UNSIGNED
    ) RETURNS varchar(200) CHARSET latin1
BEGIN
      DECLARE i INT UNSIGNED DEFAULT 0;   
      DECLARE v_result VARCHAR(200) DEFAULT '';   
      DECLARE v_dict VARCHAR(200) DEFAULT '';  
      SET v_dict = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';  
      SET v_dict = LPAD(v_dict,200,v_dict);  
      WHILE i < f_num   
      DO   
        SET v_result = CONCAT(v_result,SUBSTR(v_dict,CEIL(RAND()*200),1));  
        SET i = i + 1;   
      END WHILE;    
      RETURN v_result;   
END