【MySQL】CSV 文件导入MySQL

时间：2021-12-29 06:45:10

此问题是前几天整理数据的时候碰到的，数据存在 CSV文件中（200多万记录），通过python 往数据库中导入太慢了，后来使用MySQL 中自带的命令 LOAD DATA INFILE, 30多秒就能够完成二三百万的数据量导入。

LOAD DATA INFILE 命令允许你读取文本文件然后非常快速的插入数据库。

导入文件之前，你需要准备以下的内容：

  • 创建相应数据的数据库表格。
  • CSV 文件中的数据需要和数据库表格在列数和数据类型保持一致。
  • 具有写入数据库的文件和插入权限的账户

假定我们拥有一个 discounts 的表格，结构如下：

【MySQL】CSV 文件导入MySQL

我们使用 [CREATE TABLE statement][1]命令创建 discounts 表格：

CREATE TABLE discounts (

    id INT NOT NULL AUTO_INCREMENT,

    title VARCHAR(255) NOT NULL,

    expired_date DATE NOT NULL,

    amount DECIMAL(10 , 2 ) NULL,

    PRIMARY KEY (id)

);

discounts.csv 文件的首行作为列名称，其他三行为数据。

【MySQL】CSV 文件导入MySQL

以下的命令使得 c:\tmp\discounts.csv 的文件存入 discounts表格。

LOAD DATA INFILE 'c:/tmp/discounts.csv'

INTO TABLE discounts

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

文件中的数据阈由逗号分隔开，代码中反映为 FIELD TERMINATED BY ',' ，而且数据由双引号包围，通过 ENCLOSED BY '" ‘ 标明

CSV 中的换行标记由 LINES TERMINATED BY '\n' 进行说明。

另外：文件中的首行是标题并需要存入数据库表格中，因此通过IGNORE 1 ROWS进行忽略。

导入数据转换格式

有时数据的格式并不满足数据库表格中目标列的格式。简单的情况下，你可以在LOAD DATA INFILE 中设置 set 选项 以转换数据

假定 discount_2.csv 文件中的过期时间列 是mm/dd/yyyy 的格式。

【MySQL】CSV 文件导入MySQL

当向 discounts 表格中导入数据时，我们必须通过 str_to_date() function 转换成MySQL日期的格式

LOAD DATA INFILE 'c:/tmp/discounts_2.csv'

INTO TABLE discounts

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(title,@expired_date,amount)

SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

客户端向远程MySQL数据库导入数据

使用命令 LOAD DATA INFILE从客户端（本地电脑）向远程MySQL数据库导入数据是完全可行的。

当你使用LOAD DATA INFILE 中的LOCAL 选项，客户端程序读取本地的文件，然后将其发送到MySQL server。文件将被上传到服务器端相应的临时目录内，比如 Windows 中 C:\windows\temp 或 linux中 /temp 。

此文件夹无法被MySQL配置或占用。

我们看看下面的例子：

LOAD DATA LOCAL INFILE  'c:/tmp/discounts.csv'

INTO TABLE discounts

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

唯一的差别在于命令中的 LOCAL 选项。如果你需要load 一个大的CSV文件，你会注意到LOCAL 选项，它会比平时慢一些，因为更多时间浪费在数据传输上面。

当你使用 LOCAL 选项时，连接 MySQL server的账户并不需要文件权限来导入数据。

使用 LOAD DATA LOCAL 导入本地文件到远程 MySQL服务端，需要注意一些安全问题，你必须意识到这些问题以规避潜在的安全风险。

此文章转载自：http://www.mysqltutorial.org/import-csv-file-mysql-table/

相关文章