新建一个用户表
drop table if exists tb_user; create table tb_user( id int primary key auto_increment comment '主键', name varchar(20) comment '姓名', phone varchar(11) comment '手机号', profession varchar(50) comment '专业', age int comment '年龄', status int comment '状态', email varchar(50) comment '邮箱' );
利用Python脚本生成10万条测试数据
# Python >= 3.7.0 # 安装依赖 pip install faker records mysqlclient from faker import Faker import records # 简体中文：zh_CN faker = Faker(locale="zh_CN") # 指定随机种子，确保每次生成的数据都是一致的 faker.seed(1) # 生成数据插入 insert sql def get_insert_sql(table_name, fields): keys = ', '.join([f'`{key}`'.format(key) for key in fields]) values = ', '.join([f':{key}'.format(key) for key in fields]) return f'INSERT INTO `{table_name}` ({keys}) VALUES ({values})' # 获取数据 def get_row(): return { 'name': faker.name(), 'phone': faker.phone_number(), 'email': faker.email(), 'age': faker.random_int(20, 30), 'status': faker.random_int(0, 1), 'profession': faker.job(), } def main(): db = records.Database('mysql://root:123456@localhost/data?charset=utf8') # 10 * 10000 = 10万条数据 for i in range(10): data = [get_row() for _ in range(1000)] sql = get_insert_sql('tb_user', data[0].keys()) db.bulk_query(sql, data) if __name__ == '__main__': main()
查看生成的测试数据
mysql> select count(*) from tb_user; +----------+ | count(*) | +----------+ | 100000 | +----------+ select * from tb_user limit 10; +----+-----------+-------------+---------------------------------+------+--------+---------------------+ | id | name | phone | profession | age | status | email | +----+-----------+-------------+---------------------------------+------+--------+---------------------+ | 1 | 费阳 | 13777763170 | 法务经理 | 27 | 1 | wyao@gmail.com | | 2 | 祁海燕 | 13400806360 | 日式厨师 | 23 | 0 | jwan@jin.cn | | 3 | 姬秀英 | 18281241586 | 食品/饮料研发 | 29 | 0 | li97@wang.cn | | 4 | 官桂芳 | 15625851781 | 前台接待/总机/接待生 | 20 | 1 | fpeng@chang.cn | | 5 | 应秀珍 | 13030388368 | 酒店前台 | 20 | 1 | qiang48@hotmail.com | | 6 | 亢婷 | 18207598386 | 药品市场推广主管/专员 | 28 | 1 | ping50@hotmail.com | | 7 | 仰俊 | 13192184011 | 机场代表 | 24 | 0 | wcai@liang.net | | 8 | 匡洁 | 13622482447 | 汽车电工 | 24 | 1 | htang@gmail.com | | 9 | 程建华 | 13748396030 | 市场通路经理/主管 | 28 | 1 | fangguo@yahoo.com | | 10 | 岳荣 | 15080695604 | 培训督导 | 24 | 1 | fanglong@ding.com | +----+-----------+-------------+---------------------------------+------+--------+---------------------+ 10 rows in set (0.00 sec)
