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 the data type of a MySQL table’s column?
You can use modify command for this. Let us first us create a table.
mysql> create table DemoTable ( StudentId varchar(200) not null, StudentName varchar(20), StudentAge int, StudentAddress varchar(20), StudentCountryName varchar(20) ); Query OK, 0 rows affected (0.73 sec)
Now check the description of table.
mysql> desc DemoTable;
This will produce the following output −
+--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+-------+ | StudentId | varchar(200) | NO | | NULL | | | StudentName | varchar(20) | YES | | NULL | | | StudentAge | int(11) | YES | | NULL | | | StudentAddress | varchar(20) | YES | | NULL | | | StudentCountryName | varchar(20) | YES | | NULL | | +--------------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
Here is the query to alter the data type from varchar to BIGINT −
mysql> alter table DemoTable modify StudentId BIGINT(10) UNSIGNED NOT NULL DEFAULT 0; Query OK, 0 rows affected (1.51 sec) Records: 0 Duplicates : 0 Warnings : 0
Above, we have changed the column ‘StudentId’ datatype from varchar(200) to BIGINT(10). Let us check the description of table once again.
mysql> desc DemoTable;
This will produce the following output −
+--------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------------+------+-----+---------+-------+ | StudentId | bigint(10) unsigned | NO | | 0 | | | StudentName | varchar(20) | YES | | NULL | | | StudentAge | int(11) | YES | | NULL | | | StudentAddress | varchar(20) | YES | | NULL | | | StudentCountryName | varchar(20) | YES | | NULL | | +--------------------+---------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
Updated on: 30-Jul-2019
97 Views
Advertisements