How to Solve MySQL Error 1406: Data Too Long for Column?

MySQL Error: "1406. Data too Long for Column"

When attempting to insert data into a MySQL table where the column's data length exceeds its defined width, you'll encounter the "Error Code: 1406. Data too long for column" error.

Consider this example:

CREATE  TABLE `TEST` (
  `idTEST` INT NOT NULL ,
  `TESTcol` VARCHAR(45) NULL ,
  PRIMARY KEY (`idTEST`) 
);

Inserting data within the specified column length works as expected:

INSERT INTO TEST
VALUES
(
    1,
    'Vikas'
);

However, attempting to insert data that exceeds the maximum column length results in the error:

INSERT INTO TEST
VALUES
(
    2,
    'Vikas Kumar Gupta Kratika Shukla Kritika Shukla'
);

This occurs because MySQL truncates any insert value that is longer than the specified column width.

Solution:

To resolve this issue, temporarily disable MySQL's strict mode:

In your my.ini (Windows) or my.cnf (Unix) file:

Edit the "sql-mode" setting to remove "STRICT_TRANS_TABLES":

# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Using an SQL query in a database management tool:

Execute the following query to set the global SQL mode to empty:

SET @@global.sql_mode= '';

With strict mode disabled, you can insert data that exceeds the column length. However, be cautious as this can lead to data truncation and inconsistencies, so use this solution judiciously.

The above is the detailed content of How to Solve MySQL Error 1406: Data Too Long for Column?. For more information, please follow other related articles on the PHP Chinese website!