如果在执行查询之前使用echo($sql);
,你会发现你的查询语句存在以下问题:
文件名应该用引号括起来而不是反引号,因为它是字符串字面量而不是标识符。
在FIELDS TERMINATED BY
和ENCLOSED BY
以及ESCAPED BY
从句中指定分隔符时,绝对没有必要调用mysql_escape_string()
。
你过度使用了反引号。实际上,在你的情况下,由于没有使用保留字,可以全部弃用。它们只会增加混乱程度。
在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)
我认为您需要在加载时转换一些字段:
如果您表中的 date
是 datetime
数据类型,则需要进行转换,否则会出现错误:
Incorrect datetime value: 'Sep-18-2013 01:53:45 PM' for column 'date' at row
您需要处理 Number
字段中值周围的单引号。
您可能想要将 "null"
字符串文字更改为实际的 NULL
,以用于 addr, 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');
查询 OK,已影响 3 行(0.00 秒)
记录: 3 删除: 0 跳过: 0 警告: 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 行(0.00 秒)
最后,使用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') ";