MySQL如何导入大量数据？

有时我们会遇到需要将大量数据导入MySQL的需求，一般数据存储在csv或者txt中，数据由","分隔。这里提供两种方案供大家选择。

一、创建测试表

为了测试，我们先创建数据库和表，并创建一个用户。

create database loaddata;

use loaddata

CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sp_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务商名称',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='服务商类型';

CREATE USER 'loader'@'%' IDENTIFIED BY '1234Abcd*';
GRANT ALL PRIVILEGES ON loaddata.* TO 'loader'@'%';

二、方案一：load data infile

2.1说明

load data infile其实有两种形态，load data infile和load data local infile。

• load data infile：只能在MySQL服务器上执行，且需要文件存放到指定位置，可以使用show variables like ‘%secure%’; 查看

• load data local infile：可以在本地执行

2.2操作

2.2.1开启local_infile

首先我们需要在MySQL服务器开启local_infile

# 查看
show variables like 'local_infile';
# 开启
set global local_infile=on;

如果不开启的话，执行load data local infile会报ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides。

2.2.2执行上传

通过如下命令进行上传。load data local infile会将数据一块一块的上传，但对这块数据是作为整个事务进行上传的。

load data local infile "/Users/bytedance/Downloads/1.csv" into table loaddata.test fields terminated by',';

执行效果为：

查看表里的数据：

三、方案二：mysql shell

load data infile 不够灵活，另外可能因为事务导致性能出现问题，我们可以用mysql shell实现上传，底层使用的也是load data infile，但提供了更高的灵活性。

3.1安装

下载地址为：downloads.mysql.com/archives/sh…

我们可以从该地址选择合适的版本，mysql shell的版本需要和OS的版本匹配。

3.2使用

3.2.1连接mysql

对于mysql操作，我们可以通过如下命令连接到mysql

mysqlsh loader@127.0.0.1:3306

3.2.2更改编程语言

如上图所示，连接后的js表示现在是JavaScript，我们可以切换成python或者sql，命令为:

\sql
\py

3.3.3导入数据

导入数据我们使用python语言，命令如下：

util.import_table(  
        "/Users/bytedance/Downloads/1.csv",
    {
        "schema": "loaddata", 
        "table": "test",
        "dialect": "csv-unix",
        "skipRows": 0,
        "showProgress": True,
        "bytesPerChunk": "1M",
        "threads":2,
        "maxRate": "2M",
        "columns": ["id", "sp_name"]
        });

几个重要参数说明一下：

schema：数据库名

table：表名

showProgress：展示进度

bytesPerChunk：默认的 chunk 大小为 50M，我们可以调整 chunk 的大小，减少事务大小，如我们将 chunk 大小调整为 1M

threads：使用几个线程来导入数据，这次设置2个

maxRate：每个线程的速率为 M/s，这次设置为2M/s，这意味最高不会超过 2*2=4M/s。

其它参数大家可以参考这篇文章：dev.mysql.com/doc/dev/mys…

资料

1. MySQL 执行load data infile时同步原理及注意事项

2. 将CSV文件快速导入MySQL中

3. 13.2.5. LOAD DATA INFILE语法

4. 请教 关于 load data local infile 导入10G左右文本 到导入3G左右速度就特别慢

5. 对MySQL load data infile的一点想法

6. mysql load file 权限_Mysql 命令 load data infile 权限问题

7. 使用 LOAD DATA LOCAL INFILE，sysbench 导数速度提升30%

8. 技术分享 | MySQL Shell import_table 数据导入

9. mac mysql shell 安装_mysql for mac 安装和基本操作

10. mysql shell是什么意思_MYSQL SHELL 到底是个什么局 剑指 “大芒果”

11. shell下载地址

12. 教你使用MySQL Shell连接数据库的方法

13. dev.mysql.com/doc/dev/mys… 官网

14. dev.mysql.com/doc/dev/mys…

15. www.yisu.com/zixun/59844…

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：shidawuhen.github.io/

往期文章回顾：

1. 设计模式

2. 招聘

3. 思考

4. 存储

5. 算法系列

6. 读书笔记

7. 小工具

8. 架构

9. 网络

10. Go语言