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