Modify column Vs change column
Solution 1:
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 means to change column name, size, drop column. CHANGE COLUMN and MODIFY COLUMN commands cannot be used without help of ALTER TABLE command.
Solution 2:
The difference is whether you want to change the column name, column definition or both.
CHANGE
Can change a column name or definition, or bothALTER TABLE t1 CHANGE a b BIGINT NOT NULL
MODIFY
Can change a column definition but not its nameALTER TABLE t1 MODIFY b INT NOT NULL
RENAME COLUMN (from MySQL 8.0)
Can change a column name but not its definitionALTER TABLE t1 RENAME COLUMN b TO a
Also, CHANGE
and MODIFY
can be followed by an optional COLUMN
keyword.
For complete explanation:
- MySQL 5.7 Docs- Renaming, Redefining, and Reordering Columns
- MySQL 8.0 Docs- Renaming, Redefining, and Reordering Columns
Solution 3:
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.