MySQL快速插入一亿测试数据

1、建表

1.1 建立测试表 t_user

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 创建临时表

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

2、生成数据

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

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

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

找到对应的数据库

type 'help;' or '\h' for help. type '\c' to clear the current input statement.
 
mysql> use test;
database changed
mysql> show tables;
+----------------+
| tables_in_test |
+----------------+
| student        |
| t_user         |
| tmp_table      |
+----------------+
3 rows in set (0.00 sec)

执行导入命令

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%’; 先看一下配置：

mysql> show variables like '%secure%';
+--------------------------+-------+
| variable_name            | value |
+--------------------------+-------+
| require_secure_transport | off   |
| secure_auth              | on    |
| secure_file_priv         | null  |
+--------------------------+-------+
3 rows in set, 1 warning (0.00 sec)

说明：

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

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

重启mysql，先查看配置：

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)

再重新导入：

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中

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

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

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

到此，一亿数据插入结束。

4、参考

mysql如何快速的创建千万级测试数据

the mysql server is running with the –secure-file-priv option

到此这篇关于mysql快速插入一亿测试数据的文章就介绍到这了,更多相关mysql 插入一亿数据内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！