mysql - MySQL：LOAD DATA INFILE 产生不正确的表结果

Question

我正在尝试使用 LOAD DATA INFILE 将我的数据加载到表中。

我的桌子：

Alcohol name varchar(45)
Type varchar(45)
ABV FLOAT
Brewery INT(11)
Average Price DOUBLE
Description VARCHAR(1000)

我的 CSV 文件如下所示：

Alcohol name,Type,ABV,Brewery,Average Price,Description
Hocus Pocus,Beer,4.5,812,0,"Our take on a classic summer ale.  A toast to weeds, rays, and summer haze.  A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange."
Grimbergen Blonde,Beer,6.699999809,264,0,None
Widdershins Barleywine,Beer,9.100000381,779,0,None
Lucifer,Beer,8.5,287,0,None
Bitter,Beer,4,1056,0,None
Winter Warmer,Beer,5.199999809,1385,0,None
Winter Welcome 2007-2008,Beer,6,1099,0,None

etc....

我的 LOAD 命令看起来像：

LOAD DATA LOCAL INFILE '/home/leo/CS336/Tables/beers.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' IGNORE 1 LINES; 

我得到的垃圾表看起来像：

Alcohol name Type  ABV Brewery Average Price Description
Golden Ale  NULL    0   0   NULL NULL   
* 10.5% (2008)  NULL    0   0   NULL NULL       
* 15.5 gallon keg   NULL    0   0       NULL NULL   
* 22 oz. bottles    NULL    0   0   NULL NULL       
* 5.17 gallon keg   NULL    0   0   NULL NULL       
* 9.78% (2007)"     NULL    0   0   NULL NULL   
* Available March through August    NULL    0   0       NULL NULL   
etc...      

标签： mysqlsqlcsvmysql-workbenchinnodb

Answer 1

我根据您的描述创建了一个表格（Average_Price我添加了一个下划线）

 create table Alcohol (
   name varchar(45), 
   Type varchar(45), 
   ABV FLOAT, 
   Brewery INT(11), 
   Average_Price DOUBLE, 
   Description VARCHAR(1000)
);

我像这样加载了你的示例文件

 LOAD DATA LOCAL INFILE '/tmp/alcohol.csv' INTO TABLE Alcohol FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; 

我认为我得到了合理的结果：

 `SELECT * from Alcohol;

+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name                     | Type | ABV  | Brewery | Average_Price | Description                                                                                                                                                                                                                                                                                                                       |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hocus Pocus              | Beer |  4.5 |     812 |             0 | Our take on a classic summer ale.  A toast to weeds, rays, and summer haze.  A light, crisp ale for mowing lawns, hitting lazy fly balls, and communing with nature, Hocus Pocus is offered up as a summer sacrifice to clodless days.Its malty sweetness finishes tart and crisp and is best apprediated with a wedge of orange. |
| Grimbergen Blonde        | Beer |  6.7 |     264 |             0 | None                                                                                                                                                                                                                                                                                                                              |
| Widdershins Barleywine   | Beer |  9.1 |     779 |             0 | None                                                                                                                                                                                                                                                                                                                              |
| Lucifer                  | Beer |  8.5 |     287 |             0 | None                                                                                                                                                                                                                                                                                                                              |
| Bitter                   | Beer |    4 |    1056 |             0 | None                                                                                                                                                                                                                                                                                                                              |
| Winter Warmer            | Beer |  5.2 |    1385 |             0 | None                                                                                                                                                                                                                                                                                                                              |
| Winter Welcome 2007-2008 | Beer |    6 |    1099 |             0 | None                                                                                                                                                                                                                                                                                                                              |
|                          | NULL | NULL |    NULL |          NULL | NULL                                                                                                                                                                                                                                                                                                                              |
+--------------------------+------+------+---------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
</code>