• 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: 

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL; 

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: 

ALTER TABLE t1 CHANGE b b INT NOT NULL; 

MODIFY  is more convenient to change the definition without changing the name because it requires the column name only once: 

ALTER TABLE t1 MODIFY b INT NOT NULL; 

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: 

ALTER TABLE t1 CHANGE b a INT NOT NULL; 

RENAME COLUMN  is more convenient to change the name without changing the definition because it requires only the old and new names: 

ALTER TABLE t1 RENAME COLUMN b TO a; 

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: 

-- 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; 

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 : 

ALTER TABLE t1 MODIFY col1 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: 

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT ’my column’; 

For data type changes using CHANGE  or MODIFY , MySQL tries to convert existing column values to the new type as well as possible. 

