生产库表数据导入开发库 重复数据处理
步骤:
- 导出生产库表 advertiser,在开发库里新建同样的表命名为 advertiser_copy,把导出的advertiser.sql文件表名全部修改成 advertiser_copy ,然后执行sql脚本导入到 advertiser_copy表中
- 执行sql 把 advertiser_copy 表中和 advertiser 表相同的数据删除掉,sql:
delete from advertiser_copy where advertiser_uid
in ( select advertiser_uid from advertiser );
- 把 advertiser_copy 表中的数据复制到 advertiser 表中,sql:
-- 全部字段复制
insert into advertiser select * from advertiser_copy;
-- 主键冲突时排除主键复制
insert into advertiser( advertiser_uid, advertiser_type )
select advertiser_uid, advertiser_type from advertiser_copy
快速造数据
-- 循环插入
drop PROCEDURE test_insert;
DELIMITER;;
CREATE PROCEDURE test_insert()
begin
declare num int;
set num=0;
while num < 10000 do
insert into yun_cashflow(userid,type,operatoruserid,withdrawdepositid,money) values(FLOOR(7 + (RAND() * 6))+FLOOR(22 + (RAND() * 9)),1,FLOOR(97 + (RAND()
* 6))+FLOOR(2 + (RAND() * 9)),FLOOR(17 + (RAND() * 6))+FLOOR(2 + (RAND() * 9)),FLOOR(5 + (RAND() * 6))+FLOOR(2 + (RAND() * 9)));
set num=num+1;
end while;
END;;
call test_insert();
参考文档:
如何快速在mysql中生成大量Mock数据