MySQL 错误 1265。尝试从 txt 文件加载数据时，列的数据被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14764080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
error 1265. Data truncated for column when trying to load data from txt file
提问by Andrey
I have table in mysql table table looks like
我在mysql表表中有表看起来像
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
when I am trying to submit it by using
当我尝试通过使用提交它时
LOAD DATA INFILE 'Pickup_withoutproxy2.txt' INTO TABLE pickup;
it throws error
它抛出错误
Error Code: 1265. Data truncated for column 'PickupID' at row 1
错误代码：1265。第 1 行“PickupID”列的数据被截断
I am using MySQL 5.2
我正在使用 MySQL 5.2
回答by sgeddes
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).
此错误意味着 Pickup_withoutproxy2.txt 文件中至少有一行的第一列中的值大于 int（您的 PickupId 字段）。
An Int can only accept values between -2147483648 to 2147483647.
Int 只能接受 -2147483648 到 2147483647 之间的值。
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.
查看您的数据以了解发生了什么。如果您的 txt 文件非常大且难以查看，您可以尝试将其加载到具有 varchar 数据类型的临时表中。一旦加载到数据库中，就可以很容易地检查 int 。
Good luck.
祝你好运。
回答by Bluebaron
You're missing FIELDS TERMINATED BY ','and it's assuming you're delimiting by tabs by default.
您丢失了FIELDS TERMINATED BY ','，并且假设您默认使用制表符分隔。
回答by Celik
I have met this problem with a column that has ENUMvalues('0','1').
When I was trying to savea new record, I was assigning value 0for the ENUMvariable.
我在一个具有ENUM值（'0'，'1'）的列中遇到了这个问题。
当我尝试保存新记录时，我为ENUM变量分配了值0。
For thesolution: I have changed ENUMvariable value from 0to 1, and 1to 2.
对于解决方案：我已将ENUM变量值从0更改为1，将1更改为2。
回答by ivahidmontazer
I had same problem. I wanted to edit ENUM values in table structure. Problem was because of rows that was saved before and new ENUM values doesn't contain saved values.
我有同样的问题。我想编辑表结构中的 ENUM 值。问题是因为之前保存的行和新的 ENUM 值不包含保存的值。
Solution was updating old saved rows in MySql table.
解决方案是更新 MySql 表中旧的保存行。
回答by Liam Mayfair
I had this issue when trying to convert an existing varcharcolumn to enum. For me the issue was that there were existing values for that column that were not part of the enum's list of accepted values. So if your enumwill only allow values, say ('dog', 'cat')but there is a row with birdin your table, the MODIFY COLUMNwill fail with this error.
尝试将现有varchar列转换为enum. 对我来说，问题是该列的现有值不属于enum的接受值列表。因此，如果您enum只允许值，例如，('dog', 'cat')但是bird您的表中有一行，MODIFY COLUMN则会因此错误而失败。
回答by Max
回答by hriziya
I have seen the same warning when my data has extra space, tabs, newlines or other characters in my column which is decimal(10,2)to solve that, I had to remove those characters from value.
当我的数据在我的列中有额外的空格、制表符、换行符或其他字符decimal(10,2)来解决这个问题时，我看到了同样的警告，我不得不从值中删除这些字符。
here is how I handled it.
这是我处理它的方式。
LOAD DATA LOCAL INFILE 'c:/Users/Hitesh/Downloads/InventoryMasterReportHitesh.csv'
INTO TABLE stores_inventory_tmp
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@col1, @col2, @col3, @col4, @col5)
SET sku = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col1,'\t',''), '$',''), '\r', ''), '\n', ''))
, product_name = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col2,'\t',''), '$',''), '\r', ''), '\n', ''))
, department_number = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col3,'\t',''), '$',''), '\r', ''), '\n', ''))
, department_name = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col4,'\t',''), '$',''), '\r', ''), '\n', ''))
, price = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(@col5,'\t',''), '$',''), '\r', ''), '\n', ''))
;
I've got that hint from this answer
我从这个答案中得到了提示
