0

MySQL如何导入大量数据?

 1 year ago
source link: https://shidawuhen.github.io/2022/09/22/MySQL%E5%A6%82%E4%BD%95%E5%AF%BC%E5%85%A5%E5%A4%A7%E9%87%8F%E6%95%B0%E6%8D%AE%EF%BC%9F/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

有时我们会遇到需要将大量数据导入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',';

执行效果为:

image-20220921205050181

查看表里的数据:

image-20220921205103855

三、方案二: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 [email protected]:3306
image-20220921235639002

3.2.2更改编程语言

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

\sql
\py
image-20220921235807115

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。

image-20220922000428694
image-20220922000443026

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


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK