MySQL 修改列 Vs 更改列

Modify column Vs change column

提问by Parvathy

I know, we can not rename a column using modify column syntax,but can change column syntax.

我知道，我们不能使用modify column syntax, 但可以重命名列change column syntax。

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

我的问题是： 的主要用途是modify syntax什么？

For example,

例如，

alter table tablename change col1 col1 int(10) not null

instead of

代替

alter table tablename modify col1 int(10) not null

Edited
Question replaced

编辑的
问题已替换

What is the main usage of modify syntax?

的主要用途是modify syntax什么？

Above question was replaced by below

上面的问题被下面替换了

Why we have to use change column instead of modify column?

为什么我们必须使用更改列而不是修改列？

回答by Parvathy

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.

CHANGE COLUMN 如果您已经创建了您的 MySQL 数据库，并在事后确定您的一列命名不正确，您不需要删除它并进行替换，您只需使用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

MODIFY COLUMN 此命令执行 CHANGE COLUMN 可以执行的所有操作，但不重命名列。如果需要调整 MySQL 中的列大小，可以使用 modify SQL 命令。通过这样做，您可以允许比以前更多或更少的字符。您不能使用修改和其他重命名列

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.

注意：ALTER TABLE 用于更改表意味着更改列名、大小、删除列。没有 ALTER TABLE 命令的帮助，不能使用 CHANGE COLUMN 和 MODIFY COLUMN 命令。

回答by Shaahiin

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

区别在于您是要更改列名称、列定义还是两者。

CHANGE

改变

Can rename a column or change its definition, or both.

可以重命名列或更改其定义，或两者兼而有之。

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL

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

ALTER TABLE t1 MODIFY b INT NOT NULL

RENAME COLUMN

重命名列

Can change a column name but not its definition.

可以更改列名称，但不能更改其定义。

ALTER TABLE t1 RENAME COLUMN b TO a

ALTER TABLE t1 RENAME COLUMN b TO a

---

---

You can check the docsfor the complete explanation.

您可以查看文档以获得完整的解释。

回答by Hongwei

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_experiencemodify column idint(11) unsigned auto_increment works, but statment: alter table doctor_experiencechange column ididint(11) unsigned auto_increment will report an error.

经过一个多小时的努力，我发现了一个不同之处，试图将非 auto_increment 列转换为 auto_increment 语句：alter table doctor_experiencemodify column idint(11) unsigned auto_increment 有效，但是 statment：alter table doctor_experiencechange column ididint(11) unsigned auto_increment 将报告一个错误。

回答by Devart

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

那是一样的。这样做是为了支持另一种语法（据我所知，Oracle ALTER TABLE）。您可以同时使用它们。

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

注意：ALTER TABLE CHANGE old_col_name new_col_name语法允许使用一个命令重命名列。

回答by vsharma

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

更改列：当我们想要更改列名及其定义时使用。例如 - 改变表studentCHANGE namefull_nameVARCHAR(32) NOT NULL;

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

修改列：当列名相同但定义改变时使用。例如 - 更改表studentMODIFY full_nameVARCHAR(64) NOT NULL;

Rename column : Used when we only need to change the column name (its definition will be same) alter table studentRENAME COLUMN full_nameTO name;

重命名列：当我们只需要更改列名（其定义相同）时使用 alter table studentRENAME COLUMN full_nameTO name;