新建一个用户表
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)
