JIYIK CN >

This article describes possible causes and solutions for the MySQL error Data is truncated for a column .

Fix data truncated due to column error in MySQL

Here, we will discuss the possible causes and solutions to eliminate MySQL data truncated by a certain column error.

Data is too large

In some cases, we have to load data from a file instead of inserting it manually into a MySQL table. While loading, the error "Data truncated for column 'columnName' at row #" may occur. Why?

This is because the given data seems too large for the data type of the column in the MySQL table. For example, you have a table where the value attribute is of type text, which can only accommodate 65K characters of the provided data.

When you insert data for a value column exceeding 65K, you will receive this error.

To solve this problem, you need to change to a type that can accept more data, such as MEDIUMTEXT or LONGTEXT, which can accommodate 16MB and 4GB. You can also find some optimization tips here.

We can also truncate the data using SET ANSI_WARNINGS OFF and insert the records again, considering the maximum length of the column data type.

Invalid data was passed

If we try to insert invalid data, Error Code: 1265. Data truncated for column 'a' at row 1 will also appear in MySQL.

For example, we have a table where the price field type is float and accepts NULL values. See the following:

#create a table named `prices`
CREATE TABLE prices (ID INT NOT NULL, price FLOAT NULL);
#insert the first record
INSERT prices VALUES (1, '');

Here, we insert an empty string into the price column, a floating point type that accepts NULL values. Remember, NULL and '' are not the same thing.

However, if we try to insert an empty string into a column that takes NULL values, an error code is generated: 1265. The data is truncated in column 'a' in row 1. The easiest way to resolve this issue is to pass the correct values ​​of the correct data type required for each column of the price table.

We can resolve it by explicitly specifying NULL value instead of empty string ('') or even not writing the value for that particular column in the INSERT statement. Considering our example, see the following code snippet.

INSERT prices VALUES (1, NULL); #insert null explicitly
INSERT prices (ID) VALUES (2); #don't specify values for `price` column

Incorrect terminating character

If we insert each row manually in the MySQL table, everything works fine. The error occurs when we try to load multiple rows at once and do not get the terminating character correctly.

To solve this problem, we need to check the termination character of the file and specify it in the LOAD command as follows.

#if the terminating character is `tab`
FIELDS TERMINATED BY '\t'
#if the terminating character is a `comma`
FIELDS TERMINATED BY ','