mysql所有ddl_MySQL：DDL的一些整理

完整的MongoDB学习笔记位于IT老兵博客。

MySQL：DDL的一些整理。

前言

对于MYSQL对于表结构的修改，一直存在一些函数的地方，这里，参考一篇外贴，做一下好好的整理。

正文MySQL ALTER TABLE: ALTER vs CHANGE vs MODIFY COLUMN

Whenever I have to change a column in MySQL (which isn’t that often), I always forget the difference between ALTER COLUMN, CHANGE COLUMN, and MODIFY COLUMN. Here’s a handy reference.

这哥们也遇到了对于alter，change和modify的困扰，所以做了一个整理。

ALTER COLUMN

Used to set or remove the default value for a column. Example:

ALTER TABLE MyTable ALTER COLUMN foo SET DEFAULT ‘bar’;

ALTER TABLE MyTable ALTER COLUMN foo DROP DEFAULT;

alter 常常用来设置或者移除一列的默认值。

CHANGE COLUMN

Used to rename a column, change its datatype, or move it within the schema. Example:

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;

change用来重命名一列，修改数据类型，或者在模式中移动它。

MODIFY COLUMN

Used to do everything CHANGE COLUMN can, but without renaming the column. Example:

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

The official documentation for ALTER TABLE (for MySQL 5.1) is here.

modify除了不能重命名一个列，可以做change的所有工作。

官网的摘录：

CHANGE:

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

Has more capability than MODIFY, but at the expense of convenience for some operations. CHANGE requires naming the column twice if not renaming it.

With FIRST or AFTER, can reorder columns.

MODIFY:

Can change a column definition but not its name.

More convenient than CHANGE to change a column definition without renaming it.

With FIRST or AFTER, can reorder columns.

ALTER: Used only to change a column default value.

参考