Error code: 1406. The data is too long – MySQL

CREATE TABLE `TEST` 
(

 `idTEST` INT NOT NULL ,

 `TESTcol` VARCHAR(45) NULL ,

 PRIMARY KEY (`idTEST`) 
) ;

INSERT INTO TEST
VALUES
(
 1,
 ' Vikas'
)

select 

SELECT * FROM TEST;

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

SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla')

 '47'

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

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

SET @@global.sql_mode='';

Error code: 1406. The column data is too long

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;

Insert records exceeding length

INSERT INTO TEST
VALUES
(
 2,
'Vikas Kumar Gu pta Kratika Shukla Kritika Shukla'
)

If we choose the length

SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla')< br />
 '47'

It displays an error message

Error code: 1406. The column data is too long

But my expectation is , I want to insert at least the first 45 characters in the table

If the problem is not clear, please let me know.

I know the reason for this error. I tried to insert more than the length of the data type Value.

I want the solution in MySQL because it can be used in MS SQL. So I hope it will also appear in MySQL.

< /p>

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

To do this there is no error. Try to switch your SQL mode to not use STRICT.

< p>Mysql reference manual

Edit:

To change the mode

This can be done in two ways:

>Install in MySQL Open the my.ini (Windows) or my.cnf (Unix) file in the directory, and then search for the text “sql-mode”.

Find:

Code:

< p>

# 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"

either

>You can run SQL queries in database management tools, such as phpMyAdmin:

Code:

SET @@global.sql_mode='';< /pre>