PHP-使用LOAD DATA INFILE将CSV文件导入mysql数据库

Question

我有这样的.csv文件数据

Date,Name,Call Type,Number,Duration,Address,PostalCode,City,State,Country,Latitude,Longitude
"Sep-18-2013 01:53:45 PM","Unknown","outgoing call",'123456',"0 Secs","null","null","null","null","null",0.0,0.0,,,
"Sep-18-2013 01:54:14 PM","Unknown","outgoing call",'1234567890',"0 Secs","null","null","null","null","null",0.0,0.0,,,
"Sep-18-2013 01:54:37 PM","Unknown","outgoing call",'14772580369',"1 Secs","null","null","null","null","null",0.0,0.0,,,

我正在使用以下代码将数据插入数据库

$sql = "LOAD DATA INFILE `detection.csv`
              INTO TABLE `calldetections`
              FIELDS TERMINATED BY '".@mysql_escape_string(",").
             "` OPTIONALLY ENCLOSED BY `".@mysql_escape_string("\"").
             "` OPTIONALLY ENCLOSED BY `".@mysql_escape_string("\'").
             "` ESCAPED BY `".@mysql_escape_string("\\").
              "` LINES TERMINATED BY `".",,,\\r\\n".
             "`IGNORE 1 LINES `"

             ."(`date`,`name`,`type`,`number`,`duration`,`addr`,`pin`,`city`,`state`,`country`,`lat`,`log`)";
      $res = @mysql_query($con,$sql);

但是什么也没插入；错误在哪里？

Answer 1

如果echo($sql);执行该操作，您会发现查询语法不正确，原因如下：

1. 文件名应该用引号而不是反引号引起来，因为它是字符串文字而不是标识符。

2. 绝对不需要调用mysql_escape_string()在FIELDS TERMINATED BYand ENCLOSED BY和ESCAPED BY子句中指定分隔符。

3. 您过度使用了反引号。实际上，在您的情况下，由于没有使用保留字，因此请放弃所有保留字。它们只会增加混乱。

4. 在CSV文件第一行的末尾，您 必须要 ,,,使用它，因为将它们用作行定界符的一部分。如果您不这样做，则不仅会跳过第一行，还会跳过包含数据的第二行。

5. 您不能ENCLOSED BY多次使用子句。您必须以Number不同的方式处理字段。

6. 查看您不需要的示例行恕我直言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)

现在，恕我直言，您需要在加载它们时转换很多字段：

1. 如果date表中的datetime数据类型为数据类型，则需要对其进行转换，否则会出现错误

日期时间值不正确：行的“日期”列为“ 2013年9月18日01:53:45 PM”

1. 您必须处理围绕Number字段值的单个qoutes

2. 你最有可能要更改"null"字符串字面量实际NULL为addr, pin, city, state, country列

3. 如果持续时间总是以秒为单位，则可以提取秒的整数值并将其存储在表中，以便以后可以轻松汇总持续时间值。

话虽这么说，该语句的有用版本应如下所示

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>加载数据文件'/tmp/detection.csv'
    ->进入表呼叫检测
    ->以'，'结尾的字段
    ->可选用““''封闭 
    ->以',,, \ n'结尾的行
    ->忽略1行 
    ->（@日期，名称，类型，@编号，@持续时间，@ addr，@ pin，@ city，@ state，@ country，lat，log）
    ->设置日期= STR_TO_DATE（@date，'％b-％d-％Y％h：％i：％s％p'），
    ->数字= TRIM（都是'\''FROM @number），
    ->持续时间= 1 * TRIM（从@duration跟踪'Secs'），
    -> addr = NULLIF（@addr，'null'），
    ->引脚= NULLIF（@pin，'null'），
    ->城市= NULLIF（@city，'null'），
    ->状态= NULLIF（@state，'null'），
    ->国家= NULLIF（@ country，'null'）;
查询正常，受影响的3行（0.00秒）
记录：3已删除：0跳过：0警告：0

mysql>从calldetections中选择*;
+ --------------------- + --------- + --------------- +- ------------ + ---------- + ------ + ------ + ------ + ----- -+ --------- + ------ + ------ +
| 日期| 名称| 类型 编号 持续时间 地址| 别针 城市| 州| 国家| lat | 日志|
+ --------------------- + --------- + --------------- +- ------------ + ---------- + ------ + ------ + ------ + ----- -+ --------- + ------ + ------ +
| 2013-09-18 13:53:45 | 未知 拨出电话| 123456 | 0 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 |
| 2013-09-18 13:54:14 | 未知 拨出电话| 1234567890 | 0 | NULL | NULL | NULL | NULL | NULL | 0.0 | 0.0 |
| 2013-09-18 13:54:37 | 未知 拨出电话| 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') ";