如果你愿意echo($sql);在执行之前，您会发现查询的语法不正确，原因如下：
文件名应该用引号引起来，而不是反引号，因为它是字符串文字而不是标识符。
完全没有必要打电话mysql_escape_string()指定分隔符FIELDS TERMINATED BY and ENCLOSED BY and ESCAPED BY条款。
您过度使用反引号。事实上，在您的情况下，由于没有使用保留字，因此您将它们全部抛弃。它们只会增加混乱。
在 CSV 文件第一行的末尾，您可以必须有 ,,,因为您将它们用作行分隔符的一部分。如果您不这样做，您不仅会跳过第一行，还会跳过包含数据的第二行。
你不能使用ENCLOSED BY条款不止一次。你必须处理Number以不同的方式领域。
恕我直言，查看您不需要的示例行ESCAPED BY。但如果你觉得你需要它像这样使用它ESCAPED BY '\\'.
话虽这么说，一个语法正确的语句可能看起来像这样
LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ',,,\r\n'
IGNORE 1 LINES
(date, name, type, number, duration, addr, pin, city, state, country, lat, log)
现在恕我直言，您在加载时需要转换相当多的字段：
-
if date你的表中是datetime数据类型需要转换，否则会报错
日期时间值不正确：行中“日期”列的“Sep-18-2013 01:53:45 PM”
你必须处理围绕值的单引号Number field
你很可能想要改变"null"字符串文字到实际值NULL for addr, pin, city, state, country columns
如果持续时间始终以秒为单位，那么您可以提取秒的整数值并将其以这种方式存储在表中，以便以后能够轻松聚合持续时间值。
话虽这么说，该声明的有用版本应该如下所示
LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ',,,\r\n'
IGNORE 1 LINES
(@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
number = TRIM(BOTH '\'' FROM @number),
duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
addr = NULLIF(@addr, 'null'),
pin = NULLIF(@pin, 'null'),
city = NULLIF(@city, 'null'),
state = NULLIF(@state, 'null'),
country = NULLIF(@country, 'null')
下面是在我的机器上执行查询的结果
mysql> LOAD DATA INFILE '/tmp/detection.csv'
-> INTO TABLE calldetections
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY ',,,\n'
-> IGNORE 1 LINES
-> (@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
-> SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
-> number = TRIM(BOTH '\'' FROM @number),
-> duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
-> addr = NULLIF(@addr, 'null'),
-> pin = NULLIF(@pin, 'null'),
-> city = NULLIF(@city, 'null'),
-> state = NULLIF(@state, 'null'),
-> country = NULLIF(@country, 'null');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from calldetections;
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
| date | name | type | number | duration | addr | pin | city | state | country | lat | log |
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
| 2013-09-18 13:53:45 | Unknown | outgoing call | 123456 | 0 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 |
| 2013-09-18 13:54:14 | Unknown | outgoing call | 1234567890 | 0 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 |
| 2013-09-18 13:54:37 | Unknown | outgoing call | 14772580369 | 1 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 |
+---------------------+---------+---------------+-------------+----------+------+------+------+-------+---------+------+------+
3 rows in set (0.00 sec)
最后在 php 中将查询字符串分配给$sql变量应该是这样的
$sql = "LOAD DATA INFILE 'detection.csv'
INTO TABLE calldetections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY ',,,\\r\\n'
IGNORE 1 LINES
(@date, name, type, @number, @duration, @addr, @pin, @city, @state, @country, lat, log)
SET date = STR_TO_DATE(@date, '%b-%d-%Y %h:%i:%s %p'),
number = TRIM(BOTH '\'' FROM @number),
duration = 1 * TRIM(TRAILING 'Secs' FROM @duration),
addr = NULLIF(@addr, 'null'),
pin = NULLIF(@pin, 'null'),
city = NULLIF(@city, 'null'),
state = NULLIF(@state, 'null'),
country = NULLIF(@country, 'null') ";
