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 alter a MySQL Column from varchar(30) to varchar(100)?
You need to use ALTER TABLE command along with MODIFY
The syntax is as follows
ALTER TABLE yourTableName MODIFY COLUMN yourColumnName varchar(100) NOT NULL;
To understand the above syntax, let us create a table. The query to create a table is as follows
mysql> create table syntaxOfAlterCommandDemo -> ( -> UserId int, -> UserName varchar(30), -> UserAge int, -> UserCityName varchar(50) -> ); Query OK, 0 rows affected (0.51 sec)
Let us check the description of the table.
The query is as follows
mysql> desc syntaxOfAlterCommandDemo;
The following is the output
+--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | UserId | int(11) | YES | | NULL | | | UserName | varchar(30) | YES | | NULL | | | UserAge | int(11) | YES | | NULL | | | UserCityName | varchar(50) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.04 sec)
Now let us change UserName column from varchar(30) to varchar(100). Remember, right now we have set the UserName column to varchar(30). The query is as follows to change the column
mysql> alter table syntaxOfAlterCommandDemo modify column UserName varchar(100) NOT NULL; Query OK, 0 rows affected (1.36 sec) Records: 0 Duplicates: 0 Warnings: 0
Let us check the table description once again.
The query is as follows
mysql> desc syntaxOfAlterCommandDemo;
The following is the output
+--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | UserId | int(11) | YES | | NULL | | | UserName | varchar(100) | NO | | NULL | | | UserAge | int(11) | YES | | NULL | | | UserCityName | varchar(50) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
Look at the sample output above, the column UserName is now varchar(100).
Updated on: 30-Jul-2019
451 Views
Advertisements