When you load data from file to a MySQL table, you might run into this error:

Data truncated for column 'column_name' at row #


That error means the data is too large for the data type of the MySQL table column. 


Here are some common causes and how to fix:


1. Datatype mismatch.


First, check if the data type of the column is right for the input data. Maybe its defined length is smaller than it should be, or maybe there’s a misalignment that resulted in a value trying to be stored in a field with different datatype.

2. Wrong terminating character

If you manually insert each line into the table and it works just fine, the error occurs only when you load multiple lines, then it’s likely the command didn’t receive proper terminating character.

So check your file’s terminating character and specify it in the LOAD command


  • If it’s terminated by a tab
:
FIELDS TERMINATED BY '\t'
  • If it’s terminated by a comma
FIELDS TERMINATED BY ','

Then you’re good to go.

Need a good MySQL GUI? TablePlus provides a native client that allows you to access and manage MySQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode