ALTER TABLE Statement • ALTER : Used only to change a column default value. CHANGE  is a MySQL extension to standard SQL.  MODIFY  and  RENAME COLUMN  are MySQL extensions for Oracle compatibility. To alter a column to change both its name and definition  use  CHANGE   specifying the old and new names and the new definition. For example  to rename an  INT NOT NULL  column from  a  to  b  and change its definition to use the  BIGINT  data type while retaining the  NOT NULL  attribute  do this: To change a column definition but not its name  use  CHANGE  or  MODIFY . With  CHANGE   the syntax requires two column names  so you must specify the same name twice to leave the name unchanged. For example  to change the definition of column  b   do this: -- swap a and b ALTER TABLE t1 RENAME COLUMN a TO b  RENAME COLUMN b TO a; -- "rotate" a  b  c through a cycle ALTER TABLE t1 RENAME COLUMN a TO b  RENAME COLUMN b TO c  RENAME COLUMN c TO a; MODIFY  is more convenient to change the definition without changing the name because it requires the column name only once: To change a column name but not its definition  use  CHANGE  or  RENAME COLUMN . With  CHANGE   the syntax requires a column definition  so to leave the definition unchanged  you must respecify the definition the column currently has. For example  to rename an  INT NOT NULL  column from  b  to  a   do this: RENAME COLUMN  is more convenient to change the name without changing the definition because it requires only the old and new names: In general  you cannot rename a column to a name that already exists in the table. However  this is sometimes not the case  such as when you swap names or move them through a cycle. If a table has columns named  a    b   and  c   these are valid operations: For column definition changes using  CHANGE  or  MODIFY   the definition must include the data type and all attributes that should apply to the new column  other than index attributes such as  PRIMARY KEY or  UNIQUE . Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column  col1  is defined as  INT UNSIGNED DEFAULT 1 COMMENT ’my column’  and you modify the column as follows  intending to change only  INT  to  BIGINT : That statement changes the data type from  INT  to  BIGINT   but it also drops the  UNSIGNED    DEFAULT   and  COMMENT  attributes. To retain them  the statement must include them explicitly: For data type changes using  CHANGE  or  MODIFY   MySQL tries to convert existing column values to the new type as well as possible. 2491 ALTER TABLE t1 CHANGE a b BIGINT NOT NULL; ALTER TABLE t1 CHANGE b b INT NOT NULL; ALTER TABLE t1 MODIFY b INT NOT NULL; ALTER TABLE t1 RENAME COLUMN b TO a; ALTER TABLE t1 CHANGE b a INT NOT NULL; ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT ’my column’; ALTER TABLE t1 MODIFY col1 BIGINT;