How to increase varchar size of an existing column in a database without breaking existing data in MySQL?

MySQLMySQLi Database

Increase the varchar size of an existing column in a database with the help of CHANGE command. The syntax is as follows −

alter table yourTableName change yourColumnName yourColumnName dataType;

Here, we are creating a table with a single column and varchar size 200 −

mysql> create table IncreaseVarcharDemo
   −> (
   −> StudentId varchar(200)
   −> );
Query OK, 0 rows affected (0.60 sec)

Now insert record in the table. The query is as follows −

mysql> insert into IncreaseVarcharDemo values('John123');
Query OK, 1 row affected (0.16 sec)

Displaying all records from the table with the help of the following query −

mysql> select *from IncreaseVarcharDemo;

The following is the output −

+-----------+
| StudentId |
+-----------+
| John123   |
+-----------+
1 row in set (0.00 sec)

The following is the query to increase varchar size from 200 to 1000 −

mysql> alter table IncreaseVarcharDemo change StudentId StudentId varchar(1000);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now you can check the varchar size has been increased or not. The query is as follows −

mysql> desc IncreaseVarcharDemo;

The following is the output −

+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| StudentId | varchar(1000) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
1 row in set (0.03 sec)
raja
Published on 11-Jan-2019 09:25:29
Advertisements