MySQL快速插入一億測試數(shù)據(jù)

更新時間：2021年06月22日 11:10:16 作者：tiankong_12345

本文主要介紹了MySQL快速插入一億測試數(shù)據(jù)，有時候測試需要大量的數(shù)據(jù)，需要的朋友們下面隨著小編來一起學習學習吧

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 '創(chuàng)建時間',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`c_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.2 創(chuàng)建臨時表

CREATE TABLE `tmp_table` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、生成數(shù)據(jù)

2.1 用 python生成【一億】記錄的數(shù)據(jù)文件（這個確實稍微花點時間）

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

2.2 將生成的文件導入到臨時表tmp_table中

找到對應的數(shù)據(jù)庫

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)

執(zhí)行導入命令

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>

導入數(shù)據(jù)時有可能會報錯，原因是mysql默認沒有開secure_file_priv（這個參數(shù)用來限制數(shù)據(jù)導入和導出操作的效果，例如執(zhí)行LOAD DATA、SELECT … INTO OUTFILE語句和LOAD_FILE()函數(shù)。這些操作需要用戶具有FILE權(quán)限。）

解決辦法：在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的導入導出只能發(fā)生在/var/lib/mysql-files/目錄下
secure_file_priv=' '     不對mysqld的導入導出做限制

注意：配置要添加到 [mysqld] 節(jié)點下，至于路徑加不加引號，你可以試試：

重啟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>

億級數(shù)據(jù)，233.42s，看一下別人的數(shù)據(jù)，差不多就是這個。

3、以臨時表為基礎數(shù)據(jù)，插入數(shù)據(jù)到t_user中

一億數(shù)據(jù)需要：快半個小時了。。。（或許直接在命令行下運行更快點...）

更新創(chuàng)建時間字段讓插入的數(shù)據(jù)的創(chuàng)建時間更加隨機：

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

到此，一億數(shù)據(jù)插入結(jié)束。