MySQL实战：使用存储过程造测试数据_mysql 存储过程生成测试数据-优快云博客

本文链接：https://blog.youkuaiyun.com/zzti_erlie/article/details/123676218

这篇博客介绍了如何使用MySQL的存储过程来创建测试数据。首先定义了一个生成随机字符串的函数`rand_string`，然后创建了一个`create_data`的存储过程用于插入指定数量的数据到`single_table`表中。此外，还展示了如何复制表结构和数据，以及如何更新`staff`表以分配城市。最后，创建了`person`表并填充了数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用存储过程造测试数据

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

随机生成长度为len的字符串

delimiter //
create function rand_string(len integer) 
returns text no sql
begin
  declare counter int default 1;
  declare str text default '';
 
  if len < 1 then
    return null;
  end if;
 
  while counter <= len do
    set str = concat(str, char(floor(rand() * 94) + 33));
    set counter = counter + 1;
  end while;
 
  return str;
end
//
delimiter ;

DROP PROCEDURE IF EXISTS create_data;
-- 定义存储过程
DELIMITER //
CREATE PROCEDURE create_data(IN loop_times INT)
BEGIN
    DECLARE var INT;
    DECLARE str TEXT default '';
    SET var = 1;
    WHILE var <= loop_times DO
    	SET str = substring(MD5(RAND()), 1, 10);
    	INSERT INTO single_table (`key1`,`key2`,`key3`, `key_part1`,`key_part2`,`key_part3`,`common_field`)
    	VALUES (str, var, str, str, str, str, str);
    	SET var = var + 1;
    END WHILE;
END //
DELIMITER ;

call create_data(10000);

create table t1 like single_table;
insert into t1 (select * from single_table where id <= 10000);

个人用

drop table single_table;
drop table t1;
drop table t2;

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;


DROP PROCEDURE IF EXISTS create_data;
-- 定义存储过程
DELIMITER //
CREATE PROCEDURE create_data(IN loop_times INT)
BEGIN
    DECLARE var INT;
    DECLARE str TEXT default '';
    SET var = 1;
    WHILE var <= loop_times DO
    	SET str = substring(MD5(RAND()), 1, 10);
    	INSERT INTO single_table (`key1`,`key2`,`key3`, `key_part1`,`key_part2`,`key_part3`,`common_field`)
    	VALUES (str, var, str, str, str, str, str);
    	SET var = var + 1;
    END WHILE;
END //
DELIMITER ;


create table t1 like single_table;
create table t2 like single_table;

call create_data(100000);

insert into t1 (select * from single_table where id <= 100000);
insert into t2 (select * from single_table where id <= 100000);

造数据

CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into city (`name`) values ('北京'), ('天津'), ('上海'), ('重庆'), ('西安'), ('深圳'), ('杭州'), ('澳门'), ('太原'), ('郑州'), ('苏州');

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

DROP PROCEDURE IF EXISTS create_staff;
DELIMITER //
CREATE PROCEDURE create_staff(IN loop_times INT)
BEGIN
    DECLARE var INT;
    SET var = 1;
    WHILE var <= loop_times DO
    	INSERT INTO staff (`name`,`age`,`city`)
    	VALUES (var, var, var);
    	SET var = var + 1;
    END WHILE;
END //
DELIMITER ;


call create_staff(1000);

update staff SET city = '北京' where id  <= 50;
update staff SET city = '北京' where id  > 50;
update staff SET city = '天津' where id  > 100;
update staff SET city = '上海' where id  > 200;
update staff SET city = '重庆' where id  > 350;
update staff SET city = '西安' where id  > 450;
update staff SET city = '深圳' where id  > 470;
update staff SET city = '杭州' where id  > 500;
update staff SET city = '澳门' where id  > 600;
update staff SET city = '太原' where id  > 800;
update staff SET city = '郑州' where id  > 880;

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS create_person;
DELIMITER //
CREATE PROCEDURE create_person(IN loop_times INT)
BEGIN
    DECLARE var INT;
    DECLARE str TEXT default '';
    SET var = 1;
    WHILE var <= loop_times DO
    	SET str = substring(MD5(RAND()), 1, 10);
    	INSERT INTO person (`id`,`city`,`name`, `age`)
    	VALUES (var, str, str, var);
    	SET var = var + 1;
    END WHILE;
END //
DELIMITER ;