MySQL快速插入一亿测试数据

zhaorong · 发表于 2021-6-23 17:34:11

本文主要介绍了MySQL快速插入一亿测试数据有时候测试需要大量的数据需要的
朋友们下面随着小编来一起学习学习吧！

目录

1、建表
1.1 建立测试表 t_user
1.2 创建临时表
2、生成数据
2.1 用 python生成【一亿】记录的数据文件（这个确实稍微花点时间）
2.2 将生成的文件导入到临时表tmp_table中
3、以临时表为基础数据插入数据到t_user中
4、参考

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

复制代码

到此一亿数据插入结束。

		自动登录	找回密码
密码			注册

[编程和数据库] MySQL快速插入一亿测试数据