系统重构,做数据导出开发,结束以后由于数据量小进行测试遭嘲讽,采用存储过程增加数据库大量数据
先创建两个随机数字随机字符串的函数
CREATE DEFINER=`root`@`localhost` FUNCTION `RAND_number`() RETURNS int(10)
BEGIN
DECLARE i INT DEFAULT 0;
SET i= FLOOR(2+RAND()*10);
RETURN i;
END
CREATE DEFINER=`root`@`localhost` FUNCTION `RAND_str`(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE char_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i< n DO
SET return_str =CONCAT(return_str,SUBSTRING(char_str,FLOOR(1+RAND()*52),1));
SET i= i+1;
END WHILE;
RETURN return_str;
END
数据库表随机创建一个,主键id自增就可以
创建一个存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_aa`(IN START INT(5),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT #重复
SET i = i + 1;
INSERT INTO aa(name,age,sort_no) VALUES (RAND_str(3),RAND_number(),RAND_number());
UNTIL i = max_num
END REPEAT;
COMMIT;
END
然后在Navicat执行
08-08:补充一下
date类型以及datetime类型数据的随机生成函数(都差不多啦)
CREATE DEFINER=`root`@`localhost` FUNCTION `func_RandomDate`(
sd DATE,
ed DATE) RETURNS date
BEGIN
DECLARE sub INT DEFAULT 0;
DECLARE ret DATE;
SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
RETURN DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)))-1)) SECOND);
END;
CREATE DEFINER=`root`@`localhost` FUNCTION `func_RandomDateTime`(
sd DATETIME,
ed DATETIME) RETURNS datetime
BEGIN
DECLARE sub INT DEFAULT 0;
DECLARE ret DATETIME;
SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
RETURN DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)))-1)) SECOND);
END;
确定执行即可,数据量10w的话差不多3s左右 ;要恶补MySQL函数啊