mysql 存储过程生成_MySQL函数和存储过程生成电话号码

电话号码是有11位数字组成，所以在生成电话号码之前我们先来看一看怎样通过函数生成字符串。

DELIMITER $$

CREATE FUNCTION rand_num(n int) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str varchar(20) DEFAULT '0123456789';

DECLARE return_str varchar(255) DEFAULT '';

DECLARE i INT DEFAULT 0;

WHILE i 

SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*10 ),1));

SET i = i +1;

END WHILE;

RETURN return_str;

END $$

DELIMITER ;

MySQL的随机函数rand()返回的是一个float类型的数字n，n的取值范围是0<=n<1。值得注意的是能取到0但是不能取到1。

显然上面的rand()*10+1的取值范围为[1,11),能取到1但是不能取到11，内置函数floor的作用是向下取整。所以floor(rand()*10+1)的取值范围为[1,10]。

因为MySQL中没有内置数组，所以我们用字符串来模拟，首先来看内置函数substring(str,x,y)是表示在字符串str中从x位置开始，截取长度为y的字符串。X从1开始。

内置函数concat(str1,str2,…strn)的作用是把字符串str1到strn拼接成一个字符串。所以上面的函数不难理解。就是每次循环从’0123456789’这10个数字中随机截取一个数字拼接在我们要返回的字符串中。参数n表示拼接字符串的长度。

有了上面的函数，我们可以通过rand_num(11)这样的方式来得到一个字符串，但是我们可能得到的是00123456789这样的11数，显然不太像电话号码。没事我们再改进一下，为什么我们得到的字符串不像呢？因为一般电话号码都有固定的头，例如：136,189,156这样的，所以我们可以把这样的头加上，下面我们来写一个模拟得到手机头的函数。

DELIMITER $$

create function phone_head() returns char(3)

begin

-- 130 131 132 133 134 135 136 137 138 139 186 187 189 151 157常用的手机头

declare head char(3);

declare bodys varchar(100) default "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";

declare starts int;

set starts = 1+floor(rand()*15)*4;

set head = trim(substring(bodys,starts,3));

return head;

end $$

DELIMITER ;

我们还是有字符串来模拟，用空格分开。我们一共选取了15个号码头，每一个号码头有3位，所以我们截取字符串的开始位置从1,4，9····也就是start=1+4*n(0<=n<=14)。上面已经介绍了 rand()函数返回值n是float 其中 0<=n<1,floor是向下取整

set head = substring(bodys,starts,3);在字符串bodys中从starts位置截取三位

下面我们来生成电话号码：

DELIMITER $$

create function gen_phone() returns varchar(20)

begin

declare phone varchar(20);

set phone = trim(concat(phone_head(),rand_num(8)));

return phone;

end $$

DELIMITER ;

我们先生成3位电话号码头，然后用rand_num(8) 生成8位数字，然后用concat内置函数把他们拼接在一起。

下面我们再来写一个存储过程来生成100个电话号码来看一看：

DELIMITER $$

create procedure insert_phone()

begin

declare i int default 0;

declare phone varchar(20);

set phone = trim(concat(phone_head(),rand_num(8)));

while i<100 do

insert into phone(phone) values(phone);

set i = i+1;

set phone = trim(concat(phone_head(),rand_num(8)));

end while;

end $$

DELIMITER ;

上面的存储过程就是想phone表中插入100条我们写的函数生成的电话号码。接下来就是检验成果的时候了。还是先来创建一张表：

DROP TABLE IF EXISTS `phone`;

CREATE TABLE `phone` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`phone` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

调用我们的存储过程insert_phone：

查看一下我们是否生成成功：

怎么样，是不是看样子有一点像电话号码了，我们以后写一些简单的测试数据的时候完全可以用MySQL提供给我们的函数来实现模拟生成测试数据。