php - 如何通过为空字段添加 NULLS 将 CSV 文件导入 MySQL？

Question

我有一个来自美国人口普查的 CSV 文件，如下所示:

"ZIP5","ZIP4","ZIP9","STATE CODE","STATE","COUNTY CODE","COUNTY NAME","CBSA CODE","CBSA  TITLE","CBSA LSAD","METRO DIVISION CODE","METRO DIVISION TITLE","METRO DIVISION LSAD","CSA   CODE","CSA TITLE","CSA LSAD"
"04841",,"04841","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04843",,"04843","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan     Statistical Area",,,,,,
"04846",,"04846","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04847",,"04847","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04848",,"04848","23","ME","027","Waldo County",,,,,,,,,
"04849",,"04849","23","ME","027","Waldo County",,,,,,,,,
"04850",,"04850","23","ME","027","Waldo County",,,,,,,,,
"04851",,"04851","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04852",,"04852","23","ME","015","Lincoln County",,,,,,,,,

该文件有超过 200 万条记录。大多数记录在所有字段中都没有数据。

这是我为上述 CSV 文件定义的 MySQL 记录布局:

+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ZIP5                 | varchar(5)       | NO   |     | NULL    |                |
| ZIP4                 | varchar(5)       | NO   |     | NULL    |                |
| ZIP9                 | varchar(10)      | NO   |     | NULL    |                |
| STATE_CODE           | varchar(2)       | NO   |     | NULL    |                |
| STATE                | varchar(2)       | NO   |     | NULL    |                |
| COUNTY_CODE          | varchar(3)       | NO   |     | NULL    |                |
| COUNTY_NAME          | varchar(50)      | NO   |     | NULL    |                |
| CBSA_CODE            | varchar(5)       | NO   |     | NULL    |                |
| CBSA_TITLE           | varchar(50)      | NO   |     | NULL    |                |
| CBSA_LSAD            | varchar(50)      | NO   |     | NULL    |                |
| METRO_DIVISION_CODE  | varchar(5)       | NO   |     | NULL    |                |
| METRO_DIVISION_TITLE | varchar(50)      | NO   |     | NULL    |                |
| METRO_DIVISION_LSAD  | varchar(50)      | NO   |     | NULL    |                |
| CSA_CODE             | varchar(3)       | NO   |     | NULL    |                |
| CSA_TITLE            | varchar(50)      | NO   |     | NULL    |                |
| CSA_LSAD             | varchar(50)      | NO   |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

(我刚刚意识到我应该将 ZIP5 定义为主键？)

我读到如果 CSV 文件中有一个空字段，您应该将其更改为\N，但是有没有办法轻松做到这一点？我可以编写一个 PHP 程序来执行此操作，但是如果有超过 200 万条记录，这将花费很长时间，而且我的服务器没有很多 RAM。

如何以最简单的方式将此 CSV 文件成功导入 MySQL？ MySQL 中的 LOAD 命令是否有一些参数可以执行此操作？它现在的工作方式是，它提示 ZIP5 有数据截断，当我查看 MySQL 时，它在邮政编码中有引号并且只有前 4 位数字。谢谢!

Answer 1

首先，我在您上面发布的表格中看不到主键。首先必须始终有一个主键。通常我们使用 AUTOINCREMENT 添加一个名为 id 的列。对于邮政编码之类的东西，描述 2-3 列的复杂键也很方便。一如既往地取决于具体情况。

至于进口。你有一些解决方案

1. 在本地运行脚本以生成 SQL 插入语句，然后通过您可用的任何接口(interface)将数据提供给 mysql 服务器。

2. 上传CSV文件到服务器，使用命令行mysql导入CSV文件。 MySQL 有一个内置的 CSV 导入器，虽然我从来不喜欢它；)

3. 在服务器上运行脚本并一次添加一行。在 PHP 中，您可以逐行加载 CSV 并在每行上插入(记住相应地设置时间限制和内存限制)。提醒，对于第 3 步，如果您通过浏览器而不是通过命令行运行它，那么您的浏览器很可能会超时。放心通过脚本不会停止运行，直到结束。

我想我有一个 CSV 导入器(用于巨大的 CSV 文件——比如地理标记)。如果您需要它，请告诉我，我也许可以找到它并张贴在这里。

不幸的是，我找不到我的 csv 导入器。但是查看 php 手册中 fgetcsv 的第一个条目并进行了一些修改...

set_time_limit(3600); // 1 hour max script execution time. Adjust it according to your expectations.
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    // this will automate things but modify the csv head for each column to represent the actual column name in your table.
    $header = fgetcsv($handle, 1000, ",");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $i = 0;
        $values = array();
        foreach($header as $key) {
            if (!empty($data[$i])) {
               $values[$key] = $data[$i];
           }
        }

        $keys = "`" . implode("`, `", array_keys($values)) . "`";
        $values = "'" . implode("', '", $values) . "'";
        $statement = "INSERT INTO `table_name` ({$keys}) VALUES ({$values})";
        // run the statement. the above is if you don't use PDO. For PDO transform accordingly. $values holds the column_name => value pairs. The values that can be null and should not be inserted you should give them default values in your mysql schema (table)
    }
    fclose($handle);
}

希望对您有所帮助。还没有测试过，但看起来不错 ;)