What is the MySQL error: “Data too long for column”?

MySQLMySQLi Database

The “Data too long for column” error occurs when you insert more data for a column that does not have the capability to store that data.

For Example - If you have data type of varchar(6) that means it stores only 6 characters. Therefore, if you will give more than 6 characters, then it will give an error.

Let us create a table to understand the error. The query to create a table is as follows −

mysql> create table DataToolongDemo
  −> (
  −> Name varchar(10)
   −> );
Query OK, 0 rows affected (0.55 sec)

Above, we have created a table successfully. We have set a field “Name” with “varchar(10). Now, if we will give more than 10 characters, an error will generate.

The error is as follows −

mysql> insert into DataToolongDemo values('Carol Taylor');
ERROR 1406 (22001): Data too long for column 'Name' at row 1

To rectify the above error, you can set the type to longtext. The query is as follows to change type to longtext, since currently the type is “varchar” −

mysql> alter table DataToolongDemo change Name Name longtext;
Query OK, 0 rows affected (2.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now if you will insert same record, then no error will get generated −

mysql> insert into DataToolongDemo values('Carol Taylor');
Query OK, 1 row affected (0.11 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from DataToolongDemo;

The following is the output −

+--------------+
| Name         |
+--------------+
| Carol Taylor |
+--------------+
1 row in set (0.00 sec)
raja
Published on 11-Jan-2019 11:16:36
Advertisements