MySQL造数据脚本-亲试

DELIMITER $$
CREATE DEFINER=`root`@`192.168.2.254` PROCEDURE `pjzzspdz_fpmx_initdata12101245`()
BEGIN


#其中走完一次循环可产生15条数据，单天数据量40w 因此日期增量值为40w/15=26667 ,总的数据量为 5000w，故外循环为5000w/15=3333333
    DECLARE my_count INT DEFAULT 1;
    DECLARE my_jqbh INT DEFAULT 49909889;
        DECLARE my_kpddm INT DEFAULT 1;
        
        WHILE (my_count<=3333333) DO  
            WHILE (my_kpddm<=5) DO
#分别插入三个jqbh及对应kpddm的数据，其中每循环26667 日期往前推一次            
INSERT INTO `pj_zzspdz_fpmx`(`fplxdm`, `fpdm`, `fphm`, `tspz`, `kprq`, `dqsj`, `jqbh`, `skm`, `ghdwmc`, `ghdwdm`, `ghdwdzdh`, `ghdwyhzh`, `xhdwmc`, `xhdwdm`, `xhdwdzdh`, `xhdwyhzh`, `swjgdm`, `swjgmc`, `zyspmc`, `spsm`, `zhsl`, `hjje`, `se`, `jshj`, `bz`, `skr`, `fhr`, `kpr`, `tzdh`, `kpjh`, `fpzt`, `qdbz`, `zkbz`, `hjzkje`, `hjzkse`, `ssyf`, `zfrq`, `zfdqsj`, `zfr`, `yfpdm`, `yfphm`, `bbh`, `jym`, `czydm`, `zfrdm`, `kpddm`, `zfyy`, `fpcbh`, `qmcs`, `qmbz`, `qmz`, `scbz`, `yqbz`, `fpqqlsh`, `id`, `bmbbbh`, `hsslbs`, `zsfs`, `sccgsj`, `tscbz`, `ceburl`, `DZDH`, `GFKHDH`, `GFKHYX`, `spbmjc`)
VALUES ('026', '032001600611', 99999999-nextval_fphm('item_fphm'), '', DATE_FORMAT(date_add(STR_TO_DATE('20180815',"%Y%m%d"),interval -round(my_count/26667) day),'%Y%m%d%H%i%s'), '2018-05-30 16:33:11',499098901397, '03362+7&gt;3/*1*3&lt;42*6506&lt;6&gt;2--&gt;5*01&gt;7114-7112&gt;6&lt;1+30774605*4-0-1+397-0*4794*7974&gt;-34/5-0674&lt;1+020117&lt;61961797/125/', '苏州和诚瑞达环保科技有限公司', '91320507MA1NC9616L', '', '', '昆山京东尚信贸易有限公司', '913205830880018839', '千灯镇圣祥东路25号2号 021-39915704', '中国建设银行股份有限公司昆山市千灯支行 32201986438051511848', '132058300', '昆山市国家税务局', '*纸制品*清风（APP）抽纸柔韧2层200抽软抽*20包纸巾（新老包装交替发货）（整箱售卖）', '', 0.160000, 41.290000, 6.600000, 47.890000, '订单号:76448125796', '京东商城', '', '京东商城', '', '0', '00', '0', '1', -7.850000, -1.260000, '5', '', NULL, '', '000000000000', '00000000', '03', '03285878343465279698', '', '', concat('a',mod(my_kpddm,6)), '', 10, '01b4014282000000', 'Y', 'MIIBQgYJKoZIhvcNAQcCoIIBMzCCAS8CAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHATGCAQ4wggEKAgEBMGgwXjELMAkGA1UEBhMCY24xFTATBgNVBAseDFb9W7Z6DlKhYDtcQDEdMBsGA1UEAx4Ueg5SoXU1W1CLwU5me6F0Bk4tX8MxGTAXBgNVBA0eEABjAGEAMQAwADAAMAAwADICBgEAAAClaTAJBgUrDgMCGgUAMA0GCSqGSIb3DQEBAQUABIGAg49HvSNjYkDn8PzeyFg+LTr+q6UiFxcaKX0lF8g6wli3jRhFkGHOywxNP7JHkpRLoPEKP1ASnKr7lvBuQd9ZVebLFkI4z/8c8LsMApX0gZijxvfa+dKWygQi6yhZag+cEFNT9xjshnJy5jTxvlqej2xsjJzRyeuzs7zQc8FDX/8=', '1', '1', concat(10000000,NEXTVAL_lsh('item_lsh')), NEXTVAL_lsh('item_lsh'), '30.0', '', '0', '', '', '', '', '', '', '1');

INSERT INTO `pj_zzspdz_fpmx`(`fplxdm`, `fpdm`, `fphm`, `tspz`, `kprq`, `dqsj`, `jqbh`, `skm`, `ghdwmc`, `ghdwdm`, `ghdwdzdh`, `ghdwyhzh`, `xhdwmc`, `xhdwdm`, `xhdwdzdh`, `xhdwyhzh`, `swjgdm`, `swjgmc`, `zyspmc`, `spsm`, `zhsl`, `hjje`, `se`, `jshj`, `bz`, `skr`, `fhr`, `kpr`, `tzdh`, `kpjh`, `fpzt`, `qdbz`, `zkbz`, `hjzkje`, `hjzkse`, `ssyf`, `zfrq`, `zfdqsj`, `zfr`, `yfpdm`, `yfphm`, `bbh`, `jym`, `czydm`, `zfrdm`, `kpddm`, `zfyy`, `fpcbh`, `qmcs`, `qmbz`, `qmz`, `scbz`, `yqbz`, `fpqqlsh`, `id`, `bmbbbh`, `hsslbs`, `zsfs`, `sccgsj`, `tscbz`, `ceburl`, `DZDH`, `GFKHDH`, `GFKHYX`, `spbmjc`)
VALUES ('026', '032001600611', 99999999-nextval_fphm('item_fphm'), '', DATE_FORMAT(date_add(STR_TO_DATE('20180815',"%Y%m%d"),interval -round(my_count/26667) day),'%Y%m%d%H%i%s'), '2018-05-30 16:33:11',499098901400, '03362+7&gt;3/*1*3&lt;42*6506&lt;6&gt;2--&gt;5*01&gt;7114-7112&gt;6&lt;1+30774605*4-0-1+397-0*4794*7974&gt;-34/5-0674&lt;1+020117&lt;61961797/125/', '苏州和诚瑞达环保科技有限公司', '91320507MA1NC9616L', '', '', '昆山京东尚信贸易有限公司', '913205830880018839', '千灯镇圣祥东路25号2号 021-39915704', '中国建设银行股份有限公司昆山市千灯支行 32201986438051511848', '132058300', '昆山市国家税务局', '*纸制品*清风（APP）抽纸柔韧2层200抽软抽*20包纸巾（新老包装交替发货）（整箱售卖）', '', 0.160000, 41.290000, 6.600000, 47.890000, '订单号:76448125796', '京东商城', '', '京东商城', '', '0', '00', '0', '1', -7.850000, -1.260000, '5', '', NULL, '', '000000000000', '00000000', '03', '03285878343465279698', '', '', concat('b',mod(my_kpddm,6)), '', 10, '01b4014282000000', 'Y', 'MIIBQgYJKoZIhvcNAQcCoIIBMzCCAS8CAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHATGCAQ4wggEKAgEBMGgwXjELMAkGA1UEBhMCY24xFTATBgNVBAseDFb9W7Z6DlKhYDtcQDEdMBsGA1UEAx4Ueg5SoXU1W1CLwU5me6F0Bk4tX8MxGTAXBgNVBA0eEABjAGEAMQAwADAAMAAwADICBgEAAAClaTAJBgUrDgMCGgUAMA0GCSqGSIb3DQEBAQUABIGAg49HvSNjYkDn8PzeyFg+LTr+q6UiFxcaKX0lF8g6wli3jRhFkGHOywxNP7JHkpRLoPEKP1ASnKr7lvBuQd9ZVebLFkI4z/8c8LsMApX0gZijxvfa+dKWygQi6yhZag+cEFNT9xjshnJy5jTxvlqej2xsjJzRyeuzs7zQc8FDX/8=', '1', '1', concat(10000000,NEXTVAL_lsh('item_lsh')), NEXTVAL_lsh('item_lsh'), '30.0', '', '0', '', '', '', '', '', '', '1');

INSERT INTO `pj_zzspdz_fpmx`(`fplxdm`, `fpdm`, `fphm`, `tspz`, `kprq`, `dqsj`, `jqbh`, `skm`, `ghdwmc`, `ghdwdm`, `ghdwdzdh`, `ghdwyhzh`, `xhdwmc`, `xhdwdm`, `xhdwdzdh`, `xhdwyhzh`, `swjgdm`, `swjgmc`, `zyspmc`, `spsm`, `zhsl`, `hjje`, `se`, `jshj`, `bz`, `skr`, `fhr`, `kpr`, `tzdh`, `kpjh`, `fpzt`, `qdbz`, `zkbz`, `hjzkje`, `hjzkse`, `ssyf`, `zfrq`, `zfdqsj`, `zfr`, `yfpdm`, `yfphm`, `bbh`, `jym`, `czydm`, `zfrdm`, `kpddm`, `zfyy`, `fpcbh`, `qmcs`, `qmbz`, `qmz`, `scbz`, `yqbz`, `fpqqlsh`, `id`, `bmbbbh`, `hsslbs`, `zsfs`, `sccgsj`, `tscbz`, `ceburl`, `DZDH`, `GFKHDH`, `GFKHYX`, `spbmjc`)
VALUES ('026', '032001600611', 99999999-nextval_fphm('item_fphm'), '', DATE_FORMAT(date_add(STR_TO_DATE('20180815',"%Y%m%d"),interval -round(my_count/26667) day),'%Y%m%d%H%i%s'), '2018-05-30 16:33:11',499098901418, '03362+7&gt;3/*1*3&lt;42*6506&lt;6&gt;2--&gt;5*01&gt;7114-7112&gt;6&lt;1+30774605*4-0-1+397-0*4794*7974&gt;-34/5-0674&lt;1+020117&lt;61961797/125/', '苏州和诚瑞达环保科技有限公司', '91320507MA1NC9616L', '', '', '昆山京东尚信贸易有限公司', '913205830880018839', '千灯镇圣祥东路25号2号 021-39915704', '中国建设银行股份有限公司昆山市千灯支行 32201986438051511848', '132058300', '昆山市国家税务局', '*纸制品*清风（APP）抽纸柔韧2层200抽软抽*20包纸巾（新老包装交替发货）（整箱售卖）', '', 0.160000, 41.290000, 6.600000, 47.890000, '订单号:76448125796', '京东商城', '', '京东商城', '', '0', '00', '0', '1', -7.850000, -1.260000, '5', '', NULL, '', '000000000000', '00000000', '03', '03285878343465279698', '', '', concat('c',mod(my_kpddm,6)), '', 10, '01b4014282000000', 'Y', 'MIIBQgYJKoZIhvcNAQcCoIIBMzCCAS8CAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHATGCAQ4wggEKAgEBMGgwXjELMAkGA1UEBhMCY24xFTATBgNVBAseDFb9W7Z6DlKhYDtcQDEdMBsGA1UEAx4Ueg5SoXU1W1CLwU5me6F0Bk4tX8MxGTAXBgNVBA0eEABjAGEAMQAwADAAMAAwADICBgEAAAClaTAJBgUrDgMCGgUAMA0GCSqGSIb3DQEBAQUABIGAg49HvSNjYkDn8PzeyFg+LTr+q6UiFxcaKX0lF8g6wli3jRhFkGHOywxNP7JHkpRLoPEKP1ASnKr7lvBuQd9ZVebLFkI4z/8c8LsMApX0gZijxvfa+dKWygQi6yhZag+cEFNT9xjshnJy5jTxvlqej2xsjJzRyeuzs7zQc8FDX/8=', '1', '1', concat(10000000,NEXTVAL_lsh('item_lsh')), NEXTVAL_lsh('item_lsh'), '30.0', '', '0', '', '', '', '', '', '', '1');

         set my_kpddm=my_kpddm+1;
                end while;
      
 set my_kpddm = 1;
 set my_count=my_count+1;    

end while;
set my_count=1;
            
commit;
end$$
delimiter;