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

Question

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.

Answer 1

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:

1. 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

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

Code:

SET @@global.sql_mode= ''; 

Answer 2

This happened to me recently. I was fully migrate to MySQL 5.7, and everything is in default configuration.

All previously answers are already clear and I just want to add something.

This 1406 error could happen in your function / procedure too and not only to your table's column length.

In my case, I've trigger which call procedure with IN parameter varchar(16) but received 32 length value.

I hope this help someone with similar problem.