0%

MySQL 的 DDL 的一些整理

概要

MySQL:DDL 的一些整理。

博客

IT老兵博客

前言

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

(上面这句话中还存在一个错别字,函数,应该是含糊,这说明这篇文章后来没有再仔细阅读过。2020-03-05)

正文

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:

1
2
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:

1
2
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:

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

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 可以修改一列和修改它的定义,或者二者。
比 modify 的用处更多,但是牺牲了一些便捷性?
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.

总结

感觉这么总结,还是有些内容没有整理清楚,先放一放,等到有时间,还需要想一下,有条理地整理一下。

参考

https://hoelz.ro/ref/mysql-alter-table-alter-change-modify-column
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html