错误1265.当试图从txt文件加载数据时，列的数据被截断 [英] error 1265. Data truncated for column when trying to load data from txt file

问题描述

我在mysql表中有表
表看起来像

I have table in mysql table table looks like

create table Pickup
(
PickupID int not null,
ClientID int not null,
PickupDate date not null,
PickupProxy  varchar (40) ,
PickupHispanic bit default 0,
EthnCode varchar(2),
CategCode varchar (2) not null,
AgencyID int(3) not null,

Primary Key (PickupID),
FOREIGN KEY (CategCode) REFERENCES Category(CategCode),
FOREIGN KEY (AgencyID) REFERENCES Agency(AgencyID),
FOREIGN KEY (ClientID) REFERENCES Clients (ClientID),
FOREIGN KEY (EthnCode) REFERENCES Ethnicity (EthnCode)
);

sample data from my txt file 
1065535,7709,1/1/2006,,0,,SR,6
1065536,7198,1/1/2006,,0,,SR,7
1065537,11641,1/1/2006,,0,W,SR,24
1065538,9805,1/1/2006,,0,N,SR,17
1065539,7709,2/1/2006,,0,,SR,6
1065540,7198,2/1/2006,,0,,SR,7
1065541,11641,2/1/2006,,0,W,SR,24

通过使用

LOAD DATA INFILE 'Pickup_withoutproxy2.txt' INTO TABLE pickup;

它会引发错误

错误代码：1265.第1行的PickupID列被截断的数据

Error Code: 1265. Data truncated for column 'PickupID' at row 1

我使用MySQL 5.2

I am using MySQL 5.2

推荐答案

此错误表示Pickup_withoutproxy2.txt文件中至少有一行在其第一列中的值大于int您的PickupId字段）。

This error means that at least one row in your Pickup_withoutproxy2.txt file has a value in its first column that is larger than an int (your PickupId field).

Int只能接受-2147483648到2147483647之间的值。

An Int can only accept values between -2147483648 to 2147483647.

检查您的数据，看看发生了什么。如果txt文件非常大并且很难看到，您可以尝试将其加载到具有varchar数据类型的临时表中。很容易在数据库中检查一个int。

Review your data to see what's going on. You could try to load it into a temp table with a varchar data type if your txt file is extremely large and difficult to see. Easy enough to check for an int once loaded in the database.

祝你好运。

这篇关于错误1265.当试图从txt文件加载数据时，列的数据被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！