How to modify the size of column in MySQL table?

MySQLMySQLi Database

We can modify a column size with the help of ALTER command. Let us see how to modify column size. Suppose we are defining any column with some size. At the time of inserting if we are giving more size in comparison to the one we defined, then an error will generate.

The above problem can be reduced while modifying the size. For more understanding, we can create a table with the help of CREATE command −

mysql> CREATE table ModifyColumnNameDemo
-> (
-> id int,
-> StudentName varchar(10)
-> );
Query OK, 0 rows affected (0.45 sec)

After creating a table successfully,we can insert records into the table with the help of INSERT command.

mysql> INSERT into ModifyColumnNameDemo values(1,'CarolTaylor');
ERROR 1406 (22001): Data too long for column 'StudentName' at row 1

From the above query, we are getting the error 1406. This error can be resolved while modifying column. We can use the ALTER command for this. The following is the syntax −

ALTER table yourTableName modify column_name;

Applying the above query to modify the size of column to some size −

mysql> ALTER table ModifyColumnNameDemo modify StudentName varchar(200);
Query OK, 0 rows affected (1.54 sec)
Records: 0 Duplicates: 0 Warnings: 0

After that we can check that the size of column name ‘StudentName’ is size 200.The query is as follows −

mysql> DESC ModifyColumnNameDemo;

The following is the output −

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
|id | int(11) | YES          |      | NULL|         |
| StudentName | varchar(200) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

Look at the StudentName column above, the size has been changed to 200. Now we can insert a record into the table and we won’t get any error. Let’s check −

mysql> INSERT into ModifyColumnNameDemo values(1,'CarolTaylor');
Query OK, 1 row affected (0.14 sec)

The above record have been inserted into the table successfully. We can display the record inserted above with the help of SELECT command −

mysql> SELECT * from ModifyColumnNameDemo;

The following is the output −

| id | StudentName |
+------+-------------+
| 1 | CarolTaylor|
+------+-------------+
1 row in set (0.00 sec)

Look at the above output, the record has been inserted successfully after modifying the size of column.

raja
Published on 18-Oct-2018 09:05:34
Advertisements