MySQL CSV 导入和使用 LOAD DATA 的 CSV 之间的区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4819169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between CSV import and CSV using LOAD DATA?
提问by kylex
In phpMyAdmin there are two options to import a CSV file.
在 phpMyAdmin 中有两个选项可以导入 CSV 文件。
One is CSV. The other is CSV using LOAD DATA.
一种是CSV。另一个是使用 LOAD DATA 的 CSV。
What's the difference between these two? Is there an advantage to using one over the other?
这两者有什么区别？使用一个比另一个有优势吗？
回答by TehShrike
LOAD DATA INFILEis a MySQL query that works completely independently of PHPMyAdmin.
LOAD DATA INFILE是一个 MySQL 查询，它完全独立于 PHPMyAdmin 工作。
The CSV import probably involves uploading the file to the PHPMyAdmin server, where it parses the file and builds a series of INSERT statements to be run against the server.
CSV 导入可能涉及将文件上传到 PHPMyAdmin 服务器，在那里它解析文件并构建一系列要在服务器上运行的 INSERT 语句。
Personally, I wouldn't trust anything PHPMyAdmin does ;-) - however, actual performance will probably depend on your table structure and the data.
就个人而言，我不会相信 PHPMyAdmin 所做的任何事情 ;-) - 但是，实际性能可能取决于您的表结构和数据。
I will note, however, that MySQL takes some very efficient shortcuts when inserting data from a LOAD DATA INFILE command.
但是，我会注意到，当从 LOAD DATA INFILE 命令插入数据时，MySQL 采用了一些非常有效的快捷方式。
回答by Ian
As stated above the LOAD DATAoption is actually telling phpMyAdmin to use the MySQL command to let MySQL parse and load the file rather than phpMyAdmin parsing it first.
如上所述，该LOAD DATA选项实际上是告诉 phpMyAdmin 使用 MySQL 命令让 MySQL 解析和加载文件，而不是 phpMyAdmin 首先解析它。
As also stated above, giving MySQL access to load the file can be dangerous if you don't feel 100% secure about the source and accuracy of the file it's self. It's like using a php form with no sql injection protection to insert data.
如上所述，如果您对文件本身的来源和准确性没有 100% 安全，则授予 MySQL 访问权限以加载文件可能会很危险。这就像使用没有 sql 注入保护的 php 表单来插入数据。
However, in some cases phpMyAdmin does not format the data correctly or has trouble parsing it when the regular CSV" option is used. This will cause un-explained errors such as "invalid format on line N" or "incorrect field count on line N" Those might not be exact error messages since I'm not logged into phpMyAdmin at the moment. In these cases the LOAD DATAoption can be used to get passed the error. I think the extra option of Use local keywordhas to do with making sure the correct commands for that specific version of MySQL on the local server is used. Not sure about the last part though.
但是，在某些情况下，phpMyAdmin 无法正确格式化数据或在使用常规CSV" 选项时无法解析数据。这将导致无法解释的错误，例如“第 N 行的格式无效”或“第 N 行的字段计数不正确”这些可能不是确切的错误消息，因为我目前没有登录 phpMyAdmin。在这些情况下，该LOAD DATA选项可用于传递错误。我认为额外的选项Use local keyword与确保正确的命令有关使用本地服务器上特定版本的 MySQL。不过不确定最后一部分。
Something to keep in mind is also the size of the file (number of lines being imported) I have had to break down a 1600 line file into smaller files even when using the LOAD DATAoption in order to get it to go through. It gave no errors but the "affected rows" was incorrect when the file was too big.
需要记住的还有文件的大小（导入的行数）我不得不将 1600 行文件分解成更小的文件，即使在使用该LOAD DATA选项时也是如此，以便让它通过。它没有给出错误，但是当文件太大时“受影响的行”是不正确的。
回答by Jonathan Amend
The first option will have phpMyAdmin parse the CSV file itself and then generate and execute the SQL to insert the data. The second option will let MySQL take care of loading, processing, and inserting the data.
第一个选项将让 phpMyAdmin 解析 CSV 文件本身，然后生成并执行 SQL 以插入数据。第二个选项将让 MySQL 负责加载、处理和插入数据。
Both options (should) behave the same way, but the LOAD DATA INFILE option is generally much faster, and you don't have to worry about PHP's memory/execution time limits. The only problem is that it isn't supported by all configurations because there are security implications for giving MySQL access to the uploaded files, and as such it is often disabled (ex. shared hosting).
这两个选项（应该）的行为方式相同，但 LOAD DATA INFILE 选项通常要快得多，而且您不必担心 PHP 的内存/执行时间限制。唯一的问题是并非所有配置都支持它，因为让 MySQL 访问上传的文件存在安全隐患，因此它通常被禁用（例如共享主机）。
回答by VIKASH
CSV and CSV using LOAD DATA. The first method is implemented internally by phpMyAdmin and is the recommended one for its simplicity. With the second method, phpMyAdmin receives the file to be loaded, and passes it to MySQL. In theory, this method should be faster. However, it has more requirements due to MySQL itself
CSV 和 CSV 使用 LOAD DATA。第一种方法由 phpMyAdmin 内部实现，因其简单而推荐使用。使用第二种方法，phpMyAdmin 接收要加载的文件，并将其传递给 MySQL。理论上，这种方法应该更快。但是，由于 MySQL 本身的原因，它有更多的要求
回答by user535673
To add to the other replies: the "CSV" one insists you have exactly the same amount of columns in the text file and the table. "CSV using LOAD DATA" does not.
要添加到其他回复中：“CSV”坚持认为文本文件和表格中的列数完全相同。“使用 LOAD DATA 的 CSV”没有。
