Mysql command alter table, difference from "Modify" and "change"?

Asked

Viewed 5,022 times

4

What are the main differences in change and Modify, in which cases should I use them ?

alter table cadastro change nome nome varchar(20); 

alter table cadastro modify nome varchar(30);
mysql sql
  • 1

    Basically the change serves to rename a column, change the type, default value etc. While the modify is limited to changes of type, default value etc. That is, it does "everything" that change does, except rename things.

1 answer

7


Reset Clauses

These 2 modifiers can be used in MySQL, they allow you to change names and column definitions that have already been created, avoiding having to delete 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);

He has more capacity than the MODIFY, because it allows you to change the column name. It is most 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, just put the same name again, to remain with the name (which is not the purpose of this clause).

Allows using the FIRST and the AFTER to reorder 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 the CHANGE, because it allows the change of the columns, without having to pass another name to rename it. It is most used when you want to change only the column settings.

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

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

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

Obs:

Although it works on MySQL, the CHANGE is an extension of MySQL to the SQL standard, already the MODIFY is an extension of MySQL for compatibility with the Oracle.

You can know more by looking at your Reference Manual.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.