mysql生成隨機數

DROP FUNCTION IF EXISTS rand_string;
delimiter //
CREATE FUNCTION rand_string(l_num tinyint UNSIGNED,l_type tinyint UNSIGNED)
RETURNS varchar(127)
BEGIN
 -- Function : rand_string
 -- Author   : dbachina#dbachina.com
 -- Date     : 2010/5/30
 -- l_num : The length of random string
 -- l_type: The string type
 --         1.0-9
 --         2.a-z
 --         3.A-Z
 --         4.a-zA-Z
 --         5.0-9a-zA-Z
 --  :
  -- mysql> select rand_string(12,5) random_string;
  -- +---------------+
  -- | random_string |
  -- +---------------+
  -- | 3KzGJCUJUplw  |
  -- +---------------+
  -- 1 row in set (0.00 sec)
 DECLARE i int UNSIGNED DEFAULT 0;
 DECLARE v_chars varchar(64) DEFAULT '0123456789';
  DECLARE result varchar ( 255) DEFAULT '';

  IF l_type = 1 THEN
    SET v_chars = '0123456789';
  ELSEIF l_type = 2 THEN
    SET v_chars = 'abcdefghijklmnopqrstuvwxyz';
  ELSEIF l_type = 3 THEN
    SET v_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  ELSEIF l_type = 4 THEN
    SET v_chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  ELSEIF l_type = 5 THEN
    SET v_chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  ELSE
    SET v_chars = '0123456789';
  END IF;

  WHILE i < l_num DO
      SET result = concat( result,substr(v_chars,ceil(rand()*(length(v_chars)-1)),1) );
    SET i = i + 1;
  END WHILE;
  RETURN result;
END;
//
delimiter ;

轉自

[@more@]