CSV 导入和使用 LOAD DATA 的 CSV 之间的区别？
在 phpMyAdmin 中，有两个选项可用于导入 CSV 文件。
一种是 CSV。另一种是使用 LOAD DATA 的 CSV。
这两者有什么区别？使用其中一种比另一种有优势吗？
In phpMyAdmin there are two options to import a CSV file.
One is CSV. The other is CSV using LOAD DATA.
What's the difference between these two? Is there an advantage to using one over the other?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
LOAD DATA INFILE 是一个完全独立于 PHPMyAdmin 工作的 MySQL 查询。
CSV 导入可能涉及将文件上传到 PHPMyAdmin 服务器，在服务器中解析文件并构建一系列要针对服务器运行的 INSERT 语句。
就我个人而言，我不会相信 PHPMyAdmin 所做的任何事情；-) - 但是，实际性能可能取决于您的表结构和数据。
然而，我要指出的是，当从 LOAD DATA INFILE 命令插入数据时，MySQL 采用了一些非常有效的快捷方式。
LOAD DATA INFILE is a MySQL query that works completely independently of 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.
Personally, I wouldn't trust anything PHPMyAdmin does ;-) - however, actual performance will probably depend on your table structure and the data.
I will note, however, that MySQL takes some very efficient shortcuts when inserting data from a LOAD DATA INFILE command.
如上所述，LOAD DATA 选项实际上是告诉 phpMyAdmin 使用 MySQL 命令让 MySQL 解析并加载文件，而不是 phpMyAdmin 首先解析它。
如上所述，如果您对文件本身的来源和准确性没有 100% 的安全感，那么授予 MySQL 访问权限来加载文件可能会很危险。就像使用没有sql注入保护的php表单来插入数据一样。
但是，在某些情况下，phpMyAdmin 无法正确格式化数据，或者在使用常规“CSV”选项时无法解析数据。这将导致无法解释的错误，例如“第 N 行格式无效”或“N 行字段计数不正确” 这些可能不是确切的错误消息，因为我目前尚未登录 phpMyAdmin 在这些情况下，可以使用
LOAD DATA选项来传递错误。我认为使用本地关键字的额外选项与确保使用本地服务器上特定版本的MySQL的正确命令有关，但不确定最后一部分。介意的也是文件的大小（导入的行数），即使使用
LOAD DATA选项，我也必须将一个1600行的文件分解成更小的文件才能让它通过它没有给出错误，但当文件太大时，“受影响的行”不正确。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.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.
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 theLOAD DATAoption can be used to get passed the error. I think the extra option ofUse 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.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.第一个选项将让 phpMyAdmin 解析 CSV 文件本身，然后生成并执行 SQL 来插入数据。第二个选项将让 MySQL 负责加载、处理和插入数据。
两个选项（应该）的行为方式相同，但 LOAD DATA INFILE 选项通常要快得多，并且您不必担心 PHP 的内存/执行时间限制。唯一的问题是，并非所有配置都支持它，因为授予 MySQL 访问上传文件的权限会带来安全隐患，因此它经常被禁用（例如共享托管）。
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.
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).
要添加到其他回复：“CSV”坚持认为文本文件和表中的列数完全相同。 “使用 LOAD DATA 的 CSV”则不然。
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。第一种方法是由 phpMyAdmin 内部实现的，由于其简单性，推荐使用第一种方法。使用第二种方法，phpMyAdmin接收要加载的文件，并将其传递给MySQL。理论上来说，这个方法应该更快。但由于MySQL本身的原因，它有更多的要求
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