数据库批量造数据

DELIMITER $$
USE user$$
DROP PROCEDURE IF EXISTS proc_batch_insert_user$$

CREATE PROCEDURE proc_batch_insert_user(count int)
BEGIN
        DECLARE id int(11);
        DECLARE user_name VARCHAR(50);
        DECLARE pass VARCHAR(50);
        DECLARE sex tinyint DEFAULT NULL;
        DECLARE email VARCHAR(100);
        DECLARE tel  bigint(11);
        DECLARE age int(11);
        DECLARE rand_str VARCHAR(255);
        DECLARE createtime DATETIME;
        DECLARE tel_body VARCHAR(40);

        DECLARE i INT DEFAULT 0;
        WHILE i < count DO
		    SET i=i+1;
            SET user_name = concat(substring('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚',floor(1 + 190 * rand()),1),
								substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',floor(1 + 400 * rand()),1),
								substring('明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',floor(1 + 400 * rand()),1));
		    SET pass = md5(ceiling(rand() * 1000000));
		    SET sex = ceiling(rand() * 10) % 2;
		    SET age = FLOOR(RAND()*100);
		    SET rand_str= SUBSTRING(MD5(RAND()),1,12);
            SET email = CONCAT(rand_str,'@gmail.com');
            SET tel_body = FLOOR(RAND()*10000000);
            SET tel = cast(concat('1',substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),
				right(left(trim(cast(rand() AS char(50))), 11), 9)) AS SIGNED INTEGER);
            SET createtime = NOW();
			
		    INSERT INTO user (username,pass,sex,email,tel, age, remark, createtime) VALUES (user_name,pass,sex,email,tel,age, rand_str, createtime);
		 
     
        END WHILE;
END$$
DELIMITER ;

call proc_batch_insert_user(100)

# -*- coding:utf-8 -*-
import time
import random
from pymysql import *
# import psycopg2 import *
# from psycopg2 import extras

MYSQL_HOST = 'tidb123.com'
MYSQL_PORT = 3306
MYSQL_USER = 'user'
MYSQL_PASS = 'pass'
MYSQL_DB = 'xxx'
MYSQL_CHARSET = 'utf8'


def timer(func):
    def decor(*args):
        start_time = time.time()
        func(*args)
        end_time = time.time()
        d_time = end_time - start_time
        print("运行时间: ", d_time)
    return decor


def Unicode():
    val = random.randint(0x4e00, 0x9fbf)
    return chr(val)


def GBK2312():
    head = random.randint(0xb0, 0xf7)
    body = random.randint(0xa1, 0xfe)
    val = f'{head:x}{body:x}'
    val = bytes.fromhex(val).decode('gb2312')
    return val


def getRandomStr(length):
    s = ''
    for i in range(length):
        s += Unicode()
    return s


def subList(rowList, sublength) -> list:
    length = len(rowList)
    sublist = []
    for i in range(0, length-sublength+1, sublength):
        sublist.append((rowList[i:i+sublength]))
    return sublist


@timer
def singleOperation(sql):
    conn = connect(host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USER,
                   password=MYSQL_PASS, database=MYSQL_DB, charset=MYSQL_CHARSET)
    cs = conn.cursor()
    cs.execute(sql)
    conn.commit()
    cs.close()
    conn.close()
    print('OK')


@timer
def batchInsert(sql, rowList):
    length = len(rowList)

    sublength = 50000
    if(length <= 50000):
        batchOperation(sql, rowList)
    else:
        sublist = subList(rowList, sublength)
        for i in sublist:
            batchOperation(sql, i)


def batchOperation(sql, rowList):
    conn = connect(host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USER,
                   password=MYSQL_PASS, database=MYSQL_DB, charset=MYSQL_CHARSET)
    cs = conn.cursor()
    cs.executemany(sql, rowList)
    conn.commit()
    cs.close()
    conn.close()
    print('OK %d' % len(rowList))


if __name__ == "__main__":
    # 删除数据
    singleSQL = '''DELETE FROM xxx WHERE ccc=1'''
    singleOperation(singleSQL)

    # 生成数据列表
    rowList = []
    round = 1
    roundid = 100000
    for id in range(1000000):
        text = getRandomStr(5)
        int1_9 = random.randint(1,9)
        time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        rowList.append((id, text, int1_9, 1, time, time))
    
    InsertBatchSql = '''INSERT INTO `xxx`.`blacklist` (`BLACKLIST_ID`, `CONTENT`, `BIZ_TYPE`, `ENABLED`, `CREATE_TIME`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s);'''
    batchInsert(InsertBatchSql, rowList)

-- LOAD DATA
--     [LOW_PRIORITY | CONCURRENT] [LOCAL]
--     INFILE 'file_name'
--     [REPLACE | IGNORE]
--     INTO TABLE tbl_name
--     [PARTITION (partition_name [, partition_name] ...)]
--     [CHARACTER SET charset_name]
--     [{FIELDS | COLUMNS}
--         [TERMINATED BY 'string']
--         [[OPTIONALLY] ENCLOSED BY 'char']
--         [ESCAPED BY 'char']
--     ]
--     [LINES
--         [STARTING BY 'string']
--         [TERMINATED BY 'string']
--     ]
--     [IGNORE number {LINES | ROWS}]
--     [(col_name_or_user_var
--         [, col_name_or_user_var] ...)]
--     [SET col_name={expr | DEFAULT},
--         [, col_name={expr | DEFAULT}] ...]

LOAD DATA LOCAL INFILE 'C:/mydata.txt'  -- 文件中，字段为空 用 \N 表示
REPLACE                                 -- 遇到主键冲突时，插入并更新（REPLACE）、忽略（IGNORE）
INTO TABLE user
CHARACTER SET utf8
FIELDS TERMINATED BY '\t'               -- 字段分隔符，默认为 \t
	OPTIONALLY ENCLOSED BY ''           -- 文本限定符，表示数据字段由什么字符包围，默认为空字符
	ESCAPED BY '\'                      -- 转义符，默认为 \
LINES TERMINATED BY '\n'                -- 记录分隔符，默认为 \n
(id, name, age, address, create_date)   -- 数据文本中字段与表中字段的顺序对应关系