mysql生成a-z_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 ;