How to alter a MySQL Column from varchar(30) to varchar(100)?

MySQLMySQLi Database

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).

raja
Published on 29-Mar-2019 10:44:10
Advertisements