MySQL匯入百萬資料實踐

王一洋發表於2018-11-03
MySql

日常開發過程中，會遇到資料庫轉移，分庫等操作，資料少的時候，可以借用navicate來操作，對於大批量資料來說，工具就變得笨拙，百萬級資料可能匯入需要花上一整天，這是不能接受的。

mysql提供了一個專門針對大資料匯入的命令：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

但是要使用這個命令，還得踩過一路的坑。

1、安全配置。mysql預設不允許檔案匯入匯出，通過下面命令可以看到 secure_file_priv=null。

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)

2、配置無門，因為mysql安裝目錄下就沒有可以配置的地方，找不到my.ini。

需要手工在mysql安裝目錄的根目錄下建立my.ini（本人測試機器為windows，路徑為：

C:\Program Files\MySQL\MySQL Server 5.7）：

[mysqld]
secure_file_priv=
character-set-server=utf8
#繫結IPv4和3306埠
bind-address = 0.0.0.0
port = 3306
#sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=1000M
innodb_log_file_size=50M
# 允許最大連線數
max_connections=200
# skip_grant_tables
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysql_safe]
default-character-set=utf8
[client]
port = 3306

3、重啟mysql讓配置生效。

net stop mysql  #關閉mysql
net start mysql  #啟動mysql

4、再驗證，可以看到secure_file_priv = null，表示可以進行檔案匯入匯出了

mysql>  show variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set, 1 warning (0.00 sec)

5、再執行，繼續報錯，找不到檔案，根據提示，檔案需要放到指定目錄下（資料庫名稱為：learn）：

C:\Program Files\MySQL\MySQL Server 5.7\data\learn\test_db.sql

6、再執行，繼續報錯：

Incorrect integer value: '' for column 'id' at row 1

列ID為integer，但是資料為‘1’：

INSERT INTO `system_user` VALUES ('1', '39352f81-165e-4405-9715-75fcdf7f7068', '裴鷗鵬', 'bANyhSmVtgxYmOcF', '15175232269', '北京市,密雲縣', '0', '2', '2015-01-01 00:00:00');

一般我們是認為應該沒錯誤的。後來查了下MYSQL的資料。發現5.5以上的版本驗證較為嚴格。

7、修改my.ini:

預設為

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，

將其修改為

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"，重啟mysql後即可

8、再試，330萬資料，匯入只花了50.20秒，效率非常高

mysql> LOAD DATA INFILE "test_db.sql" INTO TABLE system_user FIELDS TERMINATED BY ',';
Query OK, 3323001 rows affected, 65535 warnings (50.20 sec)
Records: 3323001  Deleted: 0  Skipped: 0  Warnings: 16615005

參考文件：

https://www.cnblogs.com/lv_yantao/archive/2011/11/13/2246950.html

https://www.2cto.com/database/201803/725288.html

https://www.jb51.net/article/125905.htm

https://www.cnblogs.com/waynechou/p/7794939.html

https://segmentfault.com/a/1190000009333563

相關文章