php - 大型 mysql 插入语句

Question

我有一个如下所示的 .txt 文件:

    id,lat,lon,sequence,dist
    98372,40.535257,-111.871612,1,0.0
    98372,40.536023,-111.872235,2,0.1001
    98372,40.535629,-111.872599,3,0.1335
    98372,40.535499,-111.872559,4,0.1758
    ...etc

这个文件可以有 863650 行长! (34MB)
我需要使用 MyISAM 引擎将每一行作为一行插入 MySQL 数据库中。 (我假设这将是最快的)

我在 GoDaddy 的服务器上托管，因此我对 apache 和 mysql 配置没有太多控制权。

这就是我目前所拥有的，它可以工作，除了需要大约 6 分钟以上并且服务器在 2 分钟后超时，因此它永远不会完成:

    $raw = file_get_contents('file.txt');
    $lines = explode("\r\n", $raw);
    $tablename = 'tablename';
    $tableHeaders = 'id,lat,lon,sequence,dist';
    foreach($lines as $line) {
        $line_values = explode(",", $line);
        $sqlValues = "VALUES(";
        foreach($line_values as $value) {
            $sqlValues .=  "'$value',";
        }
        $sqlValues = substr($sqlValues, 0, -1) . ")";
        $sqlSyntax = "INSERT INTO $tablename ($tableHeaders) $sqlValues";
        $mysqlcon->query($sqlSyntax);
    }

这个可以优化得更好吗？或者是否可以构建一个巨大的插入语句，例如:

    "INSERT INTO table (id,lat,lon,sequence,dist)
    VALUES (98372,40.535257,-111.871612,1,0.0),
    (98372,40.536023,-111.872235,2,0.1001),
    (98372,40.535629,-111.872599,3,0.1335),
    (98372,40.535499,-111.872559,4,0.1758),
    ...etc (863647 more lines)"

任何帮助将不胜感激!

Answer 1

构建巨大的 INSERT，例如

"INSERT INTO table (id,lat,lon,sequence,dist)
VALUES (98372,40.535257,-111.871612,1,0.0),
(98372,40.536023,-111.872235,2,0.1001),
(98372,40.535629,-111.872599,3,0.1335),
(98372,40.535499,-111.872559,4,0.1758),
...etc (863647 more lines)"

这会快很多倍。当我遇到类似的问题时，使用单个大型查询通常比单个查询快 1,000 倍。

注意:由于内存限制，您可能希望每 1,000 或 5,000 行“分块”插入； 863,650 行可能会超出共享服务器上的最大可用内存。

编辑如果可以的话，请执行 LOAD DATA 。出于安全原因，GoDaddy 共享托管计划可能不提供此功能，但您可以尝试一下。你会想要这样的东西:

LOAD DATA INFILE '/path/to/file.txt' /* change to fit your path */
INTO TABLE `table` /* Is "table" really your table's name? If so, you should pick something more descriptive... */
FIELDS TERMINATED BY ',' /* because your data is separated by commas */
/* See my note below about LINES TERMINATED BY */
IGNORE 1 LINES /* skip the headers */
(id,lat,lon,sequence,dist) /* the column names */

您可能需要一个 LINES TERMINATED BY 子句。 From the manual :

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.