mysql - 使用 LOAD DATA INFILE 跳过第一列

Question

我有这样的表:

mysql> show create table final\G;
*************************** 1. row ***************************
       Table: final
Create Table: CREATE TABLE `final` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `cdatetime` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `district` varchar(255) NOT NULL,
  `beat` varchar(255) NOT NULL,
  `grid` varchar(255) NOT NULL,
  `crimedescr` varchar(255) NOT NULL,
  `ucr_ncic_code` varchar(255) NOT NULL,
  `latitude` varchar(255) NOT NULL,
  `longitude` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

我有这样的 csv 文件:

cdatetime,address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude
1/1/06 0:00,3108 OCCIDENTAL DR,3,3C        ,1115,10851(A)VC TAKE VEH W/O OWNER,2404,38.55042047,-121.3914158
1/1/06 0:00,2082 EXPEDITION WAY,5,5A        ,1512,459 PC  BURGLARY RESIDENCE,2204,38.47350069,-121.4901858
1/1/06 0:00,4 PALEN CT,2,2A        ,212,10851(A)VC TAKE VEH W/O OWNER,2404,38.65784584,-121.4621009
1/1/06 0:00,22 BECKFORD CT,6,6C        ,1443,476 PC PASS FICTICIOUS CHECK,2501,38.50677377,-121.4269508

我想做的是将该 CSV 文件加载到最终表中。问题是 csv 文件没有 ID 列，所以我在想是否有可能以某种方式告诉 mysql 跳过列 ID 并将数据加载到其余列中，但必须使用 ID。所以理想情况下它看起来像这样:

“1/1/06 0:00,3108 OCCIDENTAL DR,3,3C ,1115,10851(A)VC TAKE VEH W/O OWNER,2404,38.55042047,-121.3914158”被加载到列中，mysql 自动添加1 到列 ID，然后加载“1/1/06 0:00,2082 EXPEDITION WAY,5,5A ,1512,459 PC BURGLARY RESIDENCE,2204,38.47350069,-121.4901858”，mysql 将 2 添加到 ID 列等。 .

最近用户“Shadow”告诉我应该指定要加载的列，所以我做了这样的事情:

load data infile '/SacramentocrimeJanuary2006.csv' INTO TABLE final (cdatetime, address, district, beat, grid, crimedescr, ucr_ncic_code, latitude, longitude);

Mysql 返回:

ERROR 1261 (01000): Row 1 doesn't contain data for all columns

根据 mysql load data infile manual field delimiter is not ","所以我试图通过在语句末尾添加 FIELDS TERMINATED BY ',' 来更改它，但这会中断查询。这里的正确语法是什么？

谢谢

回答

mysql> CREATE TABLE `final` (
    ->   `id` int(4) NOT NULL AUTO_INCREMENT,
    ->   `cdatetime` longtext  NULL,
    ->   `address` longtext  NULL,
    ->   `district` longtext  NULL,
    ->   `beat` longtext  NULL,
    ->   `grid` longtext  NULL,
    ->   `crimedescr` longtext  NULL,
    ->   `ucr_ncic_code` longtext  NULL,
    ->   `latitude` longtext  NULL,
    ->   `longitude` longtext  NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.17 sec)

mysql> LOAD DATA infile '/SacramentocrimeJanuary2006.csv'  INTO TABLE final FIELDS TERMINATED BY ',' lines terminated by '\r' IGNORE 1 ROWS (cdatetime, address, district, beat, grid, crimedescr, ucr_ncic_code, latitude, longitude);
Query OK, 7584 rows affected (0.08 sec)
Records: 7584  Deleted: 0  Skipped: 0  Warnings: 0

Answer 1

Linux:

LOAD DATA INFILE '/home/frank/try_this123.txt'
INTO TABLE final
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(cdatetime, address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude)
set id = NULL;

或Windows:

LOAD DATA INFILE 'c:\\nate\\try_this123.txt'
INTO TABLE final
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(cdatetime, address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude)
set id = NULL;

.

mysql> select * from final;
+----+-------------+---------------------+----------+------------+------+-------------------------------+---------------+-------------+---------------+
| id | cdatetime   | address             | district | beat       | grid | crimedescr                    | ucr_ncic_code | latitude    | longitude     |
+----+-------------+---------------------+----------+------------+------+-------------------------------+---------------+-------------+---------------+
 | 1 | 1/1/06 0:00 | 3108 OCCIDENTAL DR  | 3        | 3C         | 1115 | 10851(A)VC TAKE VEH W/O OWNER | 2404          | 38.55042047 | -121.3914158
 | 2 | 1/1/06 0:00 | 2082 EXPEDITION WAY | 5        | 5A         | 1512 | 459 PC  BURGLARY RESIDENCE    | 2204          | 38.47350069 | -121.4901858
 | 3 | 1/1/06 0:00 | 4 PALEN CT          | 2        | 2A         | 212  | 10851(A)VC TAKE VEH W/O OWNER | 2404          | 38.65784584 | -121.4621009
 | 4 | 1/1/06 0:00 | 22 BECKFORD CT      | 6        | 6C         | 1443 | 476 PC PASS FICTICIOUS CHECK  | 2501          | 38.50677377 | -121.4269508
+----+-------------+---------------------+----------+------------+------+-------------------------------+---------------+-------------+---------------+

我让它在没有任何封闭分界(如单引号或双引号)的情况下工作。问题是，当您的地址有逗号并且它会因转移问题而丢弃您的所有数据时会发生什么。

这就是为什么，理想情况下(阅读:几乎绝对)，您通常需要将数据用双引号括起来，除非您的数据是由您生成的并且几乎是简单的，例如:

1,2,cat,14,8

因此，对于无法控制数据输入方式的第 3 方系统，人们必须编写 ETL首先清理数据的例程，以便使用足够的故障安全包装器为导入准备好数据。