概要
MySQL:DDL 的一些整理。
博客
前言
关于 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:
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;
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