使用 LOAD DATA INFILE 从 CSV 文件导入 MySQL 中的数据

您可以使用 LOAD DATA INFILE 命令将 CSV 文件导入表中。

检查链接 MySQL - LOAD DATA INFILE。

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

对于 MySQL 8.0 用户：

使用 LOCAL 关键字存在安全风险，从 MySQL 8.0 开始，LOCAL 功能默认设置为 False。您可能会看到以下错误：

错误 1148：此 MySQL 版本不允许使用的命令

您可以按照 instructions in the documentation 覆盖它。请注意，这样的覆盖并不能解决安全问题，而只是承认您知道并愿意承担风险。

---

您可能需要设置 FIELDS TERMINATED BY ',' 或任何分隔符。

对于 CSV 文件，您的语句应如下所示：

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

---

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

将文件中的数据导入到的数据库表。

一个 CSV 文件，其数据与表的列数和每列中的数据类型相匹配。

连接到 MySQL 数据库服务器的帐户具有 FILE 和 INSERT 权限。

假设我们有下表：

https://i.stack.imgur.com/d3Rh9.png

使用以下查询创建表：

CREATE TABLE IF NOT EXISTS `survey` (
  `projectId` bigint(20) NOT NULL,
  `surveyId` bigint(20) NOT NULL,
  `views` bigint(20) NOT NULL,
  `dateTime` datetime NOT NULL
);

您的 CSV 文件必须正确格式化。例如，请参阅以下附加图像：

https://i.stack.imgur.com/ktAIW.png

如果一切正常，请执行以下查询以从 CSV 文件加载数据：

注意：请添加您的 CSV 文件的绝对路径

LOAD DATA INFILE '/var/www/csv/data.csv' 
INTO TABLE survey 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

如果一切都已完成，您已成功将数据从 CSV 文件导出到表中。

---

句法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name' INTO TABLE `tbl_name`
CHARACTER SET [CHARACTER SET charset_name]
FIELDS [{FIELDS | COLUMNS}[TERMINATED BY 'string']]
[LINES[TERMINATED BY 'string']]
[IGNORE number {LINES | ROWS}]

看这个例子：

LOAD DATA LOCAL INFILE
'E:\\wamp\\tmp\\customer.csv' INTO TABLE `customer`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

---

1分钟内在数据库中批量插入超过7,000,000条记录（带计算的超快速查询）：

    LOAD DATA LOCAL INFILE "'.$file.'"
    INTO TABLE tablename
    FIELDS TERMINATED by \',\'
    LINES TERMINATED BY \'\n\'
    IGNORE 1 LINES
    (isbn10,isbn13,price,discount,free_stock,report,report_date)
     SET RRP = IF(discount = 0.00,price-price * 45/100,IF(discount = 0.01,price,IF(discount != 0.00,price-price * discount/100,@RRP))),
         RRP_nl = RRP * 1.44 + 8,
         ID = NULL

RRP 和 RRP_bl 不在 CSV 文件中，但我们正在计算它们并在之后插入它们。

---

如果您从 Windows shell 运行 LOAD DATA LOCAL INFILE，并且需要使用 OPTIONALLY ENCLOSED BY '"'，则必须执行以下操作才能正确转义字符：

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql" -u root --password=%password% -e "LOAD DATA LOCAL INFILE '!file!' INTO TABLE !table! FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"^""' LINES TERMINATED BY '\n' IGNORE 1 LINES" --verbose --show-warnings > mysql_!fname!.out

---

假设您正在使用 XAMPP 和 phpMyAdmin。

您有文件名“ratings.txt”表名“ratings”和数据库名“movies”。

如果您的 XAMPP 安装在“C:\xampp”中，请将“ratings.txt”文件复制到“C:\xampp\mysql\data\movies”文件夹中。

LOAD DATA INFILE 'ratings.txt' INTO TABLE ratings FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

如果您在 localhost 上执行此操作，这可能会起作用。

---

到现在（2019 年底），我更喜欢使用像 Convert CSV to SQL 这样的工具。

如果您有很多行，当 CSV 来自最终用户电子表格时，您可以运行分区块来保存用户错误。

---

您可以从 CSV 或文本文件加载数据。

如果您有一个包含表中记录的文本文件，您可以在表中加载这些记录。

例如，如果您有一个文本文件，其中每一行都是包含每列值的记录，您可以通过这种方式加载记录。

文件表.sql

id //field 1

name //field2

文件表.txt

1,peter

2,daniel

...

Windows 上的示例

LOAD DATA LOCAL INFILE 'C:\\directory_example\\table.txt'
INTO TABLE Table
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

---

您可以尝试像这样插入：

LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

---

编辑 my.ini 文件并在 [mysqld] 部分下添加以下secure-file-priv = ""。此外，如果已经为此设置了任何变量，则将其删除。然后重启 MySQL 服务，执行如下命令，查看值是否修改成功： SHOW VARIABLES LIKE "secure_file_priv";然后你可以在安装的 MySQL 系统中执行以下查询并导入数据， LOAD DATA INFILE "csv file location" INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (column1,column2,. ....);注意：（根据 csv 文件，列名用逗号分隔）要从任何其他远程系统添加任何 CSV 文件，您还可以在上述步骤之后执行以下操作：显示全局变量，如 'local_infile'; -- 注意：如果值为OFF，则执行下面的下一条命令 set global local_infile=true; -- 再次检查该值是否已设置为 ON 执行以下命令从任何其他系统的 CSV 文件导入数据， LOAD DATA LOCAL INFILE "csv file location" INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (column1,column2,.....); -- 按 csv 文件用逗号分隔的列名

---

我得到

错误代码：1290。 MySQL 服务器正在使用 --secure-file-priv 选项运行，因此无法执行此语句

这在使用 WAMP Server 3.0.6 64 位的 Windows 8.1 64 位上对我有用：

我从 C:\wamp64\bin\mysql\mysql5.7.14 编辑了 my.ini 文件。

删除条目secure_file_priv c:\wamp64\tmp\（或您在此处拥有的任何目录）。

我停止了一切——退出 wamp 等等——然后重新启动了一切；然后将我的 CVS 文件放在 C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur （最后一个目录是我的数据库名称）

我执行了 LOAD DATA INFILE 'myfile.csv' INTO TABLE alumnos FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

......瞧！

---