mysql 创建百万级别随机数据-存储过程

/ 技术相关 / 0 条评论 / 120浏览

mysql

mysql 创建百万级别随机数据-存储过程

创建测试表

# 用户表
CREATE TABLE `test_person` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `surname` VARCHAR(100) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `age` TINYINT(3) UNSIGNED NOT NULL,
  `sex` TINYINT(1) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

# 用户部门表
CREATE TABLE `test_department` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

# 用户住址表
CREATE TABLE `test_address` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `address` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

创建存储过程，用于批量添加测试数据

DROP procedure IF EXISTS generate;
CREATE PROCEDURE `generate`(IN num int)
BEGIN
    DECLARE surname_chars VARCHAR(512) DEFAULT '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄曲家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴鬱胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍卻璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逯盖益桓公';
    DECLARE compound_surname_chars VARCHAR(255) DEFAULT '万俟司马上官欧阳夏侯诸葛闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐钟离宇文长孙慕容鲜于闾丘司徒司空丌官司寇仉督子车颛孙端木巫马公西漆雕乐正壤驷公良拓跋夹谷宰父谷梁晋楚闫法汝鄢涂钦段干百里东郭南门呼延归海羊舌微生岳帅缑亢况郈有琴梁丘左丘东门西门商牟佘佴伯赏南宫墨哈谯笪年爱阳佟';
    DECLARE name_chars VARCHAR(10) DEFAULT '甲乙丙丁';
    DECLARE email_chars VARCHAR(65) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

    DECLARE id INT UNSIGNED;
    DECLARE surname VARCHAR(10) DEFAULT '';
    DECLARE name VARCHAR(25) DEFAULT '';

    DECLARE len INT UNSIGNED;

    DELETE FROM test_person;

    SET id=1;
    WHILE id <= num DO
        # 随机取单姓和复姓
        SET len = FLOOR(RAND()*2);
        SET surname = '';
        IF len = 0 THEN
            SET surname = SUBSTR(surname_chars,FLOOR(1 + RAND()*408),1);
        ELSE
            SET len = FLOOR(RAND()*151);
            IF len % 2 != 0 && len != 1 THEN
                SET len = len + 1;
            END IF;
            SET surname = SUBSTR(compound_surname_chars,len,2);
        END IF;

        # 随机取名字
        SET len = FLOOR(1 + RAND()*2);
        SET name = '';
        WHILE len > 0 DO
            SET name = CONCAT(name,SUBSTR(name_chars,FLOOR(1 + RAND()*4),1));
            SET len = len - 1;
        END WHILE;

        INSERT INTO test_person VALUES (id, surname, name, FLOOR(RAND()*100), FLOOR(RAND()*2));
        SET id = id + 1;
    END WHILE;
END

DROP procedure IF EXISTS genDepAdd;
CREATE PROCEDURE `genDepAdd`(IN num INT)
BEGIN
    DECLARE chars VARCHAR(100) DEFAULT '行政技术研发财务人事开发公关推广营销咨询客服运营测试';
    DECLARE chars2 VARCHAR(100) DEFAULT '北京上海青岛重庆成都安徽福建浙江杭州深圳温州内蒙古天津河北西安三期';

    DECLARE depart VARCHAR(10) DEFAULT '';
    DECLARE address VARCHAR(25) DEFAULT '';
    DECLARE id INT UNSIGNED;
    DECLARE len INT;

    DELETE FROM test_department;
    DELETE FROM test_address;

    SET id=1;
    WHILE id <= num DO
            SET len = FLOOR(2 + RAND()*2);
            SET depart = '';
            WHILE len > 0 DO
                    SET depart = CONCAT(depart,substring(chars,FLOOR(1 + RAND()*26),1));
                    SET len = len - 1;
                END WHILE;
            SET depart=CONCAT(depart,'部');
            SET len = FLOOR(6+RAND()*18);
            SET address = '';
            WHILE len > 0 DO
                    SET address = CONCAT(address,SUBSTR(chars2,FLOOR(1 + RAND()*33),1));
                    SET len = len - 1;
                END WHILE;

            INSERT INTO test_department VALUES (id,depart);
            INSERT INTO test_address VALUES (id,address);
            SET id = id + 1;
        END WHILE;
END

本站文章除注明转载/出处外，均为作者原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2023/03/09 11:27