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;