我有一份数据量达到1,0015,0806条的csv文件,现在需要导入Mysql中。 在我电脑中安装的版本是MYSQL8.0.12,常见的管理工具有:
已知的有三种办法: 1.用图形管理工具Navicat Premium通过导入向导8个步骤导入数据;
2.使用MYSQL Workbench 中的table data import导入数据;
3.使用load data infile语句导入;
最后结果:
方法1,2适合数据量较小的数据导入,在我导入数据时,由于数据太大,总是导入不成功。耗时巨大,最多的时候只能导入1000条数据,所以,不建议采用。 曾经我有想过把文件拆开,因为有1亿条数据,我想着拆成10个文件每个文件有1000万条数据,但是网上查了要么说需要下载拆分工具,要么用python,这两个我都不熟,所以果断舍弃,使用方法3导入。 数据导入前有准备工作要做,为将导入的文件找存放位置,建立相应数据库例如test,在test小创建表格并设置对应的列属性,接下来用Notepad++打开csv文件,查看文件编码格式,务必保证导入数据的编码格式是ANSI编码格式,若文件编码不正确改变编码格式
打开MYSQL命令窗口查询已有数据库并使用相应数据裤
接下来使用load data 命令导入数据了,使用命令之前有必要先了解load data 语法,根据官网参考手册,语法如下:
LOW_PRIORITY修饰符
功能:改变语句调度的优先级如果把执行检索(SELECT)的客户端称为"读取者",把执行修改操作(DELETE、INSERT、REPLACE或UPDATE)的客户端称 为"写入者"。MYSQL 的默认调度策略是写入操作优先于读取操作。 LOW_PRIORITY关键字影响DELETE、INSERT、LOAD DATA、REPLACE和UPDATE语句(写入操作)的执行调度。通常情况下,某张数据表正在被读取的时候,如果有写入操作到达,那么写入者一直等待读取者完成操 作(查询开始之后就不能中断,因此允许读取者完成操作)。如果写入者正在等待的时候,另一个读取操作到达了,该读取操作也会被阻塞,因为默 认的调度策略是写入者优先于读取者。当第一个读取者完成操作的时候,写入者开始操作,并且直到该写入者完成操作,第二个读取者才开始操作。 如果写入操作是一个LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作。在这种情况下,如果写入者在等待的时候,第 二个读取者到达了,那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候,才允许写入者开始操作。理论上,这种调度修改暗示着,可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。如果前面的读取操作在进行的过程中一直有其它的读取操作到达,那么新的请求都会插入到 LOW_PRIORITY写入操作之前。
CONCURRENT修饰符使用load data ,如果使用满足并发插入条件CONCURRENT的MYISAM表指定(即,中间不包含空闲块),则其他会话可以在执行load data时从表中检索数据。即使没有其他会话同时使用该表,使用该CONCURRENT选项load data也会影响某个位的性能。
输入文件名:文件名必须以文字字符串形式给出。在Windows上,将路径名中的反斜杠指定为正斜杠或加倍反斜杠(重点)
分隔符:fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项: terminated by:分隔符,意思是以什么字符作为分隔符
enclosed by:字段括起字符
escaped by:转义字符 lines terminated by:描述字段的分隔符,默认情况下是tab字符(\t) 当我们要导入到MySQL表中的文本文件具有以逗号','分隔的值或者可能与冒号‘:’,分号 ';' 之间的任何其他分隔符分隔时,应使用'FIELDS TERMINATED BY'选项。
最终语法如下: LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
我在刚开始导入的文件路径格式如下:
load data infile 'C:\ProgramData\MySQL\MySQL Server8.0\Uploads\XXX.csv'
遇到错误:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
通过查询语句 show variables like '%secure%'; 查到--secure-file-priv--位置将要导入的文件放在--secure-file-priv--相应位置后依然出现1290错误,将网上的各种办法逐一试过后发现是文件路径书写错误(汗。。。),最终通过如下语句成功导入数据:
历时将近1个小时,虽然时间也不短,但是相较图形界面引导导入而言导入速度更快。 最后再说一点,我在审核导入语句时曾用过 lines terminated by '\r\n'; 结果只能导入一行,那肯定是换行的问题,网友给的解释是:如果已经在Windows系统中生成了文本文件,可能必须使用LINES TERMINATED BY ‘\r\n’来正确地读取文件,因为Windows程序通常使用两个字符作为一个行终止符。(我的理解就是如果导入的是txt文件,那就用lines terminated by '\r\n',否则就用lines terminated by '\n')
最后,希望以后我们都能高效的导入数据量大的数据文件。