将多个文本文件及其标题导入MySQL

Question

我有一个包含大约10.000个文本文件的文件夹。我想将这些文件导入具有两列的MySQL表中。对于每一行，我希望将文本文件的标题(例如，1234.txt)放在一列中，而将文件的内容放在另一列中。我可以使用PHP。你建议我怎么做？

Answer 1

您可以尝试如下所示，下面的代码将遍历目录中的所有文本文件，并将每个文件的文件名作为标题添加到MySQL数据库中。

<?php
$size = "";
//Change "MyFolder" with the path and name of the directory where the .txt files are located.
$dir = "MyFolder";

if ($handle = opendir($dir)) {
    while (false !== ($file = readdir($handle))) {
        if($file == '.' || $file == '..') continue;

        $myFile = "$dir/$file";
        $fh = fopen($myFile, 'r');

        $size = filesize($myFile);
        if ($size > 0) {
        $content = fread($fh, $size);

        $title = $file; 
        echo $title."</br>";
        }

        $con=mysqli_connect("HostName","Username","Password","Database");
        // Check connection
        if (mysqli_connect_errno())
        {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }     
        mysqli_query($con,"INSERT INTO table_name (Title, Content) VALUES ('$title', '$content')");

        mysqli_close($con);  

        fclose($fh);
    }
closedir($handle);
}
?>

Answer 2

您可以(Ab)使用LOAD DATA INFILE在列中加载整个文件。“技巧”只是提供文件中没有以行/字段终止形式出现的一系列字符：

mysql> create table tbl(fname CHAR(255), data TEXT);
Query OK, 0 rows affected (0.35 sec)


mysql> LOAD DATA INFILE '/tmp/demo.txt' INTO TABLE tbl 
            FIELDS TERMINATED BY 'XXX' LINES TERMINATED BY 'YYY' 
            (data) SET fname = 'demo.csv';
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tbl;
+----------+--------------------------------------------------------------------------------------------------+
| fname    | data                                                                                             |
+----------+--------------------------------------------------------------------------------------------------+
| demo.csv | Date, Amount, Name
2013-01-01, 1000, "Apple"
2013-01-02, 500, "Orange"
2013-01-03, 250, "Prune"
 |
+----------+--------------------------------------------------------------------------------------------------+
1 rows in set (0.00 sec)

如果命令加载文件夹中的所有文件，则必须从宿主语言的循环中调用该语句，甚至可以使用简单的shell脚本：

for i in *.txt; do 
  echo "LOAD DATA INFILE '$i' INTO TABLE tbl 
                FIELDS TERMINATED BY 'XXX' LINES TERMINATED BY 'YYY' 
                (data) SET fname = '$i';";
  done
  | mysql -p -u user_name -h host_name db_name