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安装

下载地址为：https://downloads.mysql.com/archives/shell/
我们可以从该地址选择合适的版本，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。





其它参数大家可以参考这篇文章：https://dev.mysql.com/doc/dev/mysqlsh-api-python/8.0/group__util.html#ga14b9db6de5a13f78a1a41a537ed07887
资料

• MySQL 执行load data infile时同步原理及注意事项
  
• 将CSV文件快速导入MySQL中
  
• 13.2.5. LOAD DATA INFILE语法
  
• 请教 关于 load data local infile 导入10G左右文本 到导入3G左右速度就特别慢
  
• 对MySQL load data infile的一点想法
  
• mysql load file 权限_Mysql 命令 load data infile 权限问题
  
• 使用 LOAD DATA LOCAL INFILE，sysbench 导数速度提升30%
  
• 技术分享 | MySQL Shell import_table 数据导入
  
• mac mysql shell 安装_mysql for mac 安装和基本操作
  
• mysql shell是什么意思_MYSQL SHELL 到底是个什么局 剑指 “大芒果”
  
• shell下载地址
  
• 教你使用MySQL Shell连接数据库的方法
  
• https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/group__util.html#gaa1ee4527bdb71fa736f6cbe168064079 官网
  
• https://dev.mysql.com/doc/dev/mysqlsh-api-python/8.0/group__util.html
  
• https://www.yisu.com/zixun/598441.html
  

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）
我的个人博客为：https://shidawuhen.github.io/
往期文章回顾：

• 设计模式
  
• 招聘
  
• 思考
  
• 存储
  
• 算法系列
  
• 读书笔记
  
• 小工具
  
• 架构
  
• 网络
  
• Go语言
  

丨怕瓦落地丨

为了三千积分！