Redefinition Clauses

These 2 modifiers can be used in MySQL , they allow you to change names and definitions of columns that have already been created, avoiding having to erase and create again. Although they have many similarities, each has a specific purpose.

Change

Can be used to rename a column and change its settings, such as the data type of a column, for example:

CREATE TABLE clientes(
    nome int,
    id int,
    endereco int
);

ALTER TABLE clientes CHANGE COLUMN nome nome_cliente VARCHAR(50);

It has more capacity than MODIFY because it allows you to change the column name. It is most commonly used when there is an error in the column name and its definitions.

To use it, you need to specify the current name of the column and then the name you want to rename, if you do not want to change the name, simply put the same name again so that it remains with the name (which is not the purpose of this clause).

Allows you to use FIRST and AFTER to reorder the columns, for example:

ALTER TABLE clientes CHANGE COLUMN id id_cliente INT FIRST;

ALTER TABLE clientes CHANGE COLUMN id id_cli INT AFTER endereco;

Modify

Can be used to change the settings of a column but not its name, for example:

CREATE TABLE clientes(
    nome int,
    id int,
    endereco int
);

ALTER TABLE clientes MODIFY COLUMN nome VARCHAR(50);

It is more convenient than CHANGE , because it allows you to change the columns, without having to pass another name to rename it. It is most commonly used when you want to change column definitions only.

It can also be used with FIRST E AFTER , to reorder the columns:

ALTER TABLE cliente MODIFY COLUMN nome VARCHAR(50) FIRST;

ALTER TABLE cliente MODIFY COLUMN nome VARCHAR(50) AFTER endereco;

Note:

  

Although it works in MySQL , CHANGE is an extension of MySQL   for SQL default, MODIFY is an extension of MySQL for   compatibility with Oracle .

You can learn more by looking at your Reference Manual .