如何将超过 100000 条记录导入一个 mysql 数据库? [英] How to import more than 100000 records into a mysql database?

问题描述

我有一个 sql 转储文件.表中有 185458 条记录.例如

I have a sql dump file. which has 185458 records in the table. for example

INSERT INTO `cities` (`city_id`, `city_name`, `countryId`, `stateid`, `countryCode`, `latitude`, `longitude`, `zip_code`) VALUES
(2, 'Djelfa', 67, 1, 'DZA', 34.67, 3.25, ''),
(3, 'Valenza', 115, 2, 'ITA', 45.02, 8.63, '15048'),
...
...
...
(185452, 'Bosendurnbach', 17, 771, 'AUT', 48.5, 15.77, ''),
(185453, 'Môlay', 79, 937, 'FRA', 47.73, 3.94, ''),
(185454, 'Miloszyce', 183, 422, 'POL', 51.05, 17.31, ''),
(185455, 'Lovce', 212, 698, 'SVK', 48.45, 18.37, ''),
(185456, 'Winchester', 174, 74, 'NZL', -44.2, 171.28, ''),
(185457, 'Wohlde', 62, 402, 'DEU', 54.4, 9.3, ''),
(185458, 'Chiavazza', 115, 2, 'ITA', 45.58, 8.07, '');

很长时间后，我收到此错误

After long time i'm getting this error

致命错误:超过 300 秒的最大执行时间C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php 第 290 行

Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 290

我如何导入?

推荐答案

错误消息非常清楚，并指出了问题所在:php 环境中执行时间的限制.所以一些解决这个问题的方法是显而易见的:

The error message is quite clear and points you into what the issue is: the limitation of execution time in your php environment. So some approaches to solve this are obvious:

1. 增加环境的时间限制

您可以通过提高 php 配置中的限制来实现，或者，如果允许，可以在导入脚本中动态增加它.所以类似于 ini_set('max_execution_time', 3000).这两个选项都记录在案.php 文档应该总是成为您开始寻找此类问题答案的第一个位置.

You can do that by either raising the limit in your php configuration or, if permitted, by dynamically increasing it inside your import script. So something like ini_set('max_execution_time', 3000). Both options are documented. The php documentation should always be the first location where you should start to look for an answer to such a question.

1. 使用不同的 php 环境

通常为网络环境选择此类限制，以降低服务来自任何人的请求的风险.然而，没有什么可以反对在另一个环境中使用不同的配置.导入作业通常不是通过使用 Web 请求来处理的，而是通过在命令行 (CLI) 中使用 php 来处理的.对于这种通常使用单独的配置.同样，这是记录在案的.这就是您可以在此处根据需要配置彼此不同的两种环境的方法.但是，为此您需要在 CLI 上访问 php.这在您自己的系统上没有问题，但通常在廉价的网络托管服务上不可用.

Typically such limitation is chosen for a web environment to reduce the risks of serving requests from anyone out there. However nothing speaks against using a different configuration for another environment. Import jobs are typically not processed by using web requests, but by using php in the command line (CLI). For such typically a separate configuration is used. Again, this is documented. That is what you can use here to configure both environments different from each other according to your needs. However for this you need access to php on CLI. That is no issue on your own system, but usually not available on a cheap web hosting service.

1. 将您的导入分成更小的块

由于您导入的数据存储在一个 sql 文件中，因此是一个简单的文本文件，您可以使用任何普通的文本编辑器来修改该文件.注意:文本编辑器，不是文字处理器.您可以将其中包含的大 INSERT 语句拆分为几个块.语法很明显.

Since the data you import is stored in a sql file, so a simple text file, you can use any ordinary text editor to modify that file. Note: a text editor, not a word processor. You can split the big INSERT statement contained in there into several chunks. The syntax is quite obvious.

1. 使用许多单独的插入语句而不是一个大的语句

根据您用来创建您尝试导入的转储文件的工具，您现在可以选择创建转储，以便它使用许多单独的 INSERT 语句(每行一个)代替一个大的，组合的.mysqldump 例如为此提供 --skip-extended-insert 标志.使用这样的转储文件，通过简单地拆分文件将导入拆分为几个较小的块是微不足道的.

Depending on the tool you use to create that dump file you are trying to import now you have an option to create the dump such that it uses many separate INSERT statements (one for each row) instead of one big, combined one. mysqldump for example offers the --skip-extended-insert flag for this. With such a dump file it is trivial to split the import into several smaller chunks by simply splitting the file.

1. 完全绕过 php 进行导入

如果您可以直接访问您的数据库服务器(在本例中为 MySQL)，那么您可以直接与其交互，而无需在两者之间使用 phpMyAdmin 工具.您可以通过 MySQLs source 命令直接加载转储文件.这样你就完全独立于 php 的限制.

If you have a direct access to your database server (MySQL in this case), then you can simply interact directly with it instead of using the phpMyAdmin tool inbetween. You can simply load your dumpfile directly by means of MySQLs source command. That way you are completely independent from the php limitations.

这篇关于如何将超过 100000 条记录导入一个 mysql 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！