Home  >  Article  >  Database  >  mysql 动态生成测试数据

mysql 动态生成测试数据

WBOY
WBOYOriginal
2016-06-07 18:01:301074browse

mysql 动态生成测试数据的语句,方便测试数据。

一、问题
要生成两类数据:
A类:两位的 01 02 03 。。。09 10 11。。。19 20 21 。。。98 99
另一类B类:三位的 100 101 102 。。。110 111 112。。。998 999
二、解决办法
1、建表
代码如下:
CREATE TABLE `test`.`ta` (
`a` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建存储过程
代码如下:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`proc_tp` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_tp`(in prex int,in max int)
begin
declare i INT DEFAULT 0;
declare s varchar(500);
WHILE (iselect concat(prex,i) into s;
insert into ta (a) values (s);
set i=i+1;
if(i=10 and prexset prex=prex+1;
set i=0;
end if;
END WHILE ;
end $$
DELIMITER ;

3、分别调用执行存储过程
CALL proc_tp(0,10) 创建A类数据
CALL proc_tp(10,100) 创建B类数据
4、查询结果
SELECT * FROM ta t order by cast(a as signed) asc;
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn