MySQL快速插入一亿测试数据

吾爱主题阅读：179 2024-04-02 08:03:28 评论：0

1、建表

1.1 建立测试表 t_user

1 2 3 4 5 6 7 8 9 10 create table `t_user` ( `id` int (11) not null auto_increment, `c_user_id` varchar (36) not null default '' comment '用户id' , `c_name` varchar (22) not null default '' comment '用户名' , `c_province_id` int (11) not null comment '省份id' , `c_city_id` int (11) not null comment '城市id' , `create_time` datetime not null comment '创建时间' , primary key (`id`), key `idx_user_id` (`c_user_id`) ) engine=innodb default charset=utf8mb4;

1.2 创建临时表

1 2 3 4 create table `tmp_table` ( `id` int (11) not null , primary key (`id`) ) engine=innodb default charset=utf8;

2、生成数据

2.1 用 python生成【一亿】记录的数据文件（这个确实稍微花点时间）

1	`python -c` `"for i in range(1, 1+100000000): print(i)"` `> base.txt`

2.2 将生成的文件导入到临时表tmp_table中

找到对应的数据库

执行导入命令

1 2 3 4 mysql> load data infile 'e:/base.txt' replace into table tmp_table; error 1290 (hy000): the mysql server is running with the --secure-file-priv option so it cannot execute this statement mysql>

导入数据时有可能会报错，原因是mysql默认没有开secure_file_priv（这个参数用来限制数据导入和导出操作的效果，例如执行load data、select … into outfile语句和load_file()函数。这些操作需要用户具有file权限。）

解决办法：在mysql的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = 文件所在的路径 , 然后重启mysql 解决。添加自己文件放置的路径即可。

可以用 show variables like '%secure%'; 先看一下配置：

说明：

1 2 3 secure_file_prive= null 限制mysqld 不允许导入导出 secure_file_priv=/var/lib/mysql-files/ 限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下 secure_file_priv= ' ' 不对mysqld的导入导出做限制

注意：配置要添加到 [mysqld] 节点下，至于路径加不加引号，你可以试试：

重启mysql，先查看配置：

1 2 3 4 5 6 7 8 9 10 11 mysql> use test; database changed mysql> show variables like '%secure%' ; + --------------------------+-------+ | variable_name | value | + --------------------------+-------+ | require_secure_transport | off | | secure_auth | on | | secure_file_priv | e:\ | + --------------------------+-------+ 3 rows in set , 1 warning (0.00 sec)

再重新导入：

1 2 3 4 5 mysql> load data infile 'e:/base.txt' replace into table tmp_table; query ok, 100000000 rows affected (3 min 53.42 sec) records: 100000000 deleted: 0 skipped: 0 warnings: 0 mysql>

亿级数据，233.42s，看一下别人的数据，差不多就是这个。

3、以临时表为基础数据，插入数据到t_user中

一亿数据需要：快半个小时了。。。（或许直接在命令行下运行更快点...）

更新创建时间字段让插入的数据的创建时间更加随机：

1 2 3 4 5 6 7 mysql> update t_user set create_time=date_add(create_time, interval floor(1 + (rand() * 7)) year ); query ok, 100000000 rows affected (7 min 24.17 sec) rows matched: 100000000 changed: 100000000 warnings: 0 mysql> update t_user set create_time=date_add(create_time, interval floor(1 + (rand() * 7)) year ); query ok, 100000000 rows affected (8 min 2.49 sec) rows matched: 100000000 changed: 100000000 warnings: 0