php mysql 在解析 csv 时缺少第一列

Question

我有以下结构的 csv 文件。

"Agency_Name","First_Name","Last_Name","Full_Name","email_address","Address","City","State","ZIPCode","County","Phone_Number","Fax_Number","Website","Timezone","Latitude","Longitude","ZIPType","CityType","CountyFIPS","StateName","StateFIPS","UTC"
"D Real","Ki","Alod-Mell","Ki Alod-Mell","team1@example.com","Lake St","ka","AK","35-755","Stka","9071032","9077475","","Alka","53.0324","-133.339294","S","D","0220","Alaka","02","-3.0"
"retds","La","Mi","LaMi","mi@example.com","Lake Street","Ska","AK","935","Stka","(747-4880","(947-2688","","Alasa","52.061324","-125.339294","S","D","020","Alaka","02","-9.0"

这是我在 codeigniter 中的 mysql 查询。

$query = $this->db->query('
            LOAD DATA LOCAL INFILE "'.$file.'"
            INTO TABLE '.$table.'
            FIELDS TERMINATED by \',\'
            ENCLOSED BY \'"\'
            LINES TERMINATED BY \'\n\'
            STARTING BY \'\'
            IGNORE 1 LINES
            ');

但问题是它跳过了每一行的第一列。正如之前我解析每一行并过滤掉数组中的列一样，它适用于小文件，但具有数百万行的文件只是停留在那里并且服务器提供 500。

Answer 1

问题是根据屏幕截图，您的表中有一个 Id 字段，它是表中最左边的字段。在导入期间 - 除非另有说明 - MySQL 将从左到右加载字段，因此 csv 文件中 agency_name 字段中的数据将插入到 id 字段中。

显然，您没有启用严格的 sql 模式，因此 MySQL 会默默地将机构名称字符串转换为数字 (0)，这在默认设置下将触发下一个在 id 字段中设置的自动增量值。如果您在导入后立即运行 show warnings 命令，您可能会在警告日志中看到此行为的证据。

作为 load data infile 上的 MySQL 文档声明说:

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col1,col2,...);

You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

因此，指定 MySQL 应该将数据加载到的列列表。我也会考虑启用严格的 sql 模式。一开始可能不方便，但它会强制您创建符合 sql 标准的 sql 语句。