php - LOAD DATA LOCAL INFILE 不能跳过列？

Question

我已经搜索了几个小时了，但没有用。

我有一份总统职位的 csv 文件。第一行是标题。

Presidency ,President ,Wikipedia Entry,Took office ,Left office ,Party ,Portrait,Thumbnail,Home State

我想创建一个只有 Presidency、President 和 Party 的关系。

$sqlquerycreate1 = "CREATE TABLE IF NOT EXISTS `sample1` (
  `Presidency`  VARCHAR(30),
  `President` VARCHAR(30),
  `Party` VARCHAR(30)
)";
mysql_query($sqlquerycreate1);

# download the file off the internet
$file = file_get_contents("http://localhost/sample.csv");
$filepath = "sample.csv";
file_put_contents($filepath, $file);

# load the data into nycgrid table
$pt11 = "LOAD DATA LOCAL INFILE ";
$pt21 = "'C:/xampp/htdocs/test/file/sample.csv' INTO TABLE sample1(Presidency,President,@Wikipedia Entry,@Took office ,@Left office ,Party,@Portrait,@Thumbnail,@Home State) ";
$pt31 = "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ";
$pt41 = "LINES TERMINATED BY '\r\n' ";
$pt51 = "IGNORE 1 LINES";
$sqlquerynew1 = $pt11.$pt21.$pt31.$pt41.$pt51;
mysql_query($sqlquerynew1);

这是行不通的。该表已创建，但未加载任何数据。知道为什么吗？谢谢。

---

mysql> LOAD DATA LOCAL INFILE 'C:/xampp/htdocs/sample.csv' INTO TABLE sample1(Pr
esidency, President, @x, @x, @x, Party, @x, @x, @x) FIELDS TERMINATED BY ',' ENC
LOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'FIELD
S TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINE' at
line 1

Answer 1

您的 LOAD DATA 语句中存在语法错误。特别是:

1. 如 the manual 中所述:

   User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @`my-var`)

   因此您的变量名为@Wikipedia Entry、@Took office、@Left office 和@Home state在语法上都是不正确的。您必须删除空格或引用变量名称。

   但是，您不需要使用不同的变量名称，除非您打算在别处引用它们。如 the manual 中所述:

   You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

   LOAD DATA INFILE 'file.txt'
     INTO TABLE t1
     (column1, @dummy, column2, @dummy, column3);
   

2. FIELDS、LINES 和 IGNORE 子句都应该在列列表之前。如 the manual 中所述，语法为:

   LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<strong><em>file_name</em></strong>'
       [REPLACE | IGNORE]
       INTO TABLE <strong><em>tbl_name</em></strong>
       [CHARACTER SET <strong><em>charset_name</em></strong>]
       [{FIELDS | COLUMNS}
           [TERMINATED BY '<strong><em>string</em></strong>']
           [[OPTIONALLY] ENCLOSED BY '<strong><em>char</em></strong>']
           [ESCAPED BY '<strong><em>char</em></strong>']
       ]
       [LINES
           [STARTING BY '<strong><em>string</em></strong>']
           [TERMINATED BY '<strong><em>string</em></strong>']
       ]
       [IGNORE <strong><em>number</em></strong> LINES]
       [(<strong><em>col_name_or_user_var</em></strong>,...)]
       [SET col_name = <strong><em>expr</em></strong>,...]

因此，你可以简单地做:

LOAD DATA LOCAL INFILE 'C:/xampp/htdocs/test/file/sample.csv'
    INTO TABLE sample1
    FIELDS
        TERMINATED BY ','
        ENCLOSED BY '\"'
    LINES
        TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (Presidency, President, @x, @x, @x, Party, @x, @x, @x)