Search code examples
mysqlalter-table

`MODIFY COLUMN` vs `CHANGE COLUMN`

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?
Solution

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