Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to modify the size of column in MySQL table?
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.
549 Views
