Error Code: 1406. Data too long for column - MySQL

mysql, sql

Solution

MySQL will truncate any insert value that exceeds the specified column width.

to make this without error try switch your `SQL mode` to not use `STRICT`.

Mysql reference manual

EDIT:

To change the mode

This can be done in two ways:

- Open your `my.ini` (Windows) or `my.cnf` (Unix) file within the MySQL installation directory, and look for the text "sql-mode".

Find:

Code:

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

Replace with:

Code:

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

Or

- You can run an SQL query within your database management tool, such as phpMyAdmin:

Code:

SET @@global.sql_mode= '';

Problem

Error Code: 1406. Data too long for column ``` CREATE TABLE `TEST` ( `idTEST` INT NOT NULL , `TESTcol` VARCHAR(45) NULL , PRIMARY KEY (`idTEST`) ); ``` Now `Insert` some values ``` INSERT INTO TEST VALUES ( 1, 'Vikas' ) select SELECT * FROM TEST; ``` Inserting record more than the `length` ``` INSERT INTO TEST VALUES ( 2, 'Vikas Kumar Gupta Kratika Shukla Kritika Shukla' ) ``` If we `select` the `length` ``` SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla') '47' ``` And it is showing the error message Error Code: 1406. Data too long for column But what is my expectation is, I want to insert at least first 45 characters in Table please let me know if the question is not clear. I know the cause of this error. I am trying to insert values more than the length of datatype. I want solution in MySQL as It is possible in `MS SQL`. So I hope it would also be in MySQL.

Original source