71

I know, we cannot rename a column using MODIFY COLUMN syntax, but we can using CHANGE COLUMN syntax.

My question is: what is the main usage of modify syntax?

For example:

ALATER TABLE tablename CHANGE col1 col1 INT(10) NOT NULL;

instead of

ALATER TABLE tablename MODIFY col1 INT(10) NOT NULL;

Edited (question replaced)

  • What is the main usage of MODIFY syntax?
  • Why we have to use CHANGE COLUMN instead of MODIFYCOLUMN?
informatik01
  • 16,038
  • 10
  • 74
  • 104

5 Answers5

98

CHANGE COLUMN

If you have already created your MySQL database, and decide after the fact that one of your columns is named incorrectly, you don't need to remove it and make a replacement, you can simply rename it using change column.

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;

MODIFY COLUMN

This command does everything CHANGE COLUMN can, but without renaming the column. You can use the MODIFY SQL command if you need to resize a column in MySQL. By doing this you can allow more or less characters than before. You can't rename a column using MODIFY and other.

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

Note

ALTER TABLE is used for altering a table in order to change column name, size, drop column etc. CHANGE COLUMN and MODIFY COLUMN commands cannot be used without help of ALTER TABLE command.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Parvathy
  • 2,275
  • 3
  • 24
  • 39
  • what is the term `FIRST` and `AFTER` are they keyword in `sql`? – Kasun Siyambalapitiya Jul 30 '16 at 11:33
  • 1
    `FIRST` - will be the first colum on table from the left side; `AFTER col_name` - column will be located after this col_name. – Rozkalns Aug 04 '16 at 10:40
  • it's especially useful for adding comments with `comment 'my comment'` on columns without having to retype the column's name in the statement. –  Sep 16 '17 at 16:05
  • What purpose does the keyword COLUMN serve in the MODIFY statement? The MySQL documentation executed the same statement but without the COLUMN keyword: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html – theyuv Oct 02 '18 at 13:13
37

The difference is whether you want to change the column name, column definition or both.

CHANGE

Can change a column name or definition, or both
ALTER TABLE t1 CHANGE a b BIGINT NOT NULL

MODIFY

Can change a column definition but not its name
ALTER TABLE t1 MODIFY b INT NOT NULL

RENAME COLUMN (from MySQL 8.0)

Can change a column name but not its definition
ALTER TABLE t1 RENAME COLUMN b TO a

Also, CHANGE and MODIFY can be followed by an optional COLUMN keyword.

For complete explanation:

  1. MySQL 5.7 Docs- Renaming, Redefining, and Reordering Columns
  2. MySQL 8.0 Docs- Renaming, Redefining, and Reordering Columns
Deepam Gupta
  • 2,374
  • 1
  • 30
  • 33
shaahiin
  • 1,243
  • 1
  • 16
  • 26
4

I found one difference after more than an hour of effort in trying to make a non auto_increment column into auto_increment statement:

alter table `doctor_experience` modify column `id` int(11) unsigned auto_increment

works, but statment:

alter table `doctor_experience` change column `id` `id` int(11) unsigned auto_increment

will report an error.

Anthony
  • 36,459
  • 25
  • 97
  • 163
Hongwei
  • 41
  • 1
2

That is the same. It was done to support another syntax (Oracle ALTER TABLE as I know). You can use both of them.

Note: ALTER TABLE CHANGE old_col_name new_col_name syntax allows renaming column using one command.

Devart
  • 119,203
  • 23
  • 166
  • 186
2

Change Column : Used when we want to change the column name with its definition. eg - alter table student CHANGE name full_name VARCHAR(32) NOT NULL;

Modify column : Used when column name is to be same but change in its definition. eg - alter table student MODIFY full_name VARCHAR(64) NOT NULL;

Rename column : Used when we only need to change the column name (its definition will be same) alter table student RENAME COLUMN full_name TO name;

vsharma
  • 299
  • 3
  • 3