MySQL一些随机函数记录

MySQL一些随机函数记录

有时候我们需要模拟数据的进行测试，你是不是还在输入姓名，张三1，张三2？，手机号码13411111111？来来来，咱们可以用自定义函数来解决随机数据的生成，通过存储过程来批量插入数据。

1、创建一张学生表做测试

CREATE DATABASE IF NOT EXISTS myschool;
-- 选择myschool数据库
USE myschool;

-- 创建学生表结构
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentNo` INT(4) NOT NULL COMMENT '学号',
	`studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
	`sex` CHAR(2) DEFAULT '男' NOT NULL  COMMENT '性别',
	`gradeId` INT(4)  UNSIGNED COMMENT '年级编号',
	`phone` VARCHAR(50)  COMMENT '联系电话',
	`bornDate` DATETIME  COMMENT '出生时间',
	PRIMARY KEY(`studentNo`)	
) COMMENT '学生表';

2、随机函数

这些代码有些参考或来源于网络，有些是自己写的，一起执行吧，函数之间有些是依赖关系。

-- 随机姓名
DROP FUNCTION IF EXISTS randName;
DELIMITER //
CREATE FUNCTION randName()
RETURNS VARCHAR(3)
BEGIN
    DECLARE xing VARCHAR(2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';
    DECLARE ming VARCHAR(2056) DEFAULT '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';
    DECLARE x_len INT DEFAULT CHAR_LENGTH(xing);
    DECLARE m_len INT DEFAULT CHAR_LENGTH(ming);
    DECLARE stuName VARCHAR(3) DEFAULT '';
    -- 随机姓
    SET stuName = CONCAT(stuName, SUBSTRING(xing,FLOOR(1 + RAND() * x_len),1));
    -- 随机第一个字名
    SET stuName = CONCAT(stuName, SUBSTRING(ming,FLOOR(1 + RAND() * m_len),1));
    -- 如果随机数小于0.5，再生成一个字
    IF RAND()>0.500 THEN
	-- 再选一个字做名
	SET stuName = CONCAT(stuName, SUBSTRING(ming, FLOOR(1 + RAND() * m_len), 1));
    END IF;
    RETURN stuName;
END //
DELIMITER ;

-- 随机手机号码
DROP FUNCTION IF EXISTS randPhone;
DELIMITER //
CREATE FUNCTION randPhone()
RETURNS VARCHAR(11)
BEGIN
    DECLARE duang VARCHAR(255) DEFAULT '134,135,136,137,138,139,147,150,151,152,157,158,159,178,182,183,184,187,188,130,131,132,155,156,185,186,145,176,133,153,177,180,181,189';
    DECLARE phone VARCHAR(11);
    DECLARE pos INT;
    SET pos = CHAR_LENGTH(duang) / 4;
    SET pos = FLOOR(1 + RAND() * pos);
    SET phone = REPLACE(SUBSTRING(SUBSTRING_INDEX(duang, ',', pos), LENGTH(SUBSTRING_INDEX(duang, ',', pos -1)) + 1), ',', '');
    SET phone = CONCAT(phone, SUBSTRING(CONCAT(RAND()), 3, 8));
    RETURN phone;
END //
DELIMITER ;

-- 随机日期
DROP FUNCTION IF EXISTS randDateTime;
DELIMITER //
CREATE FUNCTION randDateTime(startTime DATETIME, endTime DATETIME)
RETURNS DATETIME
BEGIN
    DECLARE result DATETIME;
    DECLARE sunix BIGINT DEFAULT UNIX_TIMESTAMP(startTime);
    DECLARE unix BIGINT;
    SET unix = FLOOR(sunix + RAND() * (UNIX_TIMESTAMP(endTime) - sunix));
    SET result = FROM_UNIXTIME(unix);
    RETURN result;
END //
DELIMITER ;

-- 随机取单个字符
DROP FUNCTION IF EXISTS randChar;
DELIMITER //
CREATE FUNCTION randChar(str VARCHAR(255))
RETURNS VARCHAR(3)
BEGIN
    DECLARE result VARCHAR(3);
    DECLARE len INT DEFAULT CHAR_LENGTH(str);
    SET result = SUBSTRING(str,FLOOR(1 + RAND() * len),1);
    RETURN result;
END //
DELIMITER ;

-- 随机取N个字符
DROP FUNCTION IF EXISTS randChars;
DELIMITER //
CREATE FUNCTION randChars(str VARCHAR(255), count INT)
RETURNS VARCHAR(4000)
BEGIN
    DECLARE result VARCHAR(4000);
    DECLARE len INT DEFAULT CHAR_LENGTH(str);
		SET result = '';
		WHILE count > 0 DO
			SET result = CONCAT(result, SUBSTRING(str,FLOOR(1 + RAND() * len),1));
			SET count = count - 1;
		END WHILE;
    RETURN result;
END //
DELIMITER ;


-- 随机取N个简单字符
DROP FUNCTION IF EXISTS randSimpleChars;
DELIMITER //
CREATE FUNCTION randSimpleChars(count INT)
RETURNS VARCHAR(4000)
BEGIN
    DECLARE result VARCHAR(4000);
		SET result = randChars('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', count);
    RETURN result;
END //
DELIMITER ;

-- 随机取N个数字字符
DROP FUNCTION IF EXISTS randNumChars;
DELIMITER //
CREATE FUNCTION randNumChars(count INT)
RETURNS VARCHAR(4000)
BEGIN
    DECLARE result VARCHAR(4000);
		SET result = randChars('1234567890', count);
    RETURN result;
END //
DELIMITER ;

-- 随机取N个字母字符
DROP FUNCTION IF EXISTS randLetterChars;
DELIMITER //
CREATE FUNCTION randLetterChars(count INT)
RETURNS VARCHAR(4000)
BEGIN
    DECLARE result VARCHAR(4000);
		SET result = randChars('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', count);
    RETURN result;
END //
DELIMITER ;

-- 随机数字范围(包含上下限)
DROP FUNCTION IF EXISTS randValue;
DELIMITER //
CREATE FUNCTION randValue(min INT, max INT)
RETURNS INT
BEGIN
    DECLARE result INT;
		SET result = min + FLOOR(RAND() * (max - min + 1));
    RETURN result;
END //
DELIMITER;

-- 随机数字限制上限（包含上限）
DROP FUNCTION IF EXISTS randMax;
DELIMITER //
CREATE FUNCTION randMax(max INT)
RETURNS INT
BEGIN
    RETURN randValue(0, max);
END //
DELIMITER; ;

3、编写插入数据存储过程

-- 删除添加学生的存储过程
DROP PROCEDURE IF EXISTS `add_student`;

DELIMITER //
CREATE PROCEDURE `add_student`(ct INT)
BEGIN
    DECLARE i INT;
	DECLARE id INT;
	-- 查询获取当前的的学生总数
    SET i = (SELECT COUNT(1) FROM student);
	-- 查询获取当前最大的ID，当然ID如果是自动增长也可以不设置
	SET id = (SELECT IFNULL(MAX(studentNo),1) FROM student);
	-- 设置取消自动提交，避免每次都提交事务速度太慢，当然如果太多数量，可以分批次提交。
	SET AUTOCOMMIT=0;
    WHILE i < ct DO
		-- 随机插入数据
		INSERT INTO student (
				`studentNo`,
				`studentName`,
				`sex`,
				`gradeId`,
				`phone`,
				`bornDate`
			)  VALUES(id, randName(), randChar('男女'), randValue(1, 30), randPhone(), randDateTime('1989-01-01', '2001-11-11'));
			SET i = i + 1;
			set id = id + 1;
    END WHILE;
	SET AUTOCOMMIT=1;
	-- 提交数据
	COMMIT; 
END
//
DELIMITER ;

4、激动人心，来一波测试吧

-- 调用存储过程插入学员信息100w条，很耗时间，大家酌情添加
CALL add_student(1000000);

5、查查看，数据是不是很像真实数据