mysql:加载数据填充生成不正确的表结果

kqqjbcuj 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(372)

我正在尝试使用load data infle将数据加载到表中。
我的table：

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 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...

sql mysql csv mysql-workbench innodb

来源：https://stackoverflow.com/questions/53348270/mysql-load-data-infile-producing-incorrect-table-results

1条答案

按热度按时间

camsedfj1#

我根据你的描述创建了一个表( 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>

赞(0）回复(0）举报 2021-06-19

我来回答

mysql:加载数据填充生成不正确的表结果

1条答案

相关问题

热门标签

最新问答