时间戳

首页 > 解决方案 > 将可变长度整数加载到 MySQL 表中的问题

mysql - 将可变长度整数加载到 MySQL 表中的问题

问题描述

我是 MySQL 新手，正在尝试将 Fangrpahs 标准团队击球数据（CSV 格式）加载到 MySQL 数据库/表中。问题在于我的最后一个专栏（playerid）。我的数据看起来像这样

"Name","G","AB","PA","H","1B","2B","3B","HR","R","RBI","BB","IBB","SO","HBP","SF","SH","GDP","SB","CS","AVG","playerid"
"Nomar Garciaparra","38","156","169","50","35","7","3","5","24","21","8","2","16","4","1","0","4","2","0",".321","190"
"Trot Nixon","48","149","167","47","31","9","1","6","24","23","15","1","24","1","2","0","3","0","0",".315","204"
"Manny Ramirez","152","568","663","175","88","44","0","43","108","130","82","15","124","6","7","0","17","2","4",".308","210"
"Johnny Damon","150","621","702","189","128","35","6","20","123","94","76","1","71","2","3","0","8","19","8",".304","185"

请注意，玩家 ID 是 3 位或 4 位整数，具体取决于玩家

关于“playerid”数据类型的输入，我遇到了多个错误。首先我创建了我的表：

CREATE TABLE redsoxbatting2004 (
-> Name Varchar(20),
-> G int,
-> AB int,
-> PA int,
-> H int,
-> 1B int,
-> 2B int,
-> 3B int,
-> HR int,
-> R int,
-> RBI int,
-> BB int,
-> IBB int,
-> SO int,
-> HBP int,
-> SF int,
-> SH int,
-> GDP int,
-> SB int,
-> CS int,
-> AVG DECIMAL(3,3),
-> playerid int);

接下来我尝试将数据加载到 redsoxbatting2004 但收到错误 1265

load data infile 'Redsoxbatting2004.csv' into table redsoxbatting2004
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
-> ignore 1 rows;

ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

我尝试将删除数据表 redsoxbatting2004 并使用不同数据类型为 playerid 重新创建为 Varchar(50) 和 Varchar(4) 仅分别收到以下两个错误。

ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
ERROR 1406 (22001): Data too long for column 'playerid' at row 1

我不确定这是否是我的表格生成、数据输入方法或其他方面的问题。

非常感谢！

PS 这是我在 Stack 上提出的第一个问题。如果我有任何格式错误或是否可以采取任何其他措施使其正确，请告诉我

标签： mysqldatabase

解决方案

根据错误，您要插入的列多于表中可用的列。

例如，如果一个表有 2 列，则不能在其中插入 3。

我会从 ckv 文件的第一条记录到表中做一个简单的插入，这样你就可以更容易地发现额外的列。

推荐阅读