Update column size in MySQL and increase its size?

To update the column size, you can use alter command. The syntax is as follows −

alter table yourTableName change yourColumnName yourColumnName data type;

To understand the above syntax, let us create a table. The query to create a table −

mysql> create table DataTruncated
   −> (
   −> id int,   
   −> Name varchar(5)
   −> );
Query OK, 0 rows affected (0.64 sec)

Look at the column ‘Name’ above, the column size is 5. Whenever we will give the size greater than 5 then MySQL gives the following error −

mysql> insert into DataTruncated values(101,'JohnSmith');
ERROR 1406 (22001): Data too long for column 'Name' at row 1

Now update the column size of column ‘Name’. The query is as follows −

mysql> alter table DataTruncated change Name Name varchar(200);
Query OK, 0 rows affected (2.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Insert the same record into the table. Now no error is visible since we updated the column size from 5 to 25 −

mysql> insert into DataTruncated values(101,'JohnSmith');
Query OK, 1 row affected (0.11 sec)

Display the records −

mysql> select *from DataTruncated;

The following is the output −

+------+-----------+
| id   | Name      |
+------+-----------+
| 101  | JohnSmith |
+------+-----------+
1 row in set (0.00 sec)

Rishi Rathor

Updated on: 30-Jul-2019

296 Views

Get certified by completing the course

Get Started