如果您在执行之前这样做，您会看到查询的语法不正确，原因如下：echo($sql);
文件名应括在引号中，而不是反引号，因为它是字符串文本而不是标识符。
绝对没有必要调用来指定 and 和 子句中的分隔符。mysql_escape_string()FIELDS TERMINATED BYENCLOSED BYESCAPED BY
你过度使用反引号。事实上，在你的情况下，由于没有使用保留字，你把它们都抛弃了。它们只会增加混乱。
在 CSV 文件第一行的末尾，您必须拥有，因为您使用它们作为行分隔符的一部分。如果您不这样做，您不仅可以跳过第一行，还可以跳过包含数据的第二行。,,,
不能多次使用子句。你必须以不同的方式处理领域。ENCLOSED BYNumber
看看你的样本行，恕我直言，你不需要。但是，如果您觉得需要它，请使用它。ESCAPED BYESCAPED 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)
现在恕我直言，您需要在加载时转换相当多的字段：
-
如果表中的数据类型为数据类型，则需要对其进行转换，否则您将收到错误datedatetime
日期时间值不正确：行“日期”列的“Sep-18-2013 01：53：45 PM”
您必须处理围绕字段中的值的单个 qoutesNumber
您很可能希望将列的字符串文本更改为实际值"null"NULLaddr, pin, city, state, country
如果持续时间始终以秒为单位，则可以提取秒的整数值，并以这种方式将其存储在表中，以便以后能够轻松聚合持续时间值。
话虽如此，该语句的有用版本应如下所示
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') ";
