mysql - "FIELDS TERMINATED BY"和 "LINES TERMINATED BY"有什么问题？

Question

所以我创建了一个名为 specs 的 mysql 表，我想将一个 csv 文件导入到该表中。

CREATE TABLE specs (
  `Id` INT NOT NULL,
  `Brand` VARCHAR(40) NOT NULL,
  `Horsepower` INT NOT NULL,
  `Range` INT NOT NULL,
  PRIMARY KEY (Id)
);

csv 数据如下所示

ID\Brand\Horsepower\Range
1\Mercedes Benz\237\634
2\Audi\345\567
3\Ford\190\456
4\BMW\278\547
5\Toyota\123\364
6\Fiat\90\289
7\Daihatsu\120\450
8\Jeep\500\670
9\Seat\119\289
10\Mitsubishi\78\410

这就是我导入数据的方式，但表格中填满了零。哪里有问题。我认为问题出在 FIELDS 和 LINES TERMINATED BY。

LOAD DATA LOCAL INFILE '/Users/anilyelin/Desktop/cars.csv'
INTO TABLE specs
FIELDS TERMINATED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

输出如下所示:

mysql> select * from specs;
+----+-------+------------+-------+
| Id | Brand | Horsepower | Range |
+----+-------+------------+-------+
|  1 |       |          0 |     0 |
|  2 |       |          0 |     0 |
|  3 |       |          0 |     0 |
|  4 |       |          0 |     0 |
|  5 |       |          0 |     0 |
|  6 |       |          0 |     0 |
|  7 |       |          0 |     0 |
|  8 |       |          0 |     0 |
|  9 |       |          0 |     0 |
| 10 |       |          0 |     0 |
+----+-------+------------+-------+
10 rows in set (0,00 sec)

Answer 1

您应该尝试用竖线替换字段分隔符\|

LOAD DATA LOCAL INFILE '/Users/anilyelin/Desktop/cars.csv'
INTO TABLE specs
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS